date criteria

[bjackson]

could anyone please tell me what criteria to use in a query
that would return all order dates that were made today, when
the table holding the dates is date/time format

i am trying to find any orders that were placed to day regardless
of what time they were placed.i tried using date() but that doesnt work
any help much appreciated

 

[Mile-O]

Make a new field and build it with the expression:

NewField: DateValue([YourField])


Set the criteria to Date()

 

[bjackson]

Thanks Mile-O-File
that worked a treat

 

[Ceejay64]

Make a new field and build it with the expression:

NewField: DateValue([YourField])


Set the criteria to Date()

Hey Mile...is this an A2K solution only? It didn't work for me (A97).
Thanks!

 

[Mile-O]

No; it's the exact same for Access 97.

Maybe it's your date settings.

What's the SQL of your query?

 

[Ceejay64]

I tried it again with a fresh query, just using one field along with the new DateValue field, and it worked this time.

SELECT tblProcessing2.[4DateStopTime], DateValue([4DateStopTime]) AS 4Due
FROM tblProcessing2
WHERE (((DateValue([4DateStopTime]))=Date()));

Don't know why it didn't work before...
I'll keep at it. Thanks for all your great advice, BTW - you're a lifesaver!

 

[Ceejay64]

Hmmm...found a possible issue. Any record that had a null in the date field that I want to check causes an error ("data type mismatch in query criteria"). Any ideas how to get around this? Some of the fields are going to be blank as they won't have been done yet.

 

[Brianwarnock]

This worked for me, I'm never going back to 30/12/1899

SELECT tblgendates.sdate, tblgendates.edate, Format(IIf(IsNull([edate]),0,[edate]),"dd/mm/yyyy") AS datedu
FROM tblgendates
WHERE (((Format(IIf(IsNull([edate]),0,[edate]),"dd/mm/yyyy"))=Date()));


Brian

 

[Mile-O]

Hmmm...found a possible issue. Any record that had a null in the date field that I want to check causes an error ("data type mismatch in query criteria"). Any ideas how to get around this? Some of the fields are going to be blank as they won't have been done yet.

Use the Nz() function around your date field.

 

[Brianwarnock]

Use the Nz() function around your date field.

Hmm! I tried nz function first and couldn't get it to work just tried again and it did , guess I had a simple error first time that I didn't spot. However can you explain to this ignoramous why when using my first posted solution the data appears right justified in the field, as expected, and using nz the data appears left justified

Brian

 

[Jon K]

However can you explain to this ignoramous why when using my first posted solution the data appears right justified in the field, as expected, and using nz the data appears left justified

When Right justified -- the returned values are numeric i.e. numbers or dates

When Left justified -- the returned values are text


Nz([...]) standing alone returns text, even if [...] is numeric.