Data Type Mismatch - String versus Long

[Paul]

Hello,

I am trying to build a WHERE clause with numeric criteria and storing it as
string criteria, but I am getting the Data Type Mismatch error when I open
the DAO recordset. I have two list boxes on the form: a left list box and a
right list box with 'Move One' and 'Move All' command buttons between them.
The code for the 'Move All' command button is (assuming there are two list
items in the right list box):

' Create the WHERE clause criteria.
For lngCurrent = 0 To Me.lstRight.ListCount - 1
strWhere = strWhere & Me.lstRight.ItemData(lngCurrent) & ", "
Next lngCurrent

' Remove trailing comma and blank space.
strWhere = Left(strWhere, Len(strWhere) - 2)

' Clear SQL string.
strSql = ""

' Build SQL statement.
strSql = "PARAMETERS prmWhere Long; " & _
"SELECT lngOrderID, lngCustomerID " & _
"FROM tblOrders " & _
"WHERE lngOrderID NOT IN ([prmWhere]) " & _
"ORDER BY lngOrderID ASC;"

'Create an instance to the objects.
'Open the recordset.
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("", strSql)
With qdf
.Parameters("prmWhere").Value = strWhere
Set rs = .OpenRecordset(dbOpenDynaset)
End With

All of my DAO objects are declared with the DAO prefix in Access 2003. Any
help is appreciated.

Regards,

 

[Klatuu]

The problem is your SQL is not correctly formed.

strSQL = "SELECT lngOrderID, lngCustomerID " & _
"FROM tblOrders " & _
"WHERE lngOrderID NOT IN (strWhere) " & _
"ORDER BY lngOrderID ASC;"

 

[Paul]

Thanks. I had to change the NOT IN to: NOT IN (" & strWhere & ") " & _

Regards,

Paul

The problem is your SQL is not correctly formed.

strSQL = "SELECT lngOrderID, lngCustomerID " & _
"FROM tblOrders " & _
"WHERE lngOrderID NOT IN (strWhere) " & _
"ORDER BY lngOrderID ASC;"

Hello,

I am trying to build a WHERE clause with numeric criteria and storing it
as
string criteria, but I am getting the Data Type Mismatch error when I open
the DAO recordset. I have two list boxes on the form: a left list box and
a
right list box with 'Move One' and 'Move All' command buttons between
them.
The code for the 'Move All' command button is (assuming there are two list
items in the right list box):

' Create the WHERE clause criteria.
For lngCurrent = 0 To Me.lstRight.ListCount - 1
strWhere = strWhere & Me.lstRight.ItemData(lngCurrent) & ", "
Next lngCurrent

' Remove trailing comma and blank space.
strWhere = Left(strWhere, Len(strWhere) - 2)

' Clear SQL string.
strSql = ""

' Build SQL statement.
strSql = "PARAMETERS prmWhere Long; " & _
"SELECT lngOrderID, lngCustomerID " & _
"FROM tblOrders " & _
"WHERE lngOrderID NOT IN ([prmWhere]) " & _
"ORDER BY lngOrderID ASC;"

'Create an instance to the objects.
'Open the recordset.
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("", strSql)
With qdf
.Parameters("prmWhere").Value = strWhere
Set rs = .OpenRecordset(dbOpenDynaset)
End With

All of my DAO objects are declared with the DAO prefix in Access 2003. Any
help is appreciated.

Regards,

 

[Klatuu]

Well, if you wanted it work, you should have corrected my error

Thanks. I had to change the NOT IN to: NOT IN (" & strWhere & ") " & _

Regards,

Paul

The problem is your SQL is not correctly formed.

strSQL = "SELECT lngOrderID, lngCustomerID " & _
"FROM tblOrders " & _
"WHERE lngOrderID NOT IN (strWhere) " & _
"ORDER BY lngOrderID ASC;"

Hello,

I am trying to build a WHERE clause with numeric criteria and storing
it
as
string criteria, but I am getting the Data Type Mismatch error when I
open
the DAO recordset. I have two list boxes on the form: a left list box
and
a
right list box with 'Move One' and 'Move All' command buttons between
them.
The code for the 'Move All' command button is (assuming there are two
list
items in the right list box):

' Create the WHERE clause criteria.
For lngCurrent = 0 To Me.lstRight.ListCount - 1
strWhere = strWhere & Me.lstRight.ItemData(lngCurrent) & ", "
Next lngCurrent

' Remove trailing comma and blank space.
strWhere = Left(strWhere, Len(strWhere) - 2)

' Clear SQL string.
strSql = ""

' Build SQL statement.
strSql = "PARAMETERS prmWhere Long; " & _
"SELECT lngOrderID, lngCustomerID " & _
"FROM tblOrders " & _
"WHERE lngOrderID NOT IN ([prmWhere]) " & _
"ORDER BY lngOrderID ASC;"

'Create an instance to the objects.
'Open the recordset.
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("", strSql)
With qdf
.Parameters("prmWhere").Value = strWhere
Set rs = .OpenRecordset(dbOpenDynaset)
End With

All of my DAO objects are declared with the DAO prefix in Access 2003.
Any
help is appreciated.

Regards,