Keep getting a type mismatch error...why?

[emmaruwa]

The following VBA code, in Access 2003, is to make a button open and populate a form based on the Surname and Period_frm data on a previous form. However, i keep getting a type mismatch error whenever i click the button. I've tried changing the stLinkCriteria2 type to String but the same thing happens. Does anyone know why?

Private Sub IndividualRpt_Click()
On Error GoTo Err_Command12_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As Date

stDocName = "View report per individual"

stLinkCriteria = "[Surname]=" & "'" & Me![Surname] & "'"
stLinkCriteria2 = "[Period_frm]=" & "'" & Me![Period_frm] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria And stLinkCriteria2

Exit_IndividualRpt_Click:
Exit Sub

Err_IndividualRpt_Click:
MsgBox Err.Description
Resume Exit_IndividualRpt_Click

End Sub

[NeoPa]

Try the following modified code.
You had strings in Date fields + some other problems.
Let me know if you want all problems / changes explained.

Private Sub IndividualRpt_Click()
On Error GoTo Err_Command12_Click
 
Dim stDocName As String
Dim stLinkCriteria As String
 'Dim stLinkCriteria2 As Date
 
stDocName = "View report per individual"
 
 stLinkCriteria = "(([Surname]='" & [Surname]  & "') AND " & _
    "([Period_frm]=#" & Format([Period_frm],"M/D/YYYY") & "#))"
 'stLinkCriteria2 = "[Period_frm]=" & "'" & Me![Period_frm] & "'"
 DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_IndividualRpt_Click:
Exit Sub
 
Err_IndividualRpt_Click:
MsgBox Err.Description
Resume Exit_IndividualRpt_Click
 
End Sub

[emmaruwa]

Try the following modified code.
You had strings in Date fields + some other problems.
Let me know if you want all problems / changes explained.

Private Sub IndividualRpt_Click()
On Error GoTo Err_Command12_Click
 
Dim stDocName As String
Dim stLinkCriteria As String
 'Dim stLinkCriteria2 As Date
 
stDocName = "View report per individual"
 
 stLinkCriteria = "(([Surname]='" & [Surname]  & "') AND " & _
    "([Period_frm]=#" & Format([Period_frm],"M/D/YYYY") & "#))"
 'stLinkCriteria2 = "[Period_frm]=" & "'" & Me![Period_frm] & "'"
 DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_IndividualRpt_Click:
Exit Sub
 
Err_IndividualRpt_Click:
MsgBox Err.Description
Resume Exit_IndividualRpt_Click
 
End Sub

Thanks a lot, NeoPa. The code worked nicely after i made the necessary amendments. I appreciate this!!!

[NeoPa]

I'm glad to hear it worked for you :).