Date / Time Query - come out blank?

[Grounded]

Ok, so i have a field - dater - that stores date/time values in the standard way - 38362.931065 - for example.

I need to be able to query this field by date only. I have read a bunch of 'solutions' and tried them. they all produce blank results when querying for dates that do exist in the data.

The values that I have tried for the expression and criteria include:

Expr1: Format([dater],"mm-dd-yyyy")
Criteria: 1-11-2005 - blank results
Criteria: #1/11/2005# - blank results

dater
Criteria: #1/11/2005# - blank results
Criteria: format("dater", "mm-dd-yyyy") = "1-11-2005" = blank results
Criteria: format("dater", "mmddyyyy") = #1-11-2005# = blank results
Criteria: format("dater", "mmddyyyy") = #01112005# = blank results
Criteria: format("dater", "mmddyyyy") = "01112005" = blank results

Expr1: formate([dater],"mmddyyyy")
criteria: 01112005 - returns the results

The last one is the only one that runs successfully - but seems very cludge to me. Is there a way to do it without using a text string (that works!)

thanks

 

[CautionMP]

[tt]Expr1: DateValue([dater])
Criteria: #1/11/2005#[/tt]

Should work,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[Grounded]

This doesn't work....
I punched in:
Expr1: DateValue([dater])
Criteria: #4/18/2006#

And get "Data type mismatch in Criteria Expression"

iz

 

[PHV]

And this ?
Expr1: Int([dater])
Criteria: #2006-04-18#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Grounded]

Nope - this still returns zero records.
I thought this is something that should be common and easy.

iz

 

[PHV]

compact and repair ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

This is common and easy, there is just a piece missing somewhere. Without seeing the results it's hard to speculate. Try this:

Create a query with the fields [tt][dater][/tt], [tt]Int([dater])[/tt], and [tt]DateValue([dater])[/tt] and no criteria, what results show in the query?
Do each of the columns show up as Dates?
Numbers?
Strings?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[ClydeDoggie]

I believe DateValue is the function you need. For instance DateValue(#4/5/2006 12:01:00#)=#04/05/06# will return True. This takes formatting out of it.

 

[Grounded]

[dater] = 38825.9785763889
int[dater] = 38825
datevalue([dater]) = #Error
datevalue(format([dater], "mm/dd/yyyy") = 4/18/2006

Expression: datevalue(format([dater], "mm/dd/yyyy")
Criteria: #4/18/2006#
Returns two rows!!!

Finally - this looks like it. Just one more permutation.
Thanks for alll the help. If it doesn't work in the final application i'll be back..

iz