Query DateTime Field

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

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



"Tausif" wrote in message
...
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

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"
Newsgroups: microsoft.public.access.queries
Sent: Monday, May 31, 2004 12:03 PM
Subject: 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



"Tausif" wrote in message
...
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

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

regards,

Tausif.

"Tausif" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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

On Mon, 31 May 2004 11:33:20 +0800, "Tausif"
wrote:

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

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public