Date format issue

  • Thread starter Thread starter flava17
  • Start date Start date
F

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! :)
 
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
 
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
..
 
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
 
Back
Top