CLASSIC DATE/TIME PROBLEM

[sohunter]

But I forget how to solve it?

I want a user to be able to pull by date (without figuring in time) and the date field they're querying looks like this:

6/4/04 8:31:49 PM

The parameter looks like this:

>=[Enter start date:] And <=[Enter end date:]

Even though there's plenty of records from June 4, nothing is returned because there's nothing less than or = 6/4/04, because of the time. (If I was less fatigued, I may have worded this better!)

thank you

 

[LittleSmudge]

How about

BETWEEN [Enter start date:] AND ([Enter end date:] + 0.9999)

Or simply, If the date field data will never be midnight then

BETWEEN [Enter start date:] AND ([Enter end date:] + 1)





'ope-that-'elps

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Crowley16]

or you could just put

select format([date], "dd mm yy") from ...

 

[LittleSmudge]

If Crowley16's approach works, you'll find

select Int([Date]) from ..

will run quicker and provide the same results.



;-)

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Crowley16]

really?

so the timestamp is like:
[dateserial].[timeserial]

hmm, interesting...

 

[LittleSmudge]

Yes,

All date times in Access are stored as
DatePart is Integer
TimePart is Fraction

Integer + Fraction = DateTime

The 6/4/04 8:31:49 PM format is JUST a formatting issue. - It doesn't affect the way the data is stored - or the way it can be manipulated.


So if you take Int(DateTime) you get Date





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[BobJacksonNCI]

Is that Classic or C2?

Or...

DateValue([tableDate]) Between ...

I don't know whether it's faster or slower, but when choices exist I lean toward the most easily understood.



HTH,
Bob

 

[MichaelRed]

HMMMMMMMMMMMMMMMMMMMMMMMMMM ... mmmmmmmmmmmmmmmmm

try

CLng([Statr Date])

MichaelRed
mlred@verizon.net

 

[sohunter]

Thank you all. Very educational!

It was necessary to retain the date/time data type (because it had to be declared in the crosstab's parameters) and Int worked fine.