Query Criteria = TODAY

  • Thread starter Thread starter MrMike
  • Start date Start date
M

MrMike

I'm working with the query below. The part of the query
not working is the last part that selects DateOpened =
Date. This should select records with today's date in the
DateOpened field. How can I fix this? Thanks.

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Date()))
 
Did you store both a date and time in the DateOpened field? In other words,
was that field filled by the use of the function Now instead of the function
Date?

If you used Now, then you won't get an exact match. Change the criteria
expression to get the range for that day:

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Between Date() And Date() + 1))
 
It looks correct. Are you saying that you are getting records with Status=6
and the date not equal to today?

I'd expect you to get status=5 records not equal to today because of the
"or" statement in there.

Rick B
 
Hi Mike,

Let me just make sure I understand your criteria. You want the query to
run if ALL of these conditions are true:

([quantity]-[qtyshipped]) is greater than 0
AND
qryOrder.StatusID is equal to 5 OR to 6
AND
qryOrder.OrderID is equal to tblOrderDetail.OrderID
AND
qryOpened.DateOpened equals today's date

Is this correct? If so, try

WHERE ((([quantity]-[qtyshipped])>0) AND
([qryOrder].[StatusID] IN(5,6)) AND
([qryOrder].[OrderID]=[tblOrderDetail].[OrderID]) AND
([qryOpened].[DateOpened]=Date()))

If qryOrder is joined in this query to tblOrderdetail on the OrderID
field, then the 3rd AND isn't strictly necessary.

hth,
 
Thanks for your response. I get Neither records with
status=5 nor status=6. I get no records at all. If I
take out the DateOpened clause everything works fine.

-----Original Message-----
It looks correct. Are you saying that you are getting records with Status=6
and the date not equal to today?

I'd expect you to get status=5 records not equal to today because of the
"or" statement in there.

Rick B


I'm working with the query below. The part of the query
not working is the last part that selects DateOpened =
Date. This should select records with today's date in the
DateOpened field. How can I fix this? Thanks.

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Date()))
Click to expand...


.
Click to expand...
 
Ken, Thanks for the reply. The NOW format is not being
used, this field strictly stores dates.

-----Original Message-----
Did you store both a date and time in the DateOpened field? In other words,
was that field filled by the use of the function Now instead of the function
Date?

If you used Now, then you won't get an exact match. Change the criteria
expression to get the range for that day:

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Between Date() And Date() + 1))


--

Ken Snell
<MS ACCESS MVP>


I'm working with the query below. The part of the query
not working is the last part that selects DateOpened =
Date. This should select records with today's date in the
DateOpened field. How can I fix this? Thanks.

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Date()))
Click to expand...


.
Click to expand...
 
That is correct. Maybe I'm missing something else,
because I've tried your syntax as well as the other
syntax examples posted and I still get nothing.

Thanks for your help
-----Original Message-----
Hi Mike,

Let me just make sure I understand your criteria. You want the query to
run if ALL of these conditions are true:

([quantity]-[qtyshipped]) is greater than 0
AND
qryOrder.StatusID is equal to 5 OR to 6
AND
qryOrder.OrderID is equal to tblOrderDetail.OrderID
AND
qryOpened.DateOpened equals today's date

Is this correct? If so, try

WHERE ((([quantity]-[qtyshipped])>0) AND
([qryOrder].[StatusID] IN(5,6)) AND
([qryOrder].[OrderID]=[tblOrderDetail].[OrderID]) AND
([qryOpened].[DateOpened]=Date()))

If qryOrder is joined in this query to tblOrderdetail on the OrderID
field, then the 3rd AND isn't strictly necessary.

hth,

LeAnne said:
I'm working with the query below. The part of the query
not working is the last part that selects DateOpened =
Date. This should select records with today's date in the
DateOpened field. How can I fix this? Thanks.

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Date()))
Click to expand...
.
Click to expand...
 
I found the problem. As it turns out I was looking at
data in the TABLE used as the record source for the
qryOrder query, and not the data in qryOrder. Thanks for
your help!
-----Original Message-----
Thanks for your response. I get Neither records with
status=5 nor status=6. I get no records at all. If I
take out the DateOpened clause everything works fine.

