SQL / VBA run-time error

[ray147]

I have an SQL query in VBA in which i'm trying to get the number of rows returned using the Count function...as below:

strSQL = "SELECT Count(*) As NumRows, DespatchRef, SupplierRef " & _
"FROM Despatches " & _
"WHERE DespDate = " & date1 & _
"AND (Shift = '" & shift1 & "');"

However, I'm getting the following run-time error:

"You tried to execute a query that does not include the specified expression 'DespatchRef' as part of an aggregate function"


If I remove the 'Count(*) As NumRows' bit, everything works fine...but i need to know the number of rows returns...what can i do?!

tnx

 

[Bat17]

Try
strSQL = "SELECT Count(*) As NumRows " & _
"FROM Despatches " & _
"WHERE Despatches.DespDate = #" & date1 & _
"# AND (Despatches.Shift = '" & shift1 & "');"

As the fields are not part of the return in the query you need to reference the table

HTH

Peter

 

[Bat17]

You will probably need to format the date as well

"WHERE Despatches.DespDate = #" & format(date1, "mm/dd/yyyy") & _

HTH

Peter

 

[ray147]

Peter,

Thanx for your help...but in the meantime I found a simpler way of doing it and works fine...check out below:

NumRows = DCount("DespatchRef", "Despatches", "[DespDate] = " & date1 & " AND Shift = '" & shift1 & "'")


Cheers!

 

[Mile-O]

in the meantime I found a simpler way of doing it and works fine...check out below:

NumRows = DCount("DespatchRef", "Despatches", "[DespDate] = " & date1 & " AND Shift = '" & shift1 & "'")

You still need to consider the date delimiters as you have been shown. It's also a smart move - again, shown - to format explicitly to US date format.

NumRows = DCount("DespatchRef", "Despatches", "[DespDate] = #" & Format(date1, "mm/dd/yyyy") & "# AND Shift = """ & shift1 & """")

If date1 and shift1 are textboxes then it pays to refer to them explicitly via the form's class: i.e. Me.date1, Me.shift1

NumRows = DCount("DespatchRef", "Despatches", "[DespDate] = #" & Format(Me.date1, "mm/dd/yyyy") & "# AND Shift = """ & Me.shift1 & """")

 

[SammyJ]

In your original query you need to either remove "DespatchRef, SupplierRef " from the SELECT clause, or add a "GROUP BY DespatchRef, SupplierRef" clause. Seeing as you are restricting these fields to values you already know there is no point returning them, so go with the first option.

But if you want to return them then you must GROUP BY them, so SQL knows that these are fields to compress down to. In any grouped query each SELECTed field must be an aggregate of some sort, or one of the fields at the level you are GROUPing to, and SQL expects you to tell it as such.

Sam.