Call function from query 'Criteria' line. - microsoft.public.access.queries

I am trying to call a Public function from the criteria line in the design view of the query builder in Access 2003. I will try and recreate the look: Field: Account_ID Control_Date Table: Historical_Table Historical_Table Sort: Show: (checkmarked) (checkmarked) Criteria: <> "999999" GetDate() Public Function getdate() As Date GetDate = "#" & Date & "#" End Function When I try and run it, I receive the error : Undefined function 'GetDate' in expression. What am I doing wrong? Is it not possible to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

Is GetDate in a VBA module and not in a form or report module? It should be in VBA module. Your function should be more like Public Function getdate() As Date GetDate = Date End Function Adding the "#" at the beginning and ending turns the data type into a string. And then you will get a error #13 Type mismatch when you try to assign that to GetDate to return the value. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Breecy via AccessMonster.com wrote: > I am trying to call a Public function from the criteria line in the design > view of the query builder in Access 2003. I will try and recreate the look: > > Field: Account_ID Control_Date > Table: Historical_Table Historical_Table > Sort: > Show: (checkmarked) (checkmarked) > Criteria: <> "999999" GetDate() > > > > Public Function getdate() As Date > GetDate = "#" & Date & "#" > End Function > > When I try and run it, I receive the error : Undefined function 'GetDate' in > expression. What am I doing wrong? Is it not possible to do this? >

Are you wanting to use current date or is 'Date' in your function a field in your record? If current date then use -- Date() If a field in record use -- [Date] BTW 'date' is a reserved word and may cause problems. -- Build a little, test a little. "Breecy via AccessMonster.com" wrote: > I am trying to call a Public function from the criteria line in the design > view of the query builder in Access 2003. I will try and recreate the look: > > Field: Account_ID Control_Date > Table: Historical_Table Historical_Table > Sort: > Show: (checkmarked) (checkmarked) > Criteria: <> "999999" GetDate() > > > > Public Function getdate() As Date > GetDate = "#" & Date & "#" > End Function > > When I try and run it, I receive the error : Undefined function 'GetDate' in > expression. What am I doing wrong? Is it not possible to do this? > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 > > . >

Breecy - Why don't you just use = Date() in the criteria? Your problem is with your function, which is typed to return a date, but then you are appending the pound signs before and after the date and trying to return that (which is no longer a date). -- Daryl S "Breecy via AccessMonster.com" wrote: > I am trying to call a Public function from the criteria line in the design > view of the query builder in Access 2003. I will try and recreate the look: > > Field: Account_ID Control_Date > Table: Historical_Table Historical_Table > Sort: > Show: (checkmarked) (checkmarked) > Criteria: <> "999999" GetDate() > > > > Public Function getdate() As Date > GetDate = "#" & Date & "#" > End Function > > When I try and run it, I receive the error : Undefined function 'GetDate' in > expression. What am I doing wrong? Is it not possible to do this? > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 > > . >

I am actually in the design view of a new query. I wish I could past pictures in here I have what is in the query and then what the public Function looks like. I would like to use date(), but when I do I get no result, but if I manually type in #12/10/2009# into the query criteria it works like a champ. I was hoping to put it in the return of the fuction so it would work. I can put an SQL statement in code and make this work, but I wanted to call the function from the criteria line of the query. Make sense? Daryl S wrote: >Breecy - > >Why don't you just use = Date() in the criteria? > >Your problem is with your function, which is typed to return a date, but >then you are appending the pound signs before and after the date and trying >to return that (which is no longer a date). > >> I am trying to call a Public function from the criteria line in the design >> view of the query builder in Access 2003. I will try and recreate the look: >[quoted text clipped - 11 lines] >> When I try and run it, I receive the error : Undefined function 'GetDate' in >> expression. What am I doing wrong? Is it not possible to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

Is Control_Date DateTime datatype? Put this in your query grid to see what is returned -- My_Date_Test: CDbl([Control_Date]) and My_Date_DblCk_Test: CVDate(CDbl([Control_Date])) -- Build a little, test a little. "Breecy via AccessMonster.com" wrote: > I am actually in the design view of a new query. I wish I could past > pictures in here I have what is in the query and then what the public > Function looks like. I would like to use date(), but when I do I get no > result, but if I manually type in #12/10/2009# into the query criteria it > works like a champ. I was hoping to put it in the return of the fuction so > it would work. > > I can put an SQL statement in code and make this work, but I wanted to call > the function from the criteria line of the query. Make sense? > > Daryl S wrote: > >Breecy - > > > >Why don't you just use = Date() in the criteria? > > > >Your problem is with your function, which is typed to return a date, but > >then you are appending the pound signs before and after the date and trying > >to return that (which is no longer a date). > > > >> I am trying to call a Public function from the criteria line in the design > >> view of the query builder in Access 2003. I will try and recreate the look: > >[quoted text clipped - 11 lines] > >> When I try and run it, I receive the error : Undefined function 'GetDate' in > >> expression. What am I doing wrong? Is it not possible to do this? > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 > > . >