-----Original Message-----
It looks correct. Are you saying that you are getting records with Status=6
and the date not equal to today?

I'd expect you to get status=5 records not equal to today because of the
"or" statement in there.

Rick B


I'm working with the query below. The part of the query
not working is the last part that selects DateOpened =
Date. This should select records with today's date in the
DateOpened field. How can I fix this? Thanks.

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Date()))
Click to expand...


.
Click to expand...
.
Click to expand...
 
I found the problem. As it turns out I was looking at
data in the TABLE used as the record source for the
qryOrder query, and not the data in qryOrder. Thanks for
your help!
-----Original Message-----
Hi Mike,

Let me just make sure I understand your criteria. You want the query to
run if ALL of these conditions are true:

([quantity]-[qtyshipped]) is greater than 0
AND
qryOrder.StatusID is equal to 5 OR to 6
AND
qryOrder.OrderID is equal to tblOrderDetail.OrderID
AND
qryOpened.DateOpened equals today's date

Is this correct? If so, try

WHERE ((([quantity]-[qtyshipped])>0) AND
([qryOrder].[StatusID] IN(5,6)) AND
([qryOrder].[OrderID]=[tblOrderDetail].[OrderID]) AND
([qryOpened].[DateOpened]=Date()))

If qryOrder is joined in this query to tblOrderdetail on the OrderID
field, then the 3rd AND isn't strictly necessary.

hth,

LeAnne said:
I'm working with the query below. The part of the query
not working is the last part that selects DateOpened =
Date. This should select records with today's date in the
DateOpened field. How can I fix this? Thanks.

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Date()))
Click to expand...
.
Click to expand...
 
I found the problem. As it turns out I was looking at
data in the TABLE used as the record source for the
qryOrder query, and not the data in qryOrder. Thanks for
your help!
-----Original Message-----
Ken, Thanks for the reply. The NOW format is not being
used, this field strictly stores dates.

-----Original Message-----
Did you store both a date and time in the DateOpened field? In other words,
was that field filled by the use of the function Now instead of the function
Date?

If you used Now, then you won't get an exact match. Change the criteria
expression to get the range for that day:

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Between Date() And Date() + 1))


--

Ken Snell
<MS ACCESS MVP>


I'm working with the query below. The part of the query
not working is the last part that selects DateOpened =
Date. This should select records with today's date in the
DateOpened field. How can I fix this? Thanks.

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Date()))
Click to expand...


.
Click to expand...
.
Click to expand...
 
Mike,

Post the SQL of the *entire* query and I'll take another look.

PS. You're sure that qryOpened is "in" the query? And that qryOpened
actually contains records where DateOpened = Today? Never hurts to check
the obvious...

LeAnne
That is correct. Maybe I'm missing something else,
because I've tried your syntax as well as the other
syntax examples posted and I still get nothing.

Thanks for your help
-----Original Message-----
Hi Mike,

Let me just make sure I understand your criteria. You want the query to
run if ALL of these conditions are true:

([quantity]-[qtyshipped]) is greater than 0
AND
qryOrder.StatusID is equal to 5 OR to 6
AND
qryOrder.OrderID is equal to tblOrderDetail.OrderID
AND
qryOpened.DateOpened equals today's date

Is this correct? If so, try

WHERE ((([quantity]-[qtyshipped])>0) AND
([qryOrder].[StatusID] IN(5,6)) AND
([qryOrder].[OrderID]=[tblOrderDetail].[OrderID]) AND
([qryOpened].[DateOpened]=Date()))

If qryOrder is joined in this query to tblOrderdetail on the OrderID
field, then the 3rd AND isn't strictly necessary.

hth,

LeAnne said:
I'm working with the query below. The part of the query
not working is the last part that selects DateOpened =
Date. This should select records with today's date in the
DateOpened field. How can I fix this? Thanks.

WHERE ((([quantity]-[qtyshipped])>0) AND
((qryOrder.StatusID)=5 Or (qryOrder.StatusID)=6) AND
((qryOrder.OrderID)=[tblorderdetail].[OrderId]) AND
((qryOrder.DateOpened)=Date()))
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top