-
Jan 25th, 2015, 08:39 AM
#1
Thread Starter
Enjoy the moment
[RESOLVED] you tried to execute a query that does not include the specified expression why?
i am getting this error why?
Code:
you tried to execute a query that does not include the specified expression as part of an aggregate 'TempDate'
what is wrong with the code?
Code:
Dim DateToday As Date
DateToday = Format(Now, "DD/MM/YYYY")
Dim RsA As New ADODB.Recordset
RsA.Open "Select TempCash.TempDate ,Sum(TempCash.TempPay) AS SumOfTempPay FROM TempCash Where TempMethod = 'Cash' AND TempDate = #" & DateToday & "#", CN
tnx for any help
salsa31
-
Jan 25th, 2015, 09:35 AM
#2
Re: you tried to execute a query that does not include the specified expression why?
Originally Posted by salsa31
Code:
Dim DateToday As Date
DateToday = Format(Now, "DD/MM/YYYY")
This has nothing to do with your question but I just wanted to point out something. You can retrieve the current system date by using the intrinsic Date function; there's absolutely no need for the DateToday = Format(Now, ... line.
On Local Error Resume Next: If Not Empty Is Nothing Then Do While Null: ReDim i(True To False) As Currency: Loop: Else Debug.Assert CCur(CLng(CInt(CBool(False Imp True Xor False Eqv True)))): Stop: On Local Error GoTo 0
Declare Sub CrashVB Lib "msvbvm60" (Optional DontPassMe As Any)
-
Jan 25th, 2015, 09:41 AM
#3
Re: you tried to execute a query that does not include the specified expression why?
Because you have listed one column in your SQL SELECT statement that is not encapsulated in the SQL SUM function, you must use the SQL GROUP BY clause. The department field must, therefore, be listed in the SQL GROUP BY section.
If you are selecting other columns other than the Sum(), you need to include a Group By clause which goes on the end of your SQL statement, in this case.
Group By TempCash.TempDate
P.S. Sum() isn't the only function that may require a Group By clause. You must look at help files regarding the SQL functions you want to use. There are many out there and here is just one of them
-
Jan 25th, 2015, 09:50 AM
#4
Thread Starter
Enjoy the moment
Re: you tried to execute a query that does not include the specified expression why?
hey lavolpe
i tried group by still get error on TempPay Now
i am only trying to sum the income of today and use the where cluase
do i need a loop for the sum?
Code:
Dim RsA As New ADODB.Recordset
RsA.Open "Select TempCash.TempPay, Sum(TempCash.TempPay) As SumOfTempPay FROM TempCash Where TempMethod = 'Cash' AND TempDate = #" & DateToday & "# GROUP BY TempCash.TempDate", CN
Do While Not RsA.EOF
Me.Sections("Section1").Controls("LblCash").Caption = RsA!SumOfTempPay
RsA.MoveNext
Loop
-
Jan 25th, 2015, 09:53 AM
#5
Re: you tried to execute a query that does not include the specified expression why?
Originally you hade this:
RsA.Open "Select TempCash.TempDate ,Sum(TempCash.TempPay)
You were told to add this:
GROUP BY TempCash.TempDate
So WHY did you change this:
RsA.Open "Select TempCash.TempPay, Sum(TempCash.TempPay) As SumOfTempPay
????
-tg
-
Jan 25th, 2015, 09:56 AM
#6
Re: you tried to execute a query that does not include the specified expression why?
Code:
Select TempCash.TempDate, Sum(TempCash.TempPay) As SumOfTempPay FROM TempCash Where TempMethod = 'Cash' AND TempDate = #" & DateToday & "# GROUP BY TempCash.TempDate
But why do you even need to return the TempDate in this case? You already know the date. The query would then be:
Code:
Select Sum(TempCash.TempPay) As SumOfTempPay FROM TempCash Where TempMethod = 'Cash' AND TempDate = #" & DateToday & "#"
-
Jan 25th, 2015, 10:01 AM
#7
Thread Starter
Enjoy the moment
Re: you tried to execute a query that does not include the specified expression why?
i feel very stupid today
tnk you tg for the help
tnk you Lavolpe for you help
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|