Data Type Mismatch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am trying to run this code below, and cant find out why I keep getting
error message - Data Type Mismatch in Criteria Expression, this appears on
line

rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly

Can anyone let me know where I'm going wrong, I have built the query in the
design query and also run the Debug.Print and same query appears, but cant
see the error?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag='False' "
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
With Me
lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
Else
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
End If
.lstSearch.SetFocus
End With
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub
 
Hello Jez.

Jez said:
Hi, I am trying to run this code below, and cant find out why I keep
getting error message - Data Type Mismatch in Criteria Expression,
this appears on line

rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly

Can anyone let me know where I'm going wrong, I have built the query
in the design query and also run the Debug.Print and same query
appears, but cant see the error?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS
[Job Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " &
vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " &
vbCrLf & _
"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag='False' "
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
Click to expand...

[snip]

Are you sure about the datatype of the InputFlag column?
If it is boolean, try to omit the quotes in the criterion.
 
Hi Jez,
Try this

"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag=0 "

I say that 'cause I assume that InputFlag is defined as boolean. In this
case you can't search it for a string as you do ('false').
When you watch the field in the table you see the value false but actually
the field contains 0 for false and -1 for true.

HTH Paolo
 
Paolo,
Thanks for the tip, I changed it and seems to run through the code better,
no error now. Apart from it doenst return any values, I know there are as it
should return 313 lines. How can I be sure its connecting to the other
database?

I thought about taking the recordset parts out to test but still no returns

Where am I going wrong?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
'Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
'Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.InputFlag= 0 AND tblCSATAddress.CSATNumber <> 1"
cnn.Execute sQRY
'rs.CursorLocation = adUseClient
'rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
'With Me
' lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
' If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
' Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
' Else
' Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
' End If
' .lstSearch.SetFocus
' End With
'rs.Close
cnn.Close
'Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub


Paolo said:
Hi Jez,
Try this

"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag=0 "

I say that 'cause I assume that InputFlag is defined as boolean. In this
case you can't search it for a string as you do ('false').
When you watch the field in the table you see the value false but actually
the field contains 0 for false and -1 for true.

HTH Paolo

Jez said:
Hi, I am trying to run this code below, and cant find out why I keep getting
error message - Data Type Mismatch in Criteria Expression, this appears on
line

rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly

Can anyone let me know where I'm going wrong, I have built the query in the
design query and also run the Debug.Print and same query appears, but cant
see the error?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag='False' "
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
With Me
lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
Else
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
End If
.lstSearch.SetFocus
End With
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub
Click to expand...
Click to expand...
 
Well,
to be sure that your query extract something from your datasource you can
try a recordcount on your recordset

msgbox SQRY.recordcount

Regards

Jez said:
Paolo,
Thanks for the tip, I changed it and seems to run through the code better,
no error now. Apart from it doenst return any values, I know there are as it
should return 313 lines. How can I be sure its connecting to the other
database?

I thought about taking the recordset parts out to test but still no returns

Where am I going wrong?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
'Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
'Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.InputFlag= 0 AND tblCSATAddress.CSATNumber <> 1"
cnn.Execute sQRY
'rs.CursorLocation = adUseClient
'rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
'With Me
' lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
' If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
' Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
' Else
' Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
' End If
' .lstSearch.SetFocus
' End With
'rs.Close
cnn.Close
'Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub


Paolo said:
Hi Jez,
Try this

"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag=0 "

I say that 'cause I assume that InputFlag is defined as boolean. In this
case you can't search it for a string as you do ('false').
When you watch the field in the table you see the value false but actually
the field contains 0 for false and -1 for true.

HTH Paolo

Jez said:
Hi, I am trying to run this code below, and cant find out why I keep getting
error message - Data Type Mismatch in Criteria Expression, this appears on
line

rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly

Can anyone let me know where I'm going wrong, I have built the query in the
design query and also run the Debug.Print and same query appears, but cant
see the error?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag='False' "
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
With Me
lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
Else
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
End If
.lstSearch.SetFocus
End With
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Sorry,
better msgbox rs.recordcount

Paolo said:
Well,
to be sure that your query extract something from your datasource you can
try a recordcount on your recordset

msgbox SQRY.recordcount

Regards

Jez said:
Paolo,
Thanks for the tip, I changed it and seems to run through the code better,
no error now. Apart from it doenst return any values, I know there are as it
should return 313 lines. How can I be sure its connecting to the other
database?

I thought about taking the recordset parts out to test but still no returns

Where am I going wrong?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
'Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
'Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.InputFlag= 0 AND tblCSATAddress.CSATNumber <> 1"
cnn.Execute sQRY
'rs.CursorLocation = adUseClient
'rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
'With Me
' lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
' If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
' Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
' Else
' Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
' End If
' .lstSearch.SetFocus
' End With
'rs.Close
cnn.Close
'Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub


Paolo said:
Hi Jez,
Try this

"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag=0 "

I say that 'cause I assume that InputFlag is defined as boolean. In this
case you can't search it for a string as you do ('false').
When you watch the field in the table you see the value false but actually
the field contains 0 for false and -1 for true.

HTH Paolo

:

Hi, I am trying to run this code below, and cant find out why I keep getting
error message - Data Type Mismatch in Criteria Expression, this appears on
line

rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly

Can anyone let me know where I'm going wrong, I have built the query in the
design query and also run the Debug.Print and same query appears, but cant
see the error?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag='False' "
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
With Me
lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
Else
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
End If
.lstSearch.SetFocus
End With
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Was just writing back then to say it doesnt work with sQRY.

Ok, now putting the MsgBox in it now shows 313 records when the code is run,
so now I know that its is connecting to the BackEnd DB. How can I from the
code earlier get it to post the results into the List Box?

Jez


Paolo said:
Sorry,
better msgbox rs.recordcount

Paolo said:
Well,
to be sure that your query extract something from your datasource you can
try a recordcount on your recordset

msgbox SQRY.recordcount

Regards

Jez said:
Paolo,
Thanks for the tip, I changed it and seems to run through the code better,
no error now. Apart from it doenst return any values, I know there are as it
should return 313 lines. How can I be sure its connecting to the other
database?

I thought about taking the recordset parts out to test but still no returns

Where am I going wrong?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
'Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
'Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.InputFlag= 0 AND tblCSATAddress.CSATNumber <> 1"
cnn.Execute sQRY
'rs.CursorLocation = adUseClient
'rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
'With Me
' lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
' If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
' Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
' Else
' Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
' End If
' .lstSearch.SetFocus
' End With
'rs.Close
cnn.Close
'Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub


:

Hi Jez,
Try this

"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag=0 "

I say that 'cause I assume that InputFlag is defined as boolean. In this
case you can't search it for a string as you do ('false').
When you watch the field in the table you see the value false but actually
the field contains 0 for false and -1 for true.

HTH Paolo

:

Hi, I am trying to run this code below, and cant find out why I keep getting
error message - Data Type Mismatch in Criteria Expression, this appears on
line

rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly

Can anyone let me know where I'm going wrong, I have built the query in the
design query and also run the Debug.Print and same query appears, but cant
see the error?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag='False' "
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
With Me
lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
Else
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
End If
.lstSearch.SetFocus
End With
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Try lstSearch.RowSource = rs

Paolo
Jez said:
Was just writing back then to say it doesnt work with sQRY.

Ok, now putting the MsgBox in it now shows 313 records when the code is run,
so now I know that its is connecting to the BackEnd DB. How can I from the
code earlier get it to post the results into the List Box?

Jez


Paolo said:
Sorry,
better msgbox rs.recordcount

Paolo said:
Well,
to be sure that your query extract something from your datasource you can
try a recordcount on your recordset

msgbox SQRY.recordcount

Regards

:

Paolo,
Thanks for the tip, I changed it and seems to run through the code better,
no error now. Apart from it doenst return any values, I know there are as it
should return 313 lines. How can I be sure its connecting to the other
database?

I thought about taking the recordset parts out to test but still no returns

Where am I going wrong?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
'Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
'Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.InputFlag= 0 AND tblCSATAddress.CSATNumber <> 1"
cnn.Execute sQRY
'rs.CursorLocation = adUseClient
'rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
'With Me
' lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
' If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
' Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
' Else
' Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
' End If
' .lstSearch.SetFocus
' End With
'rs.Close
cnn.Close
'Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub


:

Hi Jez,
Try this

"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag=0 "

I say that 'cause I assume that InputFlag is defined as boolean. In this
case you can't search it for a string as you do ('false').
When you watch the field in the table you see the value false but actually
the field contains 0 for false and -1 for true.

HTH Paolo

:

Hi, I am trying to run this code below, and cant find out why I keep getting
error message - Data Type Mismatch in Criteria Expression, this appears on
line

rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly

Can anyone let me know where I'm going wrong, I have built the query in the
design query and also run the Debug.Print and same query appears, but cant
see the error?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job
Number], " & vbCrLf & _
"tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
"tblCSATBusinessType.Description AS [Business Unit],
tblCSATAddress.Engineer " & vbCrLf & _
"FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON
tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
"WHERE tblCSATAddress.CSATNumber <> 1 AND
tblCSATAddress.InputFlag='False' "
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
With Me
lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20
Then
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount) - 1)
Else
Me.lblNumRecs.Caption = "Number Of Address " +
CStr((Me.lstSearch.ListCount))
End If
.lstSearch.SetFocus
End With
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top