Date compare without time

  • Thread starter Thread starter Deborah Najm
  • Start date Start date
D

Deborah Najm

Hi,

I have a query that has a parameter value for the user to enter a date,
however the query never returns any records, because the field contains both
the date and the time stamp -- I am not sure how to format the input or the
compare, to get this right.

Thank you,
Deb

Query:
SELECT PO_Items.PO_Num, Phone.Date_In
FROM (Phone INNER JOIN Phone_Models ON
Phone.Phone_Model_ID=Phone_Models.Phone_Model_ID) INNER JOIN PO_Items ON
(Phone.PO_Item_Id=PO_Items.PO_Item_ID) AND
(Phone_Models.Phone_Model_ID=PO_Items.[Phone Model])
WHERE (((PO_Items.PO_Num)=[Enter the PO Number]) AND ((Phone.Date_In)=[Enter
the Date]));
 
SELECT PO_Items.PO_Num, Phone.Date_In
FROM (Phone INNER JOIN Phone_Models ON
Phone.Phone_Model_ID=Phone_Models.Phone_Model_ID) INNER JOIN PO_Items ON
(Phone.PO_Item_Id=PO_Items.PO_Item_ID) AND
(Phone_Models.Phone_Model_ID=PO_Items.[Phone Model])
WHERE (((PO_Items.PO_Num)=[Enter the PO Number]) AND
(DateValue(Phone.Date_In)=[Enter
the Date]));
 
Deborah Najm said:
Hi,

I have a query that has a parameter value for the user to enter a date,
however the query never returns any records, because the field contains both
the date and the time stamp -- I am not sure how to format the input or the
compare, to get this right.

Thank you,
Deb

Query:
SELECT PO_Items.PO_Num, Phone.Date_In
FROM (Phone INNER JOIN Phone_Models ON
Phone.Phone_Model_ID=Phone_Models.Phone_Model_ID) INNER JOIN PO_Items ON
(Phone.PO_Item_Id=PO_Items.PO_Item_ID) AND
(Phone_Models.Phone_Model_ID=PO_Items.[Phone Model])
WHERE (((PO_Items.PO_Num)=[Enter the PO Number]) AND ((Phone.Date_In)=[Enter
the Date]));
Click to expand...

Most efficient...

....Phone.Date_In BETWEEN [Enter the Date] AND DateAdd("d", 1, [Enter the
Date])

Make sure to explicitly set the parameter [Enter the Date] as a Date
DataType in the query. As long as the text within the [ ] is identical the
user will only be prompted once.

The above is more efficient because it can still take advantage of any
index that might exist on the Date_In field. A method that cannot utilize
an index, but is somewhat easier to construct is...

....DateValue(Phone.Date_In) = [Enter the Date]

DateValue() will strip the time portion off of the value, but any time you
apply criteria to an expression you lose the ability to use an index on the
base field.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



The above
 
When I use your suggestion, I get a syntax error. When I use DATEVALUE I
get a message saying that the syntex is too complicated to be evaluated....


Rick Brandt said:
Deborah Najm said:
Hi,

I have a query that has a parameter value for the user to enter a date,
however the query never returns any records, because the field contains both
the date and the time stamp -- I am not sure how to format the input or the
compare, to get this right.

Thank you,
Deb

Query:
SELECT PO_Items.PO_Num, Phone.Date_In
FROM (Phone INNER JOIN Phone_Models ON
Phone.Phone_Model_ID=Phone_Models.Phone_Model_ID) INNER JOIN PO_Items ON
(Phone.PO_Item_Id=PO_Items.PO_Item_ID) AND
(Phone_Models.Phone_Model_ID=PO_Items.[Phone Model])
WHERE (((PO_Items.PO_Num)=[Enter the PO Number]) AND ((Phone.Date_In)=[Enter
the Date]));
Click to expand...

Most efficient...

...Phone.Date_In BETWEEN [Enter the Date] AND DateAdd("d", 1, [Enter the
Date])

Make sure to explicitly set the parameter [Enter the Date] as a Date
DataType in the query. As long as the text within the [ ] is identical
the
user will only be prompted once.

The above is more efficient because it can still take advantage of any
index that might exist on the Date_In field. A method that cannot utilize
an index, but is somewhat easier to construct is...

...DateValue(Phone.Date_In) = [Enter the Date]

DateValue() will strip the time portion off of the value, but any time you
apply criteria to an expression you lose the ability to use an index on
the
base field.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



The above
Click to expand...
 
