Query improvement

[jbair via AccessMonster.com]

I have the following query that returns what was completed yesterday, but of
course on mondays it shows nothing as it only looks for yesterday. I would
like to show what was completed on friday, or sometimes saturday when there
is production any ideas?
Jerry

SELECT Sum(InspectionLog.Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE (((InspectionLog.Test3Date)>=DateAdd("d",-1,Date()) And (InspectionLog.
Test3Date)<Date()));

 

[Dale Fye]

Assuming that Test3Date is a date, and does not include time data, the
following should give you the Sum of the [Quantity] field for the most recent
day where data exists, prior to today.

SELECT Sum(InspectionLog.Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE InspectionLog.Test3Date= (SELECT MAX(Test3Date)
FROM InspectionLog
WHERE Test3Date < Date())

If, however, Test3Date contains date and time information, then I would try:

SELECT Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE DateValue(Test3Date)= (SELECT MAX(DateValue(Test3Date))
FROM InspectionLog
WHERE Test3Date < Date())

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[jbair via AccessMonster.com]

Thanks Dale you are on the right track and Test3Date is date and time but I
am getting a data type mismatch in the criteria.
Jerry

Assuming that Test3Date is a date, and does not include time data, the
following should give you the Sum of the [Quantity] field for the most recent
day where data exists, prior to today.

SELECT Sum(InspectionLog.Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE InspectionLog.Test3Date= (SELECT MAX(Test3Date)
FROM InspectionLog
WHERE Test3Date < Date())

If, however, Test3Date contains date and time information, then I would try:

SELECT Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE DateValue(Test3Date)= (SELECT MAX(DateValue(Test3Date))
FROM InspectionLog
WHERE Test3Date < Date())

I have the following query that returns what was completed yesterday, but of
course on mondays it shows nothing as it only looks for yesterday. I would

[quoted text clipped - 6 lines]

WHERE (((InspectionLog.Test3Date)>=DateAdd("d",-1,Date()) And (InspectionLog.
Test3Date)<Date()));

 

[Dale Fye]

what happens when your run this query? Does this work properly?

SELECT MAX(DateValue(Test3Date)) as MaxDate
FROM InspectionLog
WHERE Test3Date < Date()

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Thanks Dale you are on the right track and Test3Date is date and time but I
am getting a data type mismatch in the criteria.
Jerry

Assuming that Test3Date is a date, and does not include time data, the
following should give you the Sum of the [Quantity] field for the most recent
day where data exists, prior to today.

SELECT Sum(InspectionLog.Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE InspectionLog.Test3Date= (SELECT MAX(Test3Date)
FROM InspectionLog
WHERE Test3Date < Date())

If, however, Test3Date contains date and time information, then I would try:

SELECT Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE DateValue(Test3Date)= (SELECT MAX(DateValue(Test3Date))
FROM InspectionLog
WHERE Test3Date < Date())

I have the following query that returns what was completed yesterday, but of
course on mondays it shows nothing as it only looks for yesterday. I would

[quoted text clipped - 6 lines]

WHERE (((InspectionLog.Test3Date)>=DateAdd("d",-1,Date()) And (InspectionLog.
Test3Date)<Date()));

 

[jbair via AccessMonster.com]

I get my Saturday date, works great ...now how do I add the sum of my
quantities part.
Thanks so much

what happens when your run this query? Does this work properly?

SELECT MAX(DateValue(Test3Date)) as MaxDate
FROM InspectionLog
WHERE Test3Date < Date()

Thanks Dale you are on the right track and Test3Date is date and time but I
am getting a data type mismatch in the criteria.

[quoted text clipped - 23 lines]

 

[Dale Fye]

I'm not sure why you are getting a problem with the SQL string I originally
sent (the 2nd one). I duplicated your table (at least the Test3Date and
Quantity fields), and the query worked just fine. Try it again:

SELECT Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE DateValue([Test3Date])=(SELECT MAX(DateValue(Test3Date)) FROM
InspectionLog WHERE Test3Date < Date());

Dale

I get my Saturday date, works great ...now how do I add the sum of my
quantities part.
Thanks so much

what happens when your run this query? Does this work properly?

SELECT MAX(DateValue(Test3Date)) as MaxDate
FROM InspectionLog
WHERE Test3Date < Date()

Thanks Dale you are on the right track and Test3Date is date and time
but I
am getting a data type mismatch in the criteria.

[quoted text clipped - 23 lines]

WHERE (((InspectionLog.Test3Date)>=DateAdd("d",-1,Date()) And
(InspectionLog.
Test3Date)<Date()));

 

[jbair via AccessMonster.com]

Same thing I get a data type mismatch in the criteria expression. I have a
large table two of the fields are Quantity, which is just the quantity of an
order, and Test3Date is just the date and time the orders were tested ( maybe
40 to 50 orders each day). I just pasted your string in a blank sql screen. I
am using access 2002.

I'm not sure why you are getting a problem with the SQL string I originally
sent (the 2nd one). I duplicated your table (at least the Test3Date and
Quantity fields), and the query worked just fine. Try it again:

SELECT Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE DateValue([Test3Date])=(SELECT MAX(DateValue(Test3Date)) FROM
InspectionLog WHERE Test3Date < Date());

Dale

I get my Saturday date, works great ...now how do I add the sum of my
quantities part.

[quoted text clipped - 13 lines]

 

[Dale Fye]

OK, try:

SELECT DateValue([Test3Date]), Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
GROUP BY DateValue([Test3Date])

Did that work?

If so, try:

SELECT TOP 1 DateValue([Test3Date]), Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
GROUP BY DateValue([Test3Date])
ORDER BY DateValue([Test3Date]) DESC

This should actually get you todays data, but I'm just testing at this
point. If that works, try:

SELECT TOP 1 DateValue([Test3Date]), Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE [Test3Date] < Date()
GROUP BY DateValue([Test3Date])
ORDER BY DateValue([Test3Date]) DESC

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Same thing I get a data type mismatch in the criteria expression. I have a
large table two of the fields are Quantity, which is just the quantity of an
order, and Test3Date is just the date and time the orders were tested ( maybe
40 to 50 orders each day). I just pasted your string in a blank sql screen. I
am using access 2002.

I'm not sure why you are getting a problem with the SQL string I originally
sent (the 2nd one). I duplicated your table (at least the Test3Date and
Quantity fields), and the query worked just fine. Try it again:

SELECT Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE DateValue([Test3Date])=(SELECT MAX(DateValue(Test3Date)) FROM
InspectionLog WHERE Test3Date < Date());

Dale

I get my Saturday date, works great ...now how do I add the sum of my
quantities part.

[quoted text clipped - 13 lines]

(InspectionLog.
Test3Date)<Date()));

 

[jbair via AccessMonster.com]

Same thing data mismatch on the first query

OK, try:

SELECT DateValue([Test3Date]), Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
GROUP BY DateValue([Test3Date])

Did that work?

If so, try:

SELECT TOP 1 DateValue([Test3Date]), Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
GROUP BY DateValue([Test3Date])
ORDER BY DateValue([Test3Date]) DESC

This should actually get you todays data, but I'm just testing at this
point. If that works, try:

SELECT TOP 1 DateValue([Test3Date]), Sum(Quantity) AS SumOfQuantity
FROM InspectionLog
WHERE [Test3Date] < Date()
GROUP BY DateValue([Test3Date])
ORDER BY DateValue([Test3Date]) DESC

Same thing I get a data type mismatch in the criteria expression. I have a
large table two of the fields are Quantity, which is just the quantity of an

[quoted text clipped - 18 lines]

 

[jbair via AccessMonster.com]

If i go to my design view and back to my sql view here is what is returned

SELECT DateValue([Test3Date]) AS Expr1, Sum(InspectionLog.Quantity) AS
SumOfQuantity
FROM InspectionLog
GROUP BY DateValue([Test3Date]);

Same thing data mismatch on the first query

OK, try:

[quoted text clipped - 25 lines]

 

[Dale Fye]

Check to see whether any of the [Test3Date] values are NULL. If so, insert a
WHERE clause in your query that filters out those values.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

If i go to my design view and back to my sql view here is what is returned

SELECT DateValue([Test3Date]) AS Expr1, Sum(InspectionLog.Quantity) AS
SumOfQuantity
FROM InspectionLog
GROUP BY DateValue([Test3Date]);

Same thing data mismatch on the first query

OK, try:

[quoted text clipped - 25 lines]

(InspectionLog.
Test3Date)<Date()));

 

[John Spencer]

If Test3Date contains a value that cannot be interpreted as a date you will
get a mismatch error.

Try the following and see if you get any nulls returned. If you do, then you
need to check for empty strings, nulls, and invalid date strings.

SELECT IIF(IsDate(Test3Date),DateValue(Test3Date),Null) as ADate
, Sum(Quantity) as SumQty
FROM InspectionLog
GROUP BY IIF(IsDate(Test3Date),DateValue(Test3Date),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

If i go to my design view and back to my sql view here is what is returned

SELECT DateValue([Test3Date]) AS Expr1, Sum(InspectionLog.Quantity) AS
SumOfQuantity
FROM InspectionLog
GROUP BY DateValue([Test3Date]);

Same thing data mismatch on the first query

OK, try:

[quoted text clipped - 25 lines]

(InspectionLog.
Test3Date)<Date()));

 

[jbair via AccessMonster.com]

That did the trick, some values will be null until the test is complete!
thanks very much.
JBair

Check to see whether any of the [Test3Date] values are NULL. If so, insert a
WHERE clause in your query that filters out those values.

If i go to my design view and back to my sql view here is what is returned

[quoted text clipped - 10 lines]