how do you use a function in a view (access project / adp)

running access 2k adp/project as front-end; and sql-server 7 as back-
end.

I would like to create my own special function that I can then
reference from a view.

how do I do this?
a function defined in a module, is apparently, not usable from a view.

ie:
public function myFn( x as integer )
myFn = x * 3.141 + 212
end function

select *, myFn( [myTable.xvalue] ) as xAnswer from myTable


TIA
0
Bob
11/18/2009 11:33:29 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
1092 Views

Similar Articles

[PageSpeed] 37

Create the function in SQL Server.

-- 
Duane Hookom
Microsoft Access MVP


"Bob" wrote:

> running access 2k adp/project as front-end; and sql-server 7 as back-
> end.
> 
> I would like to create my own special function that I can then
> reference from a view.
> 
> how do I do this?
> a function defined in a module, is apparently, not usable from a view.
> 
> ie:
> public function myFn( x as integer )
> myFn = x * 3.141 + 212
> end function
> 
> select *, myFn( [myTable.xvalue] ) as xAnswer from myTable
> 
> 
> TIA
> .
> 
0
Utf
11/19/2009 6:56:01 AM
Bob -

To return a value from a function, you need to specify the type of value 
returned in the call (I chose Double in the example below):

public function myFn( x as integer ) As Double
   myFn = x * 3.141 + 212
end function

-- 
Daryl S


"Bob" wrote:

> running access 2k adp/project as front-end; and sql-server 7 as back-
> end.
> 
> I would like to create my own special function that I can then
> reference from a view.
> 
> how do I do this?
> a function defined in a module, is apparently, not usable from a view.
> 
> ie:
> public function myFn( x as integer )
> myFn = x * 3.141 + 212
> end function
> 
> select *, myFn( [myTable.xvalue] ) as xAnswer from myTable
> 
> 
> TIA
> .
> 
0
Utf
11/19/2009 3:11:04 PM
On Nov 19, 12:56 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
> Create the function in SQL Server.
>
> --
> Duane Hookom
> Microsoft Access MVP
>
> "Bob" wrote:
> > running access 2k adp/project as front-end; and sql-server 7 as back-
> > end.
>
> > I would like to create my own special function that I can then
> > reference from a view.
>
> > how do I do this?
> > a function defined in a module, is apparently, not usable from a view.
>
> > ie:
> > public function myFn( x as integer )
> > myFn = x * 3.141 + 212
> > end function
>
> > select *, myFn( [myTable.xvalue] ) as xAnswer from myTable
>
> > TIA
> > .

