Query DateTime Field

[Tausif]

Hi,

I have a field DateStamp, stores both date and time. I would like put a
query with which I can only compare date but not the time. I put this query
but it is not working. Please help thankx..

SELECT transactions.ChangeRequest FROM transactions WHERE 1=1 AND
transactions.[Date Request] = #5/31/2004# ORDER BY transactions.Status

Thankx

 

[Ken Snell]

When you store both the time and date together in one field, you must use a
calculated field that gives you just the date portion for searching:

SELECT transactions.ChangeRequest FROM transactions
WHERE 1=1 AND
DateValue(transactions.[Date Request]) = #5/31/2004#
ORDER BY transactions.Status;

 

[Tausif]

Hi,

THankx a lot for the help.

I used that but it gave me the error:

Data type mismatch in criteria expression.
/helpdesk/HelpDesk/HelpDesk.asp, line 400
I am using HTML as front end and ASP for database access.. thankx in
advance..

Tausif
----- Original Message -----
From: "Ken Snell" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Monday, May 31, 2004 12:03 PM
Subject: Re: Query DateTime Field

When you store both the time and date together in one field, you must use a
calculated field that gives you just the date portion for searching:

SELECT transactions.ChangeRequest FROM transactions
WHERE 1=1 AND
DateValue(transactions.[Date Request]) = #5/31/2004#
ORDER BY transactions.Status;

--
Ken Snell
<MS ACCESS MVP>

Hi,

I have a field DateStamp, stores both date and time. I would like put a
query with which I can only compare date but not the time. I put this query
but it is not working. Please help thankx..

SELECT transactions.ChangeRequest FROM transactions WHERE 1=1 AND
transactions.[Date Request] = #5/31/2004# ORDER BY transactions.Status

Thankx

 

[Tausif]

I checked it is due to some records having NULL in the datestamp field .
Now, how can i use datevalue function.

regards,

Tausif.

 

[Tausif]

I checked it is due to some records having NULL in the datestamp field .
Now, In this case; how can i use datevalue function.

regards,

Tausif.

 

[Tausif]

I checked it is due to some records having NULL in the datestamp field .
Now, In this case; how can i use datevalue function.

regards,

Tausif.

 

[Tausif]

I checked it is due to some records having NULL in the datestamp field .
Now, In this case; how can i use datevalue function.

regards,

Tausif.

 

[John Vinson]

SELECT transactions.ChangeRequest FROM transactions WHERE 1=1 AND
transactions.[Date Request] = #5/31/2004# ORDER BY transactions.Status

Another approach, which is tolerant to NULLs and takes advantage of
any index on [Date Request], would be

WHERE True AND [Transactions].[Date Request] >= [Enter date:] AND
[Date Request] < [Enter date:] + 1