Date compare without time

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]));
 
D

Douglas J. Steele

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]));
 
R

Rick Brandt

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
 
D

Deborah Najm

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...
 
R

Rick Brandt

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.
 
D

Deborah Najm

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...
 
D

Deborah Najm

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...
 
Top