Date Function in query

R

RoyS1031

I have two tables. One is INVOICES, the other is "INVENTORY. Each has a
DATEMODIFIED field. The dates in INVENTORY are formatted dd/mm/yyyy
hh:mm:ss. Yet dates in INVOICES are formatted dd/mm/yyyy without any time
stamp. The Date() function as query criteria works on the field formatted
dd/mm/yyyy but does not on the other. Both fields are filled by code. Both
tables have the DATEMODIFIED field formatted the same in the design grid.
Why is one table saving dates with the time stamp and the other table is not?
 
K

Ken Snell

Date values are always stored with a time value, but if you stored just the
date into the field, the time value equals "midnight" for that day. Date
values are stored as floating point numbers, where the integter portion
(date) is the number of days since December 30, 1899, and the decimal part
(time) is the fractional part of a 24-hour day. So, if you store just the
date into the field, you'll get a number like 3467.000000000000000000.

In the field where you have time values other than "zero", you likely are
using the Now function to store the value into that field. Now gives you
both date and time. Date function gives you just date, which likely is what
you're using to store the value into the field without non-zero times.
 
R

RoyS1031

Thanks, Ken. You are exactly right. I discovered the NOW function in the
code and changed it to the DATE Function. My data is now being stored
correctly and the DATE() function works in the query.

Your time as well as you explination is appreciated very much.

Have a great day.

RoyS1031
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top