Type Mismatch

[Dan @BCBS]

I don't understand why I'm getting a "Type Mismatch" with this code.
The only value I'm using is the "ICNNO"
I'm trying to transfer data to a report with: select * from [q_LetterVals]
where [ICNNo] = " & "'" & txtICNNO & "';")

Suggestions???


Dim stDocName As String
Dim stLinkCriteria As String
Dim dbcurrent As Database
Dim rstemp As Recordset

stLinkCriteria = "[ICNNO]=" & "'" & txtICNNO & "'"

If IsNull(Me.txtICNNO) Or Me.txtICNNO = "" Then
MsgBox "Please enter an ICNNO number"
Exit Sub
End If


Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from [q_LetterVals] where
[ICNNo] = " & "'" & txtICNNO & "';")
stDocName = "rptThankYou"
If rstemp.RecordCount > 0 Then
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit Sub
End If

If rstemp.RecordCount <= 0 Then
MsgBox "There is no record for this ICN Number. Please review and
try again"
txtICNNO.SetFocus
Exit Sub
End If

 

[SteveM]

Are you sure that ICNNo is of Text data type?
If not, remove the single quotes.

Steve

 

[Klatuu]

Just a minor syntax problem:
select * from [q_LetterVals] where [ICNNo] = " & "'" & txtICNNO & "';")
try
select * from [q_LetterVals] where [ICNNo] = '" & txtICNNO & "';"

 

[Dirk Goldgar]

In

I don't understand why I'm getting a "Type Mismatch" with this code.
The only value I'm using is the "ICNNO"
I'm trying to transfer data to a report with: select * from
[q_LetterVals] where [ICNNo] = " & "'" & txtICNNO & "';")

Suggestions???


Dim stDocName As String
Dim stLinkCriteria As String
Dim dbcurrent As Database
Dim rstemp As Recordset

stLinkCriteria = "[ICNNO]=" & "'" & txtICNNO & "'"

If IsNull(Me.txtICNNO) Or Me.txtICNNO = "" Then
MsgBox "Please enter an ICNNO number"
Exit Sub
End If


Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from [q_LetterVals]
where [ICNNo] = " & "'" & txtICNNO & "';")
stDocName = "rptThankYou"
If rstemp.RecordCount > 0 Then
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit Sub
End If

If rstemp.RecordCount <= 0 Then
MsgBox "There is no record for this ICN Number. Please review
and try again"
txtICNNO.SetFocus
Exit Sub
End If

What version of Access are you using? Does it work if you declare the
recordset like this:

Dim rstemp As DAO.Recordset

?

In any case, you should close that recordset before you exit the
procedure.