Error when Date/Time is a criteria

K

KenNiuM

Hello.

When i have this query, there is an error.
(You tried to execute a query that does not include the specified expression
SalesTransaction.[stDate/Time])>=Query1.lDate_TImeOfMax as part of an
aggregate function.)

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount, SalesTransaction.[stDate/Time]
FROM SalesTransaction, Query1
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount, SalesTransaction.[stDate/Time]
HAVING (((SalesTransaction.[stDate/Time])>=#Query1.lDate_TImeOfMax#));



This is my SQL for Query1:
SELECT CashierLoginDetails.lStaffID, Max(CashierLoginDetails.[lDate/TIme])
AS lDate_TImeOfMax, Last(CashierLoginDetails.lCashRegister) AS
lCashRegisterOfLast, SalesTransaction.stPaymentMode
FROM CashierLoginDetails, SalesTransaction
GROUP BY CashierLoginDetails.lStaffID, SalesTransaction.stPaymentMode;


I want to have my query show transactions after the log in time. How do i do
it?
Both my table date/time fields data type are set to Date/Time and default
value NOW().
 
A

Allen Browne

Suggestions:

1. Omit the # from around the field.
You use the # around literal date/time values, not field names.

2. Uncheck the Show box under Query1.lDate_TImeOfMax in query design. (This
gives you a WHERE clause instead of a HAVING clause.)

I'm not really sure this will give you what you want, but it should deal
with the immediate error.
 
K

KenNiuM

I did as advised. Below is my SQL:

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount
FROM SalesTransaction, Query1
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount, SalesTransaction.[stDate/Time]
HAVING (((SalesTransaction.[stDate/Time])>="[Query1].[lDate_TImeOfMax]"));


However i am having a different error now, which shows data type mismatch in
criteria expression.

Please advise.


Allen Browne said:
Suggestions:

1. Omit the # from around the field.
You use the # around literal date/time values, not field names.

2. Uncheck the Show box under Query1.lDate_TImeOfMax in query design. (This
gives you a WHERE clause instead of a HAVING clause.)

I'm not really sure this will give you what you want, but it should deal
with the immediate error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

KenNiuM said:
Hello.

When i have this query, there is an error.
(You tried to execute a query that does not include the specified
expression
SalesTransaction.[stDate/Time])>=Query1.lDate_TImeOfMax as part of an
aggregate function.)

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount, SalesTransaction.[stDate/Time]
FROM SalesTransaction, Query1
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount, SalesTransaction.[stDate/Time]
HAVING (((SalesTransaction.[stDate/Time])>=#Query1.lDate_TImeOfMax#));



This is my SQL for Query1:
SELECT CashierLoginDetails.lStaffID, Max(CashierLoginDetails.[lDate/TIme])
AS lDate_TImeOfMax, Last(CashierLoginDetails.lCashRegister) AS
lCashRegisterOfLast, SalesTransaction.stPaymentMode
FROM CashierLoginDetails, SalesTransaction
GROUP BY CashierLoginDetails.lStaffID, SalesTransaction.stPaymentMode;


I want to have my query show transactions after the log in time. How do i
do
it?
Both my table date/time fields data type are set to Date/Time and default
value NOW().
Click to expand...
Click to expand...
 
J

John W. Vinson

However i am having a different error now, which shows data type mismatch in
criteria expression.
Click to expand...

That's because you're comparing the date/time value to a text string, the name
of the query field!

Change the SQL to

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount
FROM SalesTransaction, Query1
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount, SalesTransaction.[stDate/Time]
WHERE (((SalesTransaction.[stDate/Time])>=[Query1].[lDate_TImeOfMax]));

or do the comparison in the JOIN clause instead:

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount
FROM SalesTransaction INNER JOIN Query1
ON (((SalesTransaction.[stDate/Time])>=[Query1].[lDate_TImeOfMax]));
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount, SalesTransaction.[stDate/Time];

It's not clear why you're using a TOTALS query though - you're not summing or
counting anything. Perhaps you mean to sum the total amount grouped by payment
mode and staff member? If so

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction INNER JOIN Query1
ON (((SalesTransaction.[stDate/Time])>=[Query1].[lDate_TImeOfMax]));
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
[Query1].[lDate_TImeOfMax];
 
K

KenNiuM

Perhaps you mean to sum the total amount grouped by payment
mode and staff member? If so

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction INNER JOIN Query1
ON (((SalesTransaction.[stDate/Time])>=[Query1].[lDate_TImeOfMax]));
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
[Query1].[lDate_TImeOfMax];


YES! I am tying to do the above. When i tried to do the above there is
another error. (the LEVEL clause includes a reserved word or argument that is
misspelled or missing, or the punctuation is incorrect.)

Please help.


John W. Vinson said:
However i am having a different error now, which shows data type mismatch in
criteria expression.
Click to expand...

That's because you're comparing the date/time value to a text string, the name
of the query field!

Change the SQL to

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount
FROM SalesTransaction, Query1
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount, SalesTransaction.[stDate/Time]
WHERE (((SalesTransaction.[stDate/Time])>=[Query1].[lDate_TImeOfMax]));

or do the comparison in the JOIN clause instead:

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount
FROM SalesTransaction INNER JOIN Query1
ON (((SalesTransaction.[stDate/Time])>=[Query1].[lDate_TImeOfMax]));
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
SalesTransaction.stTotalAmount, SalesTransaction.[stDate/Time];

It's not clear why you're using a TOTALS query though - you're not summing or
counting anything. Perhaps you mean to sum the total amount grouped by payment
mode and staff member? If so

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction INNER JOIN Query1
ON (((SalesTransaction.[stDate/Time])>=[Query1].[lDate_TImeOfMax]));
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
[Query1].[lDate_TImeOfMax];
Click to expand...
 
J

John W. Vinson

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction INNER JOIN Query1
ON (((SalesTransaction.[stDate/Time])>=[Query1].[lDate_TImeOfMax]));
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
[Query1].[lDate_TImeOfMax];


YES! I am tying to do the above. When i tried to do the above there is
another error. (the LEVEL clause includes a reserved word or argument that is
misspelled or missing, or the punctuation is incorrect.)
Click to expand...

Remove the first semicolon. Sorry for the typo. Should be

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction INNER JOIN Query1
ON SalesTransaction.[stDate/Time]>=[Query1].[lDate_TImeOfMax]
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
[Query1].[lDate_TImeOfMax];

Not knowing what's in SalesTransaction or Query1 I'm not certain this gives
you what you *want* but it's at least a valid query.
 
K

KenNiuM

hmmm... the sql seems right and is working perfectly fine..
but the results are not what i wanted..


What i wanted was the sum of the total amount made after
[Query1].[lDate_TImeOfMax] grouped by payment mode and staff ID.


I know we are getting close to what i want, hope you could help.



John W. Vinson said:
SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction INNER JOIN Query1
ON (((SalesTransaction.[stDate/Time])>=[Query1].[lDate_TImeOfMax]));
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
[Query1].[lDate_TImeOfMax];


YES! I am tying to do the above. When i tried to do the above there is
another error. (the LEVEL clause includes a reserved word or argument that is
misspelled or missing, or the punctuation is incorrect.)
Click to expand...

Remove the first semicolon. Sorry for the typo. Should be

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction INNER JOIN Query1
ON SalesTransaction.[stDate/Time]>=[Query1].[lDate_TImeOfMax]
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
[Query1].[lDate_TImeOfMax];

Not knowing what's in SalesTransaction or Query1 I'm not certain this gives
you what you *want* but it's at least a valid query.
Click to expand...
 
J

John W. Vinson

hmmm... the sql seems right and is working perfectly fine..
but the results are not what i wanted..


What i wanted was the sum of the total amount made after
[Query1].[lDate_TImeOfMax] grouped by payment mode and staff ID.


I know we are getting close to what i want, hope you could help.
Click to expand...


You've described it in words. Just put it into the SQL.

SQL is a very logical language - a query consists of clauses; a SELECT clause
describing which fields to include; a FROM clause indicating which table or
tables to select from; in this case a GROUP BY clause indicating which fields
to group by; a WHERE clause indicating what criteria to apply.

ASSUMING - and you haven't said, so I don't know if it's correct - that Query1
returns only one record, go back to the Cartesian join and try

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction, Query1
WHERE SalesTransaction.[stDate/Time]>=[Query1].[lDate_TImeOfMax]
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID;

If my assumption about Query1 is wrong, please post its SQL and indicate what
it's intended to provide.
 
K

KenNiuM

This is my SQL for query 1.
The purpose of me doing this SQL is to group the CashierID, Last Login time,
last CashRegister and the different payment mode.

I managed to get what i wanted from this query.

SELECT CashierLoginDetails.lStaffID, Max(CashierLoginDetails.[lDate/TIme])
AS lDate_TImeOfMax, Last(CashierLoginDetails.lCashRegister) AS
lCashRegisterOfLast, SalesTransaction.stPaymentMode
FROM CashierLoginDetails, SalesTransaction
GROUP BY CashierLoginDetails.lStaffID, SalesTransaction.stPaymentMode;


However i am still not able to get the the 2nd query to work properly. The
sum is not right.


Hope that you would help me.

John W. Vinson said:
hmmm... the sql seems right and is working perfectly fine..
but the results are not what i wanted..


What i wanted was the sum of the total amount made after
[Query1].[lDate_TImeOfMax] grouped by payment mode and staff ID.


I know we are getting close to what i want, hope you could help.
Click to expand...


You've described it in words. Just put it into the SQL.

SQL is a very logical language - a query consists of clauses; a SELECT clause
describing which fields to include; a FROM clause indicating which table or
tables to select from; in this case a GROUP BY clause indicating which fields
to group by; a WHERE clause indicating what criteria to apply.

ASSUMING - and you haven't said, so I don't know if it's correct - that Query1
returns only one record, go back to the Cartesian join and try

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction, Query1
WHERE SalesTransaction.[stDate/Time]>=[Query1].[lDate_TImeOfMax]
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID;

If my assumption about Query1 is wrong, please post its SQL and indicate what
it's intended to provide.
Click to expand...
 
K

KenNiuM

I found out the current problem. Here is my current SQL.

SELECT Query1.lStaffID, Query1.stPaymentMode,
Sum(SalesTransaction.stTotalAmount) AS Totalamount
FROM SalesTransaction, Query1
WHERE (((SalesTransaction.stDate)>=[Query1].[lDate_TImeOfMax]))
GROUP BY Query1.lStaffID, Query1.stPaymentMode;

Currently the sum is grouped by staffID and not paymentMode.
How do i group by both staffID and PaymentMode?

Hope you would help me!

KenNiuM said:
This is my SQL for query 1.
The purpose of me doing this SQL is to group the CashierID, Last Login time,
last CashRegister and the different payment mode.

I managed to get what i wanted from this query.

SELECT CashierLoginDetails.lStaffID, Max(CashierLoginDetails.[lDate/TIme])
AS lDate_TImeOfMax, Last(CashierLoginDetails.lCashRegister) AS
lCashRegisterOfLast, SalesTransaction.stPaymentMode
FROM CashierLoginDetails, SalesTransaction
GROUP BY CashierLoginDetails.lStaffID, SalesTransaction.stPaymentMode;


However i am still not able to get the the 2nd query to work properly. The
sum is not right.


Hope that you would help me.

John W. Vinson said:
hmmm... the sql seems right and is working perfectly fine..
but the results are not what i wanted..


What i wanted was the sum of the total amount made after
[Query1].[lDate_TImeOfMax] grouped by payment mode and staff ID.


I know we are getting close to what i want, hope you could help.
Click to expand...


You've described it in words. Just put it into the SQL.

SQL is a very logical language - a query consists of clauses; a SELECT clause
describing which fields to include; a FROM clause indicating which table or
tables to select from; in this case a GROUP BY clause indicating which fields
to group by; a WHERE clause indicating what criteria to apply.

ASSUMING - and you haven't said, so I don't know if it's correct - that Query1
returns only one record, go back to the Cartesian join and try

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction, Query1
WHERE SalesTransaction.[stDate/Time]>=[Query1].[lDate_TImeOfMax]
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID;

If my assumption about Query1 is wrong, please post its SQL and indicate what
it's intended to provide.
Click to expand...
Click to expand...
 
K

KenNiuM

This is my latest update, i still have a little problem.
below is my sql..

SELECT Query1.lStaffID, Sum(SalesTransaction.stSubtotal) AS Totalamount,
Query1.lCashRegisterOfLast, SalesTransaction.stPaymentMode
FROM SalesTransaction INNER JOIN Query1 ON SalesTransaction.stPaymentMode =
Query1.stPaymentMode
WHERE (((SalesTransaction.stDate)>=[Query1].[lDate_TImeOfMax]))
GROUP BY Query1.lStaffID, Query1.lCashRegisterOfLast,
SalesTransaction.stPaymentMode;


The supposed result for the second row total amount is 10 and not 13. It
seems that it add up a 3 from the 3rd row.
I wish you could help me..

hmmm... the sql seems right and is working perfectly fine..
but the results are not what i wanted..


What i wanted was the sum of the total amount made after
[Query1].[lDate_TImeOfMax] grouped by payment mode and staff ID.


I know we are getting close to what i want, hope you could help.


You've described it in words. Just put it into the SQL.

SQL is a very logical language - a query consists of clauses; a SELECT clause
describing which fields to include; a FROM clause indicating which table or
tables to select from; in this case a GROUP BY clause indicating which fields
to group by; a WHERE clause indicating what criteria to apply.

ASSUMING - and you haven't said, so I don't know if it's correct - that Query1
returns only one record, go back to the Cartesian join and try

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Sum(SalesTransaction.stTotalAmount) As Totalamount
FROM SalesTransaction, Query1
WHERE SalesTransaction.[stDate/Time]>=[Query1].[lDate_TImeOfMax]
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID;

If my assumption about Query1 is wrong, please post its SQL and indicate what
it's intended to provide.
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

I found out the current problem. Here is my current SQL.

SELECT Query1.lStaffID, Query1.stPaymentMode,
Sum(SalesTransaction.stTotalAmount) AS Totalamount
FROM SalesTransaction, Query1
WHERE (((SalesTransaction.stDate)>=[Query1].[lDate_TImeOfMax]))
GROUP BY Query1.lStaffID, Query1.stPaymentMode;

Currently the sum is grouped by staffID and not paymentMode.
How do i group by both staffID and PaymentMode?
Click to expand...

The query as written IS grouped by Paymentmode.

AGAIN... *I cannot see your screen*. I do not know what Query1 might be; I do
not know what data is there.

Could you post some sample data and the result that you are seeing?
 
K

KenNiuM

http://s106.photobucket.com/albums/m268/KenNiuM/?action=view&current=Untitled.jpg

This is my SQL for the query above which is query 1.
SELECT CashierLoginDetails.lStaffID, Max(CashierLoginDetails.[lDate/TIme])
AS lDate_TImeOfMax, Last(CashierLoginDetails.lCashRegister) AS
lCashRegisterOfLast, SalesTransaction.stPaymentMode
FROM SalesTransaction INNER JOIN CashierLoginDetails ON
(SalesTransaction.stCashRegister = CashierLoginDetails.lCashRegister) AND
(CashierLoginDetails.lOutlet = SalesTransaction.stOutlet) AND
(SalesTransaction.stCashierID = CashierLoginDetails.lStaffID)
GROUP BY CashierLoginDetails.lStaffID, SalesTransaction.stPaymentMode;



http://s106.photobucket.com/albums/m268/KenNiuM/?action=view&current=Untitled1-1.jpg

This SQL is for the above.
SELECT Query1.lStaffID, Sum(SalesTransaction.stSubtotal) AS Totalamount,
Query1.lCashRegisterOfLast, SalesTransaction.stPaymentMode
FROM SalesTransaction INNER JOIN Query1 ON SalesTransaction.stPaymentMode =
Query1.stPaymentMode
WHERE (((SalesTransaction.stDate)>=[Query1].[lDate_TImeOfMax]))
GROUP BY Query1.lStaffID, Query1.lCashRegisterOfLast,
SalesTransaction.stPaymentMode;

The supposed result for the second row total amount is 10 and not 13. It
seems that it add up a 3 from the 3rd row.

Hope to get some help.
Would it be better if i am able to upload my database somewhere?


John W. Vinson said:
I found out the current problem. Here is my current SQL.

SELECT Query1.lStaffID, Query1.stPaymentMode,
Sum(SalesTransaction.stTotalAmount) AS Totalamount
FROM SalesTransaction, Query1
WHERE (((SalesTransaction.stDate)>=[Query1].[lDate_TImeOfMax]))
GROUP BY Query1.lStaffID, Query1.stPaymentMode;

Currently the sum is grouped by staffID and not paymentMode.
How do i group by both staffID and PaymentMode?
Click to expand...

The query as written IS grouped by Paymentmode.

AGAIN... *I cannot see your screen*. I do not know what Query1 might be; I do
not know what data is there.

Could you post some sample data and the result that you are seeing?
Click to expand...
 

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

Queries runs for ever - Code and Formula 2
Extremely slow IN clause 8
Get transaction balance prior to a certain date 2
Using Min & Max in combined SQL statements 4
Updatable query no longer updatable, and slow IN clause 1
Jet Database Engine Error on Crosstab Query 1
Expression changes (97 to 2000 version) 1
Totals Query 10

Top