query problem related to date format

[rdw456]

Hello all
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records. I really need help on this one.

Thanks Bob

 

[Brianwarnock]

If you want to work with the date only the Datevalue(yourfield) will do it.

Format is for display purposes the time will still be in the field if it was entered.

Brian

 

[pr2-eugin]

Bob, try DateValue of the Field as the Criteria, instead of using the Whole field..

SELECT theFields FROM theTable
WHERE DateValue(theDateField) = [PleaseEnterDate:];

EDIT: Brain, all the way !!

 

[Brianwarnock]

Paul that should be DateValue not DatePart in your code, as per your text.

Brian

Ah! you spotted it and changed it whilst I was typing.

 

[pr2-eugin]

Ha ha.. Yea.. I said DateValue, and my manager was chatting away something I lost track of what I was doing..

 

[rdw456]

Hello all
Thanks for all your help but did manage to solve my problem. I used Now() as the default value which gives time as soon as I used Date() problem solved

Silly Me LOL

 

[Steve@trop]

I'm having a similar problem. I've got a table with a date/time field. When I query that field with =date() as the criteria, it returns no records even though I know there is a record with today's date in that field. I think I need to filter out the time in order for it to work but I can't figure out the syntax for that. I know I'm missing something obvious here. If I edit the record and remove the time information, the query works so I know it has something to do with the time.

Thanks for any help!

Steve

 

[Steve@trop]

I think I have a solution. If I use >date() it works. This way it will look at the entire date instead of the date at 12:00 AM (00:00:00).

 

[Brianwarnock]

Posts #2 and 3 gave you the syntax for Datevalue.
Sure using >Date() works for the reason stated, but not convinced that it is a professional approach, what happens if there is an entry for 00:00:00 ?

Brian

 

[Steve@trop]

Thanks Brian,

I actually tried those but for some reason Datevalue didn't work.

The name of the table is NorthCompressorRoundT
The Date/Time field is LoggedAt

When I use =DateValue([NorthCompressorRoundT.loggedat]) in the criteria, it doesn't find the record from today. Perhaps I have the syntax wrong.

I just tried using >=Date() and that works. I added another record with today's date with a time of 12:00:00 AM and it picks up both of them. One funny quirk that I saw with this: when I entered the time of 12:00:00 AM the field removed the time and just showed the date with no time.

 

[Brianwarnock]

DateValue([NorthCompressorRoundT.loggedat])

Goes in the field row and = Date() in the criteria

Brian

 

[Steve@trop]

That's it! Thanks!