Here is a subset of the data that is returned: ACCOUNT_ID CONTROL_DATE NET_ASSETS My_Date_Test My_Date_DblCk_Test 100601 3/3/2009 0 39875 3/3/2009 100601 3/4/2009 0 39876 3/4/2009 100601 3/5/2009 0 39877 3/5/2009 100601 3/6/2009 0 39878 3/6/2009 100601 3/9/2009 0 39881 3/9/2009 100601 3/10/2009 0 39882 3/10/2009 100601 3/11/2009 0 39883 3/11/2009 100601 3/12/2009 0 39884 3/12/2009 100601 3/13/2009 0 39885 3/13/2009 100601 3/16/2009 0 39888 3/16/2009 100601 3/17/2009 0 39889 3/17/2009 100601 3/18/2009 0 39890 3/18/2009 100601 3/19/2009 0 39891 3/19/2009 100601 3/20/2009 0 39892 3/20/2009 100601 3/23/2009 0 39895 3/23/2009 100601 3/24/2009 0 39896 3/24/2009 100601 3/25/2009 0 39897 3/25/2009 100601 3/26/2009 0 39898 3/26/2009 100601 3/27/2009 0 39899 3/27/2009 100601 3/30/2009 0 39902 3/30/2009 100100 3/31/2009 0 39903 3/31/2009 100101 3/31/2009 0 39903 3/31/2009 100102 3/31/2009 0 39903 3/31/2009 100103 3/31/2009 0 39903 3/31/2009 100104 3/31/2009 0 39903 3/31/2009 100105 3/31/2009 0 39903 3/31/2009 100106 3/31/2009 0 39903 3/31/2009 100107 3/31/2009 0 39903 3/31/2009 100108 3/31/2009 0 39903 3/31/2009 100109 3/31/2009 0 39903 3/31/2009 100110 3/31/2009 0 39903 3/31/2009 What is this data telling me? KARL DEWEY wrote: >Is Control_Date DateTime datatype? >Put this in your query grid to see what is returned -- > My_Date_Test: CDbl([Control_Date]) >and > My_Date_DblCk_Test: CVDate(CDbl([Control_Date])) > >> I am actually in the design view of a new query. I wish I could past >> pictures in here I have what is in the query and then what the public >[quoted text clipped - 19 lines] >> >> When I try and run it, I receive the error : Undefined function 'GetDate' in >> >> expression. What am I doing wrong? Is it not possible to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

Hi - My_Date_Test is returning the date as it is stored internally by Access. Try pasting this into the SQL view of a new query. SELECT Account_ID, Control_Date FROM Historical_Table WHERE (((Control_Date)=Date())); Bob -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

But if it don't recognize the function then it doesn't matter how I format is passed back, does it. John Spencer wrote: >Is GetDate in a VBA module and not in a form or report module? It should be >in VBA module. > >Your function should be more like > >Public Function getdate() As Date > GetDate = Date >End Function > >Adding the "#" at the beginning and ending turns the data type into a string. >And then you will get a error #13 Type mismatch when you try to assign that to >GetDate to return the value. > >John Spencer >Access MVP 2002-2005, 2007-2009 >The Hilltop Institute >University of Maryland Baltimore County > >> I am trying to call a Public function from the criteria line in the design >> view of the query builder in Access 2003. I will try and recreate the look: >[quoted text clipped - 11 lines] >> When I try and run it, I receive the error : Undefined function 'GetDate' in >> expression. What am I doing wrong? Is it not possible to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

Also all I should have stated before, but if the date that I am pulling is a holiday, I want to be able to specify the date that I want to use. raskew wrote: >Hi - > >My_Date_Test is returning the date as it is stored internally by Access. > >Try pasting this into the SQL view of a new query. > >SELECT Account_ID, Control_Date >FROM Historical_Table >WHERE (((Control_Date)=Date())); > >Bob -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

