Date Time

[cg084]

Hello,

I havev written a query for a database. The query is to look at records created today. The design of the table is such that when a record is completed that the date and time is recorded in a field called "date raised".

In my query i am trying to find only the records created today so have used -Date() to do this. The only problem is that it is not finding these records with the auto date/time function. However if the date is overidden by the operator that record will appear in the results. (Subject to it being created on the same day.

I need to be able to capture those records which have automatically had the date/time information added.

Hope you can help.

Cheers CG084

 

[KenReay]

Hi

How are you populating the date raised 'automatically', are you perhaps using NOW() ?, if yes, that will store the date and time and will not give a match when compared with date, to check it out you could try a criteria of >=Date()-1 and <=Date()+1

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cheerio]

Access stores date/time as a number like 37921.5

Here 37921 is the number of days since 1900 and represents 27 Oct 2003

The .5 is half a day telling us that we are talking about noon on 27 Oct.

If your users key in the date they key 27 Oct 2003 or some equivalent such as 10/27/2003 and that gets stored as 37921 which you match.

When the computer adds the date your are probably using the now() function which adds in the time as well. If you test for 37921 by using the date function there is no match against 37921.5

What you could do is add a column to your query where you surround the date field with an Int function to throw away the time.

Alternatively, you can use a BETWEEN criteria such as
BETWEEN date() AND date()+0.9999999

Alternatively you could test for >=date() and < date()+1

It's a question of taste and performance.

Finally you could store exact dates rather than date/times by changing the way the computer puts dates into the table.

 

[cg084]

All,

Thank you for your help. I would just have changed the data type of the field to overcome this but it was a design change they required. My alternative was for a hidden date only field to be added to the table.


Once Again Cheers.