Data Type mismatch in criteria expression - WHY?WHY?WHy?

[vman92]

' Usage: PrintAllCustomers("Reportnamehere")
Public Sub PrintAllCustomers(ReportName As String)
Dim ADOCon As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim Folder As String ' to hold directory information
Dim NumLoop As Integer
Dim sqlstr As String

NumLoop = 1
'now lets ask directory you want to save
Folder = InputBox("Where do you want to save files?", "Destination
Folder", Application.CurrentProject.Path)
'if canceled leave the sub without processing.
If Folder = "" Then Exit Sub

Set ADOCon = Application.CurrentProject.Connection 'get current
connection
Set Rst = New ADODB.Recordset 'to query current customers
'open recordset and load all data we need into recordset
'only print the customers we want
sqlstr = "SELECT * FROM Business WHERE CustomerNumber = '" &
NumLoop & "';"

Rst.Open sqlstr, ADOCon

DoCmd.SetWarnings WarningsOff
Do While Not Rst.EOF 'start sending all reports to files
CustomerID = Rst!CustomerNumber 'set necessary global variables
before print to file
Filename = Folder & "\" & Rst!BusinessName & ".SNP" 'set folder
and filename.
'Rst.Filter = "CustomerNumber = " & CustomerID
'MsgBox CustomerNumber
'MsgBox BusinessName
'send report to file
DoCmd.OutputTo acOutputReport, ReportName, acFormatSNP,
Filename, True
'DoCmd.OpenReport ReportName, acViewPreview, , CustomerID
Rst.MoveNext 'next customer
Loop
DoCmd.SetWarnings WarningsOn

Rst.Close
Set Rst = Nothing
End Sub

 

[Jeff L]

This would help a lot more if you could indicate where you are getting
the error.

 

[vman92]

Sorry, I am getting the error when moving to the (Rst.Open sqlstr,
ADOCon) command.
When I hover over the sqlstr in debug I get the SQL expression I wrote
in the previous statement. Not sure if this is right or not. Shouldn't
I see the results of the SQL query?

 

[Jeff L]

No when you hover over it you will only see the SQL statement. One
thing I noticed in your statement
sqlstr = "SELECT * FROM Business WHERE CustomerNumber = '" &
NumLoop & "';"
is that you have single quotes around NumLoop, which is an integer. Is
CustomerNumber an integer (or at least a numeric value) as well? If it
is then that is where your issue is. You are trying to compare a
string value to an integer field. Take out the single quotes and it
should work better.

sqlstr = "SELECT * FROM Business WHERE CustomerNumber = " & NumLoop &
";"

Hope that helps!

 

[vman92]

I believe that will work. Thanks.

No when you hover over it you will only see the SQL statement. One
thing I noticed in your statement
sqlstr = "SELECT * FROM Business WHERE CustomerNumber = '" &
NumLoop & "';"
is that you have single quotes around NumLoop, which is an integer. Is
CustomerNumber an integer (or at least a numeric value) as well? If it
is then that is where your issue is. You are trying to compare a
string value to an integer field. Take out the single quotes and it
should work better.

sqlstr = "SELECT * FROM Business WHERE CustomerNumber = " & NumLoop &
";"

Hope that helps!