Do you have any data with today's date? If not, the query with the criteria of =Date() will not have any records to return. Do you want to check for yesterday's data as in your query from the 11th (using December 10th as the date)? If so, try =Date() - 1 in your criteria. -- Daryl S "Breecy via AccessMonster.com" wrote: > Here is a subset of the data that is returned: > > ACCOUNT_ID CONTROL_DATE NET_ASSETS My_Date_Test My_Date_DblCk_Test > 100601 3/3/2009 0 39875 3/3/2009 > 100601 3/4/2009 0 39876 3/4/2009 > 100601 3/5/2009 0 39877 3/5/2009 > 100601 3/6/2009 0 39878 3/6/2009 > 100601 3/9/2009 0 39881 3/9/2009 > 100601 3/10/2009 0 39882 3/10/2009 > 100601 3/11/2009 0 39883 3/11/2009 > 100601 3/12/2009 0 39884 3/12/2009 > 100601 3/13/2009 0 39885 3/13/2009 > 100601 3/16/2009 0 39888 3/16/2009 > 100601 3/17/2009 0 39889 3/17/2009 > 100601 3/18/2009 0 39890 3/18/2009 > 100601 3/19/2009 0 39891 3/19/2009 > 100601 3/20/2009 0 39892 3/20/2009 > 100601 3/23/2009 0 39895 3/23/2009 > 100601 3/24/2009 0 39896 3/24/2009 > 100601 3/25/2009 0 39897 3/25/2009 > 100601 3/26/2009 0 39898 3/26/2009 > 100601 3/27/2009 0 39899 3/27/2009 > 100601 3/30/2009 0 39902 3/30/2009 > 100100 3/31/2009 0 39903 3/31/2009 > 100101 3/31/2009 0 39903 3/31/2009 > 100102 3/31/2009 0 39903 3/31/2009 > 100103 3/31/2009 0 39903 3/31/2009 > 100104 3/31/2009 0 39903 3/31/2009 > 100105 3/31/2009 0 39903 3/31/2009 > 100106 3/31/2009 0 39903 3/31/2009 > 100107 3/31/2009 0 39903 3/31/2009 > 100108 3/31/2009 0 39903 3/31/2009 > 100109 3/31/2009 0 39903 3/31/2009 > 100110 3/31/2009 0 39903 3/31/2009 > > > What is this data telling me? > > KARL DEWEY wrote: > >Is Control_Date DateTime datatype? > >Put this in your query grid to see what is returned -- > > My_Date_Test: CDbl([Control_Date]) > >and > > My_Date_DblCk_Test: CVDate(CDbl([Control_Date])) > > > >> I am actually in the design view of a new query. I wish I could past > >> pictures in here I have what is in the query and then what the public > >[quoted text clipped - 19 lines] > >> >> When I try and run it, I receive the error : Undefined function 'GetDate' in > >> >> expression. What am I doing wrong? Is it not possible to do this? > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 > > . >

O.K. All, I have solved the mystery!!!! Here is the key to calling a function in a query: It has to be in a module. I thought since I declared my function as public that I could put my code in the form object, but no. So thanks to everyone for their time and effort on helping me solve this mystery. Breecy wrote: >I am trying to call a Public function from the criteria line in the design >view of the query builder in Access 2003. I will try and recreate the look: > >Field: Account_ID Control_Date >Table: Historical_Table Historical_Table >Sort: >Show: (checkmarked) (checkmarked) >Criteria: <> "999999" GetDate() > >Public Function getdate() As Date >GetDate = "#" & Date & "#" >End Function > >When I try and run it, I receive the error : Undefined function 'GetDate' in >expression. What am I doing wrong? Is it not possible to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

Similiar Articles:

User Defined Query Function Not Working with Multiple Criteria ...
Using Access 2007 I created a user defined function used in a query that works fine on single criteria ... If you have done that then you would call the function with ...

Using a Variable as a query Expression Value - microsoft.public ...
Hi, I've been reading a past post "Passing a Variable As Query Criteria ... in a form or report" I have done this, but how do you call the Variable/function from the query ...

Calling user-defined function from adp - microsoft.public.access ...
User Defined Query Function Not Working with Multiple Criteria ... Calling user-defined function from adp - microsoft.public.access ... User Defined Query Function Not ...

Ampersand in Query Criteria - microsoft.public.access.queries ...
How can I escape the ampersand used in query criteria? If I type a & b as ... It is important to include the use of the Replace() > function if the criteria can ever ...

Use Function in Query problem - microsoft.public.access.queries ...
Hi All, I'm trying to run an access query using a function I ... I assume you have a saved query, let's call it qryArchive. ... that the passed >> value meets some criteria ...

Create Temp table from query, with memo fields? - microsoft.public ...
The data must not be that large, but i'd like to see what you call "functions or ... Use Table field in query criteria - microsoft.public.access ... Create Temp table ...

