Can you query just the date from the =now() 3

[iowabuckmaster]

If the record gets changed I populate a field with the =now() function using the BeforeUpdate. User wants both date and time. The field only displays the date but the time is included. Works fine. Now they also want a nightly report that shows all records changed or created for the day. Here is MY QUESTION: Is there a way to just query out the previous days date (Run after midnight) from the field that has date and time from the =now()??? Here is how I have gotten around it, on change I am storing the date in an extra field by using =Date() Then quering using Date() -1. This seems reduntant if the date is sitting in the other field already. Any ideas?

 

[JonFer]

You can use CDate(Int(DateField)) to drop the time from your field and then compare to Date()-1

OR use this comparison:

>=Date()-1 and <Date() ' on or after midnight prior day and less than midnight current day

OR

Format(DateField,&quot;yyyymmdd&quot = Format(Date()-1,&quot;yyyymmdd&quot

 

[dhookom]

Actually DateValue([DateTimeExpression]) works great for removing the time portion.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[BobJacksonNCI]

I haven't done this, but think it should work:

format([tblDate], &quot;short date&quot = date() -1

HTH,
Bob

 

[iowabuckmaster]

Thanks all. I used a variation of the format you all suggested. Which formats to short date. Which is 2 in the FormatDateTime function.

Date Only: FormatDateTime([Changed Record Date Time],2)
in the citeria I used Date() -1

I had to create the field Date Only in this query.

But
If I could place the following suggested criteria
>=Date()-1 and <Date() in the [Changed Record Date Time] field I wouldn't have to create the Date Only field. But I could not make this work.

 

[dhookom]

I count 5 different but usefull suggestions. I try to use the solution that does the least amount of data conversion. This generally rules out using the Format() function.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[iowabuckmaster]

I got it with the least amount of data conversion. Actually I did not use any data conversion. I used JonFer's suggestion of

>=Date()-1 and <Date() ' on or after midnight prior day and less than midnight current day

I'm not sure what I did wrong the first time I said I could not get it to work. But everything good now and I dropped the extra formatted field. Thanks again all.


&quot;Good thing my bow was in hand, I never saw him until he was at 20 yards! 157 7/8 typ