Datatype Mismatch Error

[charles.kendricks]

I am trying to tie a small piece of code to a command button on a form
to open a particular report. The report is a client medical record
which I want to open from the clients medical records form. I am using
code almost identical to the code I use in another database to open
another report from a form. The code is as follows:

Private Sub Command148_Click()

Dim Ans As Byte
Dim strSSN As String

strSSN = Forms!frmFindClientByName!SSN
Ans = MsgBox("Is This Report For Next Week?", 36, "Current Or Next
Week?")
If Ans = 6 Then
DoCmd.OpenReport "rptWeeklyMedsAllNextWeek", acViewPreview, ,
"[tblClient_SSN]=" & strSSN

Else
DoCmd.OpenReport "rptWeeklyMedsAllThisWeek", acViewPreview, ,
"[tblClient_SSN]=" & strSSN

End If
End Sub

The error is:
Runtime error 3464
Data type mismatch in criteria expression

The SSN field in the table which the underlying query depends is a Text
field....Somebody please help me...

 

[Douglas J. Steele]

When your fields are text, the values need to be enclosed in quotes.

DoCmd.OpenReport "rptWeeklyMedsAllNextWeek", acViewPreview, , _
"[tblClient_SSN]=" & Chr$(34) & strSSN & Chr$(34)

or

DoCmd.OpenReport "rptWeeklyMedsAllNextWeek", acViewPreview, , _
"[tblClient_SSN]='" & strSSN & "'"

where, exagerated for clarity, that second option is


DoCmd.OpenReport "rptWeeklyMedsAllNextWeek", acViewPreview, , _
"[tblClient_SSN]= ' " & strSSN & " ' "