Criteria for age calculation - microsoft.public.access
I want to calculate the age in a query. I have tried different criteria from MS Access ... IIf(intMonths <> 1, "s", "") End If End Function Call it ...

Multiple values in criteria field - microsoft.public.access ...
... the values from a field on a form or from a function called from the query ... If I hard code in the line in the criteria field ... How do I query multiple data values in ...

null value pass from form into query - microsoft.public.access ...
... and it executes a query with criteria ... to work where I can call a null or non-null value into the query ... using NZ() function to return ''0'' value when query is null ...

Query criteria based on table entries - microsoft.public.access ...
I have added "Not In ("custname1","custname2" and so on) to my query criteria line and ... based on type of service, last performed vs calendar vs ... What function will ...

Call Function. How does this code work. - microsoft.public.access ...
... am contemplating using it in my project but I don't really understand the call function ... much better not to get Forms involved at all, but instead run an Append query ...

Create a Query that Prompts for Date and Time - microsoft.public ...
I am in the beginning stages of setting up a call database that tracks calls ... Create a Query that Prompts for Date and Time - microsoft.public ... date query criteria ...

IIF statement in query criteria, help! - microsoft.public.access ...
iif function in query criteria - Microsoft Answers I have a query that uses the following as criteria in the [lockprofit?] column: IIf([forms]![select ...

criteria for filtering check boxes in a query - microsoft.public ...
Hello all, I have a question regarding a custom function. The query results can't be filtered nor can a criteria be used in the design window to filter/ restrict the ...

Not Like - not working in query criteria - microsoft.public.access ...
Parameter Criteria from a forms Text box - 2003 - not working ... Parameter query with and multiple "NOT / OR" selection criteria ..... Query Function Not Working with ...

Function of colon between table names when query in design view ...
... the following syntax: CALL query ... don't have to open the query in Design View ... Query criteria ... the lower area of the query design to insert another line for functions ...

Excel User Defined Function to Access Function - microsoft.public ...
User Defined Query Function Not Working with Multiple Criteria ... Using Access 2007 I created a user ... can call user-defined functions asynchronously. Calling functions ...

Summing up multiple line items per invoice number (RefNumber ...
This is the result I get with the following query ... If I use the aggregate Sum function for either it creates ... only the records that meet the WHERE clause criteria to ...

Call a subform function from the form? - microsoft.public.access ...
What is the syntax for calling a function in > a > subform module from the module for the ... The form countrack is behind a query also called countrack that will...

if.. then.. else.. in a query - microsoft.public.access.queries ...
Kim, To do this in a query, I prefer to use the Switch function, which evaluates a series of ... to calculate the value of a field based on > other criteria in a ...

Call function from query 'Criteria' line. DataBase
I am trying to call a Public function from the criteria line in the design view of the query builder in Access 2003. I will try and recreate the look: F

Call function from query 'Criteria' line.
I am actually in the design view of a new query. I wish I could past pictures in here I have what is in the query and then what the public Function looks like.

Calling user-defined function from query - Microsoft Access / VBA
Calling user-defined function from query. Microsoft Access / VBA ... get this from the query, I call the function - by typing "GetPropertyCode()" in the criteria of "Owner ...

Can I call a function in query criteria? DataBase
DataBase - Can I call a function in query criteria? ... Should I be able to run a function in the query criteria? It may just not have ...

Function In Query Criteria - Microsoft Access / VBA
Function In Query Criteria. Microsoft Access / VBA Forums on Bytes. ... problem calling date function from query; Blank Query Parameter Problems; Query Parameter ...

VBA Function Call in Query - dBforums
I wrote a function which I call via a simple SQL SELECT statement. The SQL passes ... the first query, based on its specified unique ID from the criteria applied Second Query.

Can we call a function in sql query and what are the pre ...
SQL - Can we call a function in sql query and what are the pre requisites to call a function in sql query? . 9 Answers are available for this question.

How to: Call User-Defined Functions Inline (LINQ to SQL)
Although you can call user-defined functions inline, functions that are included in a query whose execution is deferred are not executed until the query is executed.

MS ACCESS :: Call Function In Query To Get Variable
Unfortunately, the query doesn't work if the criteria is generated by the function call. However, if I hardcode the criteria (don't send a function call but directly ...

Using a VBA Variable to Filter a Query in Access 2007
... the function as part of a calculation, or reference it in the criteria of a field. In other words, the only way to work with VBA in a query is to call a function.

7/19/2012 3:06:32 PM