Date format issue

[flava17]

I am under-going a college project that involves a supermarket
delivery system.
One of the fields in my tables looks up a date from another table.
However, when this value is looked up access seems to change the data
type to a "Number" rather than keeping it as a "Date/Time" data type.

In one of my queries I require this date to compare whether it is less
than Date() - however it will not let me compare the date with type
"Number" to the function Date(). Is there any way I can compare these
values by:

a) Creating a custom field in my query that changes the Date() value
to a number and then being able to compare the two number values

b) Creating a customer field in my query that changes the number value
of the date to an actual date value?

Hope you understand, thanks!

 

[wj8801]

I am under-going a college project that involves a supermarket
delivery system.
One of the fields in my tables looks up a date from another table.
However, when this value is looked up access seems to change the data
type to a "Number" rather than keeping it as a "Date/Time" data type.

In one of my queries I require this date to compare whether it is less
than Date() - however it will not let me compare the date with type
"Number" to the function Date(). Is there any way I can compare these
values by:

a) Creating a custom field in my query that changes the Date() value
to a number and then being able to compare the two number values

b) Creating a customer field in my query that changes the number value
of the date to an actual date value?

Hope you understand, thanks!

Man you crazy

 

[John Spencer]

Try wrapping the returned number in CDate.
Or
use CDbl around the date to force it to a number

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[flava17]

Try wrapping the returned number in CDate.
Or
use CDbl around the date to force it to a number

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.










- Show quoted text -

When I use CDate, I get the wrong date value.
For example, when the OrderDate is 05/04/2007, the CDate value gives
me 11/01/1900.

When I use CDbl, I seem to get the value of the primary key of the
date from my orders table. So 05/04/2007 will return 12, because the
ID of that field is 12.

I have uploaded an example file to show you what I mean. I am trying
to use the <Date() function in the criteria so that my query returns
orders that are less than today's date.

<b>Example file:</b> http://www.ectoprods.com/uploads/db8.mdb