Call function from query 'Criteria' line.

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/For...eries/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/For...eries/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/For...eries/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/For...eries/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/For...eries/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/For...eries/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/For...eries/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/For...eries/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/For...eries/200912/1