Call function from query 'Criteria' line.

[Breecy via AccessMonster.com]

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?

 

[John Spencer]

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

 

[KARL DEWEY]

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.

 

[Daryl S]

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).

 

[Breecy via AccessMonster.com]

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?

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?

 

[KARL DEWEY]

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.

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?

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?

--



.

 

[Breecy via AccessMonster.com]

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?

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]

 

[raskew via AccessMonster.com]

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

 

[Breecy via AccessMonster.com]

But if it don't recognize the function then it doesn't matter how I format is
passed back, does it.

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?

 

[Breecy via AccessMonster.com]

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.

 

[Daryl S]

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

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?

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?

--



.

 

[Breecy via AccessMonster.com]

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.