Unix Date Query Criteria 1

[mikeyb540]

Hello,

Having a hard time with Unix Time in a Query. This is what I got
Field is
Date: (Format((DateAdd("s",Trim([DATECREAT]),#1/1/1970#)),"m/d/yyyy"))

Then in the criteria I have a between statement as follows:
between #9/14/2005# And #9/15/2005#

My results return:
9/14/2003
9/14/2004
9/14/2005
9/15/2003
9/15/2004
9/15/2005

Why am I getting 2003 and 2004 data?

Thanks in advance.

 

[PHV]

Date: DateAdd('s', Trim([DATECREAT]), #1970-01-01#)

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

 

[mikeyb540]

Thanks PHV.

If I leave it with # it reverts back to the old format 1/1/1970. Tried "1970-1-1" but i still get 2003 and 2004 data.

What else can I try?

Thanks in advance

 

[PHV]

Did you remove the Format function ?
The goal is to have the expression returning a DateTime field (instead of a Text field).

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

 

[mikeyb540]

that did it. it was the format.

Thanks

 

[mikeyb540]

hmm... without the format function now I see the Time and wont the grouping right

Any ideas on how to group per date?

 

[PHV]

Date: Int(DateAdd('s', Trim([DATECREAT]), #1970-01-01#))

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

 

[mikeyb540]

Thanks Again.... works like a charm