comapre dates

[jon666]

This is probably a simple question, but i could not work it out. I have an access Db which has many oracle tables linked.

I only want to get data when the data matchs a certain date. But the date format from oracle includes a date stamp that i want to ignore.

e.g.
dd/mm/yyyy hh:mm:ss


so i only want my users to input a date in the format dd/mm/yyyy, and compare it against the dates in the oracle table ,but obviously it doesn't match the oracle date format so no rows are returned.

thanks in advance.

p.s. i'm very new to this access thing and any help greatly received

 

[FloBob]

theres a function called formatdatetime that you might be interested in. You can find out info in help but its relitively straight forward let me know if you still need help.

 

[jon666]

i'm using access 2000 and i cant find the function, can you help please

 

[raskew]

A Date/Time data type is stored as a double-precision, floating-point number (up to 15 decimal places).
- The integer portion of the double-precision number representing the date
- The decimal portion representing the time as a portion of a whole day

Example from the debug window:

MyDate = now()
? MyDate
7/31/02 6:26:04 PM

To see how it's stored in Access:
? cdbl(MyDate)
37468.7681018518

There are several methods you could use to extract just the date portion. The DateValue() function is probably as easy as any.

? DateValue(MyDate)
7/31/02

? cdbl(DateValue(MyDate))
37468

Although there's no available Oracle table to test this against, suspect that by referring to your Oracle date using the DateValue() examples shown above would hopefully provide you with a valid comparison.