Text field vs. Number field

[Guest]

I have the following code running properly when used on text fields. It
fails when I sub a number field. I sub Group in my strWhere statement with
GroupID and it fails. Can anyone help me with the correct syntax?

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT tblProduct.* tblProduct "

strWhere = "Where Group IN( "
For i = 0 To lstClass.ListCount - 1
If lstClass.Selected(i) Then
strWhere = strWhere & "'" & lstClass.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdOK_Click

End Sub

 

[Guest]

I believe this is the line that is causing the problem:

strWhere = strWhere & "'" & lstClass.Column(0, i) & "', "

For numeric fields, leave out the single quotes:

strWhere = strWhere & lstClass.Column(0, i) & ", "

For Date fields it is:

strWhere = strWhere & "#" & lstClass.Column(0, i) & "#, "

 

[Guest]

I removed the single quotes per your example and got a message "Item not
found in collection". When I run the query from the lstClass Row source
tblProduct - GroupID (number field) on the grid, records are retrieved
properly, but it fails when running from the cmdOK button. Any other ideas/

 

[Guest]

I don't see anything obvious. On which line do you get the error?
Does it fail on the first iteration of the For Next loop or somewhere in the
middle?

 

[Guest]

MY BAD; I mistakenly omitted a letter (s) from lstClass. All is fine NOW;
Thanks for your help. Greatly appreciated.

 

[Guest]

Glad I could help.

 

[Guest]

I tried adding another List Box (text field - CO) and building up the Where
string. I got an error message telling me Characters found after end of SQL
statement. Did I do something wrong with strWhere1?

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String, strWhere1 As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT tblProduct.* FROM tblProduct "
strWhere = "Where GroupID IN( "
For i = 0 To lstGroup.ListCount - 1
If lstGroup.Selected(i) Then
strWhere = strWhere & lstGroup.Column(0, i) & ", "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

strWhere1 = "Where CO IN( "
For i = 0 To lstClass.ListCount - 1
If lstClass.Selected(i) Then
strWhere1 = strWhere1 & "'" & lstClass.Column(0, i) & "', "
End If
Next i
strWhere1 = Left(strWhere1, Len(strWhere1) - 2) & ");"

strSQL = strSQL & strWhere & strWhere1
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdOK_Click
End If

End Sub

 

[Pieter Wijnen]

You've got a ; (semicolon) in the middle of your SQL, After the First IN
Clause.
FYI there's no need to append a ; in a SQL String (Except when using a
Parameter Clause)

Pieter