Deborah Najm said:
When I use your suggestion, I get a syntax error. When I use DATEVALUE I
get a message saying that the syntex is too complicated to be
Click to expand...
evaluated....

No way to tell what the problem is unless you post the entire SQL
statement.
 
Here is the ENTIRE query:

SELECT PO_Items.PO_Num, Phone.Date_In, Phone.ESN, Phone_Models.Manf,
Phone_Models.Model_Number, Phone.Refurb_Reason, Phone.Under_Warranty
FROM (Phone INNER JOIN Phone_Models ON
Phone.Phone_Model_ID=Phone_Models.Phone_Model_ID) INNER JOIN PO_Items ON
(Phone_Models.Phone_Model_ID=PO_Items.[Phone Model]) AND
(Phone.PO_Item_Id=PO_Items.PO_Item_ID)
WHERE (((PO_Items.PO_Num)=[Enter the PO Number]) AND (Phone.Date_In BETWEEN
[Enter the Date] AND DateAdd("d", 1, [Enter the
Date]));

Deborah Najm said:
When I use your suggestion, I get a syntax error. When I use DATEVALUE I
get a message saying that the syntex is too complicated to be
evaluated....


Rick Brandt said:
Deborah Najm said:
Hi,

I have a query that has a parameter value for the user to enter a date,
however the query never returns any records, because the field contains both
the date and the time stamp -- I am not sure how to format the input or the
compare, to get this right.

Thank you,
Deb

Query:
SELECT PO_Items.PO_Num, Phone.Date_In
FROM (Phone INNER JOIN Phone_Models ON
Phone.Phone_Model_ID=Phone_Models.Phone_Model_ID) INNER JOIN PO_Items ON
(Phone.PO_Item_Id=PO_Items.PO_Item_ID) AND
(Phone_Models.Phone_Model_ID=PO_Items.[Phone Model])
WHERE (((PO_Items.PO_Num)=[Enter the PO Number]) AND ((Phone.Date_In)=[Enter
the Date]));
Click to expand...

Most efficient...

...Phone.Date_In BETWEEN [Enter the Date] AND DateAdd("d", 1, [Enter the
Date])

Make sure to explicitly set the parameter [Enter the Date] as a Date
DataType in the query. As long as the text within the [ ] is identical
the
user will only be prompted once.

The above is more efficient because it can still take advantage of any
index that might exist on the Date_In field. A method that cannot
utilize
an index, but is somewhat easier to construct is...

...DateValue(Phone.Date_In) = [Enter the Date]

DateValue() will strip the time portion off of the value, but any time
you
apply criteria to an expression you lose the ability to use an index on
the
base field.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



The above
Click to expand...
Click to expand...
 
NM -- I got it -- Thank you!!

Deb

Rick Brandt said:
Deborah Najm said:
Hi,

I have a query that has a parameter value for the user to enter a date,
however the query never returns any records, because the field contains both
the date and the time stamp -- I am not sure how to format the input or the
compare, to get this right.

Thank you,
Deb

Query:
SELECT PO_Items.PO_Num, Phone.Date_In
FROM (Phone INNER JOIN Phone_Models ON
Phone.Phone_Model_ID=Phone_Models.Phone_Model_ID) INNER JOIN PO_Items ON
(Phone.PO_Item_Id=PO_Items.PO_Item_ID) AND
(Phone_Models.Phone_Model_ID=PO_Items.[Phone Model])
WHERE (((PO_Items.PO_Num)=[Enter the PO Number]) AND ((Phone.Date_In)=[Enter
the Date]));
Click to expand...

Most efficient...

...Phone.Date_In BETWEEN [Enter the Date] AND DateAdd("d", 1, [Enter the
Date])

Make sure to explicitly set the parameter [Enter the Date] as a Date
DataType in the query. As long as the text within the [ ] is identical
the
user will only be prompted once.

The above is more efficient because it can still take advantage of any
index that might exist on the Date_In field. A method that cannot utilize
an index, but is somewhat easier to construct is...

...DateValue(Phone.Date_In) = [Enter the Date]

DateValue() will strip the time portion off of the value, but any time you
apply criteria to an expression you lose the ability to use an index on
the
base field.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



The above
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Comparing records by year in a report 2
Right format of dates for query criteria 1
Date/Time Filter problem 5
comparing dates 1
User input date to return query after date 7
Entering Report Parameters through a Form 2
Query Criteria 8
Start Date/End Date require multiple entries. 1

Back
Top