I have two listboxes. The first listbox contains a list of each item type queried from an order. The second listbox queries each item of the selected item type in the first listbox. The user will enter serial numbers into a textbox which will update the selected item. I need to determine when serial numbers have been entered for every item of every item type on that order. I suppose another possible approach would be to query the table the listbox is queried on directly to determine if there are any null values. This might actually be preferrable because I can query all items at once regardless of what is selected in the listboxes, I'm just not sure how to do it.

I tried to run a query to display all records where SerialNumber = "" and then if a recordcount = 0 then all serials have been entered, but I keep getting "You tried to execute a query that does not include the specified expression 'POID' as part of an aggregate function."

strSQL = "SELECT POID, SerialNumber, Serialized, Count(SerialNumber) " & _
"FROM Items " & _
"WHERE POID = " & Me.POID & " AND Serialized = True AND SerialNumber = '';"

With CurrentDb.OpenRecordset(strSQL)
MsgBox .RecordCount
If .RecordCount = 0 Then
Me.Rcvd = True
Else
Me.Rcvd = False
End If
End With

Hi,

I'm not entirely clear with what your goal is, but your SQL is incorrect. You've created an aggrigate statement, but not included any criteria to group it by. I also do not understand the 'Serialnumber = ";" ' part. What are you trying to accomplish? Finding a serial number that is ';'? Trying to end your SQL? Trying to look for empty or null values?

Looking for ";" :

" AND serialNumber = " & chr(34) & ";" & chr(34)

Looking for null :

" And isnull(serialNumber)"

If you're just trying to end your statement, you can drop the last part alltogether.

Try :

strSQL = "SELECT POID, SerialNumber, Serialized, Count(SerialNumber) " & _
"FROM Items " & _
"WHERE POID = " & Me.POID & " AND Serialized = True group by POID, Serialnumber, Serialized"


Hope this helps some,

Adam

I am trying to determine if there are any records where SerialNumber is empty, I could us IsNull but I know there is a difference between Null and "" and there may be some cases where a value is erased. I tried your suggestion but now the form that calls the form I am working on (via onDblClick event) suddenly stopped calling the form. Very strange since nothing has been modified on that form. I've got to get that straightened out now. I have used similar SQL statements before and I don't see where the problem is at but the only difference I see in the SQL is the addition of a GROUP BY statement.

Thanks for the help!

Hi,

Yes, the only difference is the GROUP BY, which is all the difference. The error you encountered said you needed a GROUP BY, so you will need to include that.

You can try this :

strSQL = "SELECT POID, SerialNumber, Serialized, Count(SerialNumber) as SerialCount " & _
"FROM Items " & _
"WHERE POID = " & Me.POID & " AND Serialized = True " & _
"AND( isnull(SerialNumber) or SerialNumber = " & chr(34) & "" & chr(34) & _
" group by POID, Serialnumber, Serialized"

You will still need the GROUP BY clause added in. I've also just looked over your sql, and that COUNT isn't going to do much for you - you're including it both in the main part and the aggrigate part. To get a result, you will need to remove the SerialNumber from the Select and Group by areas.

Hope this helps,

Adam

It works! Typo in the SQL though (IMG:http://www.utteraccess.com/forum/style_emoticons/default/grin.gif)

strSQL = "SELECT POID, SerialNumber, Serialized " & _
"FROM Items " & _
"WHERE POID = " & Me.POID & " AND Serialized = True " & _
"AND IsNull(SerialNumber) OR SerialNumber = " & Chr(34) & "" & Chr(34) & _
"GROUP BY POID, Serialnumber, Serialized;"

I was initially trying to get a record count of all items where SerialNumber is empty which is why I initially left out the group by statement because I didn't want it to group the records. This way I only get a 1 or 0 on the record count but it actually works better since I am only trying to determine if any matching records exist.

Thanks for your help!

Whoops (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Glad I could help,

Adam