TX to both Duane & Daryl  for your replies...
only 1 problem with doing a function - sql 7 does not support
functions... :(

I was hoping there might be some way to emulate this with maybe
procedures or some other method; but I haven't been able to figure out
how....

in order to use procedures, I would need to use an exec stmt within a
select in a view, and I'm not sure that can be done.

Bob
0
Bob
11/19/2009 5:46:15 PM
Can't you use something like:

select *, xvalue * 3.141 + 212 as xAnswer from myTable

-- 
Duane Hookom
Microsoft Access MVP


"Bob" wrote:

> On Nov 19, 12:56 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
> wrote:
> > Create the function in SQL Server.
> >
> > --
> > Duane Hookom
> > Microsoft Access MVP
> >
> > "Bob" wrote:
> > > running access 2k adp/project as front-end; and sql-server 7 as back-
> > > end.
> >
> > > I would like to create my own special function that I can then
> > > reference from a view.
> >
> > > how do I do this?
> > > a function defined in a module, is apparently, not usable from a view.
> >
> > > ie:
> > > public function myFn( x as integer )
> > > myFn = x * 3.141 + 212
> > > end function
> >
> > > select *, myFn( [myTable.xvalue] ) as xAnswer from myTable
> >
> > > TIA
> > > .
> 
> TX to both Duane & Daryl  for your replies...
> only 1 problem with doing a function - sql 7 does not support
> functions... :(
> 
> I was hoping there might be some way to emulate this with maybe
> procedures or some other method; but I haven't been able to figure out
> how....
> 
> in order to use procedures, I would need to use an exec stmt within a
> select in a view, and I'm not sure that can be done.
> 
> Bob
> .
> 
0
Utf
11/20/2009 5:00:06 PM
absolutely.

that technique works just fine.
problem is - the actual calculation is VERY complex, and is used in
multiple views.
so maintaining it individually in several places would be a nightmare.
additionally, because of the complexity and length of the calculation
- what I would like to do in 1 view, must be spread across many views
- it's a real mess.

a function-like solution is the only one that makes any sense.

and I think that all comes down to whether or not procedural exec's
can be used within select statements....
unless there's another approach I haven't thought of yet....

TX again...


On Nov 20, 11:00 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
> Can't you use something like:
>
> select *, xvalue * 3.141 + 212 as xAnswer from myTable
>
> --
> Duane Hookom
> Microsoft Access MVP
>
> "Bob" wrote:
> > On Nov 19, 12:56 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
> > wrote:
> > > Create the function in SQL Server.
>
> > > --
> > > Duane Hookom
> > > Microsoft Access MVP
>
> > > "Bob" wrote:
> > > > running access 2k adp/project as front-end; and sql-server 7 as back-
> > > > end.
>
> > > > I would like to create my own special function that I can then
> > > > reference from a view.
>
> > > > how do I do this?
> > > > a function defined in a module, is apparently, not usable from a view.
>
> > > > ie:
> > > > public function myFn( x as integer )
> > > > myFn = x * 3.141 + 212
> > > > end function
>
> > > > select *, myFn( [myTable.xvalue] ) as xAnswer from myTable
>
> > > > TIA
> > > > .
>
> > TX to both Duane & Daryl  for your replies...
> > only 1 problem with doing a function - sql 7 does not support
> > functions... :(
>
> > I was hoping there might be some way to emulate this with maybe
> > procedures or some other method; but I haven't been able to figure out
> > how....
>
> > in order to use procedures, I would need to use an exec stmt within a
> > select in a view, and I'm not sure that can be done.
>
> > Bob
> > .

0
Bob
11/20/2009 5:46:57 PM
absolutely.

that technique works just fine.
problem is - the actual calculation is VERY complex, and is used in
multiple views.
so maintaining it individually in several places would be a nightmare.
additionally, because of the complexity and length of the calculation
- what I would like to do in 1 view, must be spread across many views
- it's a real mess.

a function-like solution is the only one that makes any sense.

and I think that all comes down to whether or not procedural exec's
can be used within select statements....
unless there's another approach I haven't thought of yet....

TX again...


On Nov 20, 11:00 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
> Can't you use something like:
>
> select *, xvalue * 3.141 + 212 as xAnswer from myTable
>
> --
> Duane Hookom
> Microsoft Access MVP
>
> "Bob" wrote:
> > On Nov 19, 12:56 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
> > wrote:
> > > Create the function in SQL Server.
>
> > > --
> > > Duane Hookom
> > > Microsoft Access MVP
>
> > > "Bob" wrote:
> > > > running access 2k adp/project as front-end; and sql-server 7 as back-
> > > > end.
>
> > > > I would like to create my own special function that I can then
> > > > reference from a view.
>
> > > > how do I do this?
> > > > a function defined in a module, is apparently, not usable from a view.
>
> > > > ie:
> > > > public function myFn( x as integer )
> > > > myFn = x * 3.141 + 212
> > > > end function
>
> > > > select *, myFn( [myTable.xvalue] ) as xAnswer from myTable
>
> > > > TIA
> > > > .
>
> > TX to both Duane & Daryl  for your replies...
> > only 1 problem with doing a function - sql 7 does not support
> > functions... :(
>
> > I was hoping there might be some way to emulate this with maybe
> > procedures or some other method; but I haven't been able to figure out
> > how....
>
> > in order to use procedures, I would need to use an exec stmt within a
> > select in a view, and I'm not sure that can be done.
>
> > Bob
> > .

0
Bob
11/20/2009 5:47:11 PM
I'm out of suggestions since this is an adp connected to an older version of 
SQL Server :-(
-- 
Duane Hookom
Microsoft Access MVP


"Bob" wrote:

> absolutely.
> 
> that technique works just fine.
> problem is - the actual calculation is VERY complex, and is used in
> multiple views.
> so maintaining it individually in several places would be a nightmare.
> additionally, because of the complexity and length of the calculation
> - what I would like to do in 1 view, must be spread across many views
> - it's a real mess.
> 
> a function-like solution is the only one that makes any sense.
> 
> and I think that all comes down to whether or not procedural exec's
> can be used within select statements....
> unless there's another approach I haven't thought of yet....
> 
> TX again...
> 
> 
> On Nov 20, 11:00 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
> wrote:
> > Can't you use something like:
> >
> > select *, xvalue * 3.141 + 212 as xAnswer from myTable
> >
> > --
> > Duane Hookom
> > Microsoft Access MVP
> >
> > "Bob" wrote:
> > > On Nov 19, 12:56 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
> > > wrote:
> > > > Create the function in SQL Server.
> >
> > > > --
> > > > Duane Hookom
> > > > Microsoft Access MVP
> >
> > > > "Bob" wrote:
> > > > > running access 2k adp/project as front-end; and sql-server 7 as back-
> > > > > end.
> >
> > > > > I would like to create my own special function that I can then
> > > > > reference from a view.
> >
> > > > > how do I do this?
> > > > > a function defined in a module, is apparently, not usable from a view.
> >
> > > > > ie:
> > > > > public function myFn( x as integer )
> > > > > myFn = x * 3.141 + 212
> > > > > end function
> >
> > > > > select *, myFn( [myTable.xvalue] ) as xAnswer from myTable
> >
> > > > > TIA
> > > > > .
> >
> > > TX to both Duane & Daryl  for your replies...
> > > only 1 problem with doing a function - sql 7 does not support
> > > functions... :(
> >
> > > I was hoping there might be some way to emulate this with maybe
> > > procedures or some other method; but I haven't been able to figure out
> > > how....
> >
> > > in order to use procedures, I would need to use an exec stmt within a
> > > select in a view, and I'm not sure that can be done.
> >
> > > Bob
> > > .
> 
> .
> 
0
Utf
11/20/2009 9:36:02 PM
Reply:

Similar Artilces:

last 4 wk average using calculated pivotitem
Hi, I am trying to create a pivot table/chart from data that is listed b week. So my table has "week" as the row field and "total X" as th column field. I want the chart to display the total for each week as bar (the easy part) and also to graph the average of the last fou weeks as a line on top of the bar graph. I have tried to figure ou how to add a calculated pivot item but I can't seem to get it to wor right. So for each week there would be a second almost subtotal lik entry that calculates the average for the previous four weeks (if ther aren't four previo...

Access 2000 Package and Deployment Wizard problem!
I have created an Access 2000 application that I now wish to distribute to users as an MDE file. The Access 2000 Package and Deployment Wizard does a fine job of storing the relevant installation files on a CD, exploiting A2K Runtime. However, I would prefer to let users download my application from a website. The Package and Deployment Wizard offers a 'Web Publishing' deployment option that appears to address this need. My only problem is that it doesn't work for me. I get the following error message, Unexpected error number 48 has occurred: File not found: WebPost.dll I...

SQL Back-end / Access Front-end using linked tables????
I have a backend that contains tables that I pull Driver information, Customer information , etc. (these are MAS 200 accounting software tables that are exported nightly to the SQL backend). I also have on the backend tables that I push information to; information that we enter on the forms located on the front end. I might not have this set- up correctly, but I'm linking all tables on the back end to the Access database on the front end. When trying to set up an Auto Lookup off of a query I've created in Access it says I need to go into the table change Data Type to the Lookup wizard,...

autocomplete (like Access)
Hi, I'm trying to restrict the entry of data into cells based on values in a list. What I need is similar to Data Validation but I want Excel to complete the entry after the user has entered just the first character. For example, In Microsoft Access, when creating a new table in Design view, first you enter the field name, then tab across to Data Type. Entering a "c" and then tab automatically enters "Currency" as the data type. Similar for "d", DateTime; "t", Text etc. So, in Excel, if I have a list, say, New York, London, Sydney, Tokyo. Typi...

Viewing an Excel sheet w/out all the empty fields...
How do you create a spreadsheet that only shows the fileds with data in them? -How do you get rid of all the empty rows/columns, to ease viewing for those who are easily confused by excel spreadsheets? (I don't know how else to emaplin my question... I just don't want the extra columns & rows there, if that's possible...) Please help... Hi! >I just don't want the extra columns & rows there, if that's possible... Just hide them! Suppose the last column in your sheet that contains data is column H. You can hide columns I:IV so that after column H all you ...

viewing LN emails (.nsf files) saved to CD in outlook?
I have lotus notes messages saved on CD, these are saved as .nsf files. Is there any way I can view these in Outlook? have tried various LN websites - to no avail. ...

Data from Access query to Excel
To pull data from an Access 2003 database, I have created the queries in Access, then import into Excel. The problem is that all the numbers that are pulled into Excel are text and need to convert them into numbers to run formulas on. I have converted a few sheets by hand, but, some have will over 50,000 rows. Is there a function to select all number colums (the colums are the same through out the sheets) and convert? Thanks There are instructions here for converting text to numbers: http://www.contextures.com/xlDataEntry03.html You can select all the columns, and only the num...

How to view the code for excel built-in functions?
Is it possible? -For example the function PMT(). thanks. No, the code is compiled, so it would likely be less than useful anyway. About the best you can do is check out the equations used in Help (see "PV"). In article <OSU3OXOBGHA.1676@TK2MSFTNGP09.phx.gbl>, "serdar" <s@s.com> wrote: > Is it possible? -For example the function PMT(). > thanks. ...

Using XP Briefcase with money file
I put a copy of my money file into the laptop briefcase and I am able to keep them in sych for a while. For some reason I get locked out, the computer knows the file has been updated but it will not let the desk top file update the laptop file, I get the following message; "The source file can not be opened" "Check to see if the other program has the file opened, and close the file before updating the briefcase." Other files update just fine. When I get this message, I can't even drag a new copy of the Money file to my briefcase, I have to rename the file an...

Using a VBscript to copy/delete/move Outlook messages
I need to write a VBscript to: - open my Outlook (2000 or XP) mailbox - read the list of the folders in either the Exchange Mailbox and in local ..pst files - open each folder and subfolders - read information about messages (i.e. sender, recipient, creation date, and so on) - move, copy or delete messages based on the above information Can anybody please provide a pointer to a sample I can start from? Regards Marius ...

View in new Portfolio Manager
Older portfolio manager had a view available which displayed by type of security. I am unable to find this option in the new portfolio manager. Am I missing something? Charlie On May 22, 9:57=A0pm, charlie <char...@discussions.microsoft.com> wrote: > Older portfolio manager had a view available which displayed by type of > security. =A0I am unable to find this option in the new portfolio manager= .. =A0Am > I missing something? > Charlie You didn't state which version of Money you are using but I'll attempt to answer. If you are using Money Plus, you can choos...

Exchange Features Tab in ADUC when using Windows 2000 SP3
Hi, I would like to run Active Directory Users and Computers with Exchange 2003 Admin tools I'm limited to Windows 2000 Prof/SP3 Everything works,except from the Exchange Features tab. When selecting this tab, ADUC hangs. Any ideas. (I have to stay on SP3!) br TN Terminal Services is always my first choice. On Wed, 13 Apr 2005 23:35:22 +0200, "newsMS" <t_n@trashcan.dk> wrote: >Hi, > >I would like to run Active Directory Users and Computers with Exchange 2003 >Admin tools >I'm limited to Windows 2000 Prof/SP3 > >Everything works,except...

Interactive web spreaadsheet viewing
I built an interactive speadsheet for the internet in Excel XP and it works and looks fine from my PC, but when I go to another PC that doesn't have Office XP on it, I get an error message that states I need some kind of component to properly view the spreadsheet. Is this something I can get and if so, will it run from the server or does it need to be offered as a download, plugin, or whatever? Any suggestions and/or help would be greatly appreciated. Thanks!!! ...

Custom Menus in Access 2007
I know in VB 6 you can create custom pop-up menu. Is there a way to do this in Access 2007? Active X? DLL? Hidden command that I can't find? Any tutorials or direction? Any help or direction that anyone can provide would be appreciated. In earlier versions, called "shortcut" menus. Right click on menu bar, select customise, show "shortcut menus" tollbar, select "custom". don't know if that tollbar has changed in 2007. (david) "Jerry Sheldon" <JerrySheldon@discussions.microsoft.com> wrote in message news:7431915F-33FF-4534-B5B4-C5E6F...

Access 2007 Runtime 10-30-07
1. Can I distribute Accees 2003 applications using Access 2007 runtime? 2. Do I also need the runtime extensions? 3. Will installing the Access 2007 runtime and extensions affect my current Access 2003 (full version) software, already installed? ...

Setting default program for viewing pictures
How do I set the default program for viewing picture attachments? I use Outlook Express 6 and when I click to open an attached picture it opens to JASC Paint Shop Pro 8 but no picture appears. In the past Explorer opened up my pictures. Can any one give me the steps to change the default from paint Shop to Explorer?? Hi Digitdan, Change the program that starts when you open a file http://office.microsoft.com/assistance/preview.aspx?AssetID=HP051925951033&C TT=1&Origin=EC790000701033&QueryID=bSa5KrD4N0&Query=Change+the+program+that+ starts+when+you+open+a+file&Scop...

View both incoming/outgoing messages by Thread or Conversation ? #2
Is it possible to view messages in outlook 2003 by Thread or Conversation, both incoming and outgoing, in a single folder, or Inbox, just like Outlook Express News Reader. For example: + Sent Message + Received Reply +Sent Message or Replied + Received Reply I tried using Arrange by Conversation Option in outlook 2003 Inbox Folder, but its just showing Inbox Messages, not Sent Items Messages. Best Regards, Luqman ...

Using a variable for a chart limit
Since I got instant help on my last query, is there any way to use a cell reference as an axis minimum or maximum in format axis? It seems impossible, but there is a lot of experience out there. Thanks in advance. -- Vince F Hi, See Jon's information http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html Cheers Andy Vince F wrote: > Since I got instant help on my last query, is there any way to use a cell > reference as an axis minimum or maximum in format axis? It seems impossible, > but there is a lot of experience out there. Thanks in advance. -- Andy Pop...

How do I make door hangers using publisher?
Is this even possible? Mary Sauer has a website with a generous collection of goodies including door hangers. Take a look at just above George Washington's portrait (way down the page) at, http://msauer.mvps.org/publisher_projects2.htm -- Don - Publisher 2000� Vancouver, USA "medinapie" <medinapie@discussions.microsoft.com> wrote in message news:986B8199-C9B5-46CE-8F2F-C772BE5C21BC@microsoft.com... > Is this even possible? I have a sample on my web page. Scroll down -- on the left. http://msauer.mvps.org/graphics.htm Some cute ones here that you could mod...

What program is trying to access e-mail address information stored in outlook?
I have one user (Vista Business, Outlook 2007 connected to Exchange 2003) who has today started to see a pop-up from Outlook saying: "A program is trying to access e-mail address information stored in Outlook. If this is unexpected, click Deny and verify your antivirus software is up-to-date." Anti-virus (McAfee) is up todate, and I'm doing a scan now and afterwards will do a separate scan for malware. This user tells me that he hasn't knowingly installed anything recently. My question is: is there any way to determine what the program is which is try...

using program to record audio comment and compress audio to MP3
I can use VBA to record an audio comment into a Word file using Selection.InlineShapes.AddOLEObject ClassType:="SoundRec", FileName:="", _ LinkToFile:=False, DisplayAsIcon:=True, IconFileName:= _ "C:\WINDOWS\system32\sndrec32.exe", IconIndex:=0, IconLabel:="Wave Sound" BUT but by default the file is stored as a PCM at 44kHz and 16 bit stereo whcih is about 200kB per second Sound recorder (which is used to do the recording) allows me to manually convert this to a MP3 and a much smaller file e.g. 3kB per second Is there any way I can aut...

Multiple entries in CRM Contacts when viewed from Outlook
Hi everyone, We have an odd problem going on here with this. When we create a new E-mail in Outlook, select the To button and the CRM Contacts, it brings up the list of contacts from CRM that we can select from. All good so far. The problem is that each contact appears several times and it's different for different users. For example, every contact appears 6 times for me. It makes for a very long list. All of them are valid and any one of those 6 can be selected and the address will be correct and it will track against the correct contact. Other users have anywhere from the correc...

HTML view with outlook and pocket pc 2003
When I download email from a pop3 server, the messages are displayed only in text format not in HTML format, i can't see the images in the email, I only see lines or dashes. Check with microsoft.public.pocketpc for help with PPC. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After searching google.groups.com and finding no answer Mike Mikhail <smikhail@socal.rr.com> asked: | When I download email from a pop3 server, the messages | are d...

Can I use oulook from home and have access at work?
When I set up my outlook I lose all my inbox messages online and would not be able to read my email from work. Is there a way to have my inbox available on outlook and remain online? Just to clarify, are you asking how to set up Outlook at home to receive email from work? If that's your question, you need to ask your IT Admin or Exchange Admin if you can use OWA and how that's done. That wouldn't have email coming into your Outlook account but it is a form of Outlook, Outlook Web Access. This may or may not be allowed. Your IT staff would of course have all kinds of securi...

Access 2003 and Sharepoint
My company is moving most of our files into Sharepoint -- however, it seems no one could explain how Sharepoint is going to affect our current Access database. Can someone please enlighten me regarding Sharepoint and how it will benefit us if our Access database is stored in it? Will it make accessing the database faster through our network? Right-now our database is stored in a network drive and depending on where we are located from the server -- it may take long to accesst the data. Or any sites that will help explain the process of Sharepoint. Thank you, Sharon You cannot store your A...