Data type mismatch in criteria expression

[Emma]

Using the following code to open a report based on the
current records Accounting_Reference_No, I'm getting an
error "Data type mismatch in criteria expression". Can
anyone tell me why?

Private Sub Print_Sponsorship_Consulting_Request_Click()
On Error GoTo
Err_Print_Sponsorship_Consulting_Request_Click

Dim strDocName As String, strLinkCriteria As String

'if Request Type is 1 then print the current record
Sponsorship Form
If Request_Type_Frame = 1 Then
strDocName = "Rpt_Sponsorship_Request"
'select only the current form accounting reference number
to the report accounting reference no.
strLinkCriteria = "[Accounting_Reference_No]
= " & Me![Accounting_Reference_No]
DoCmd.OpenReport strDocName, acViewPreview, ,
strLinkCriteria

Else

'if Request Type is 2 then print the current record
Consulting Form
If Request_Type_Frame = 2 Then
strDocName = "Rpt_Consulting_Request"
'select only the current form accounting reference number
to the report accounting reference no.
strLinkCriteria
= "[Accounting_Reference_No] = " & Forms!Frm_Request!
[Accounting_Reference_No]
DoCmd.OpenReport strDocName,
acViewPreview, , strLinkCriteria
End If
End If

Exit_Print_Sponsorship_Consulting_Request:
Exit Sub

Err_Print_Sponsorship_Consulting_Request_Click:
MsgBox Err.Description
Resume Exit_Print_Sponsorship_Consulting_Request

End Sub

Thanks,

Emma

 

[Paul Johnson]

I would make an unfounded guess that the Accounting_Reference_No field is a
text data type. Your code ought to work if Accounting_Reference_No is
numeric. If it is a text field in your table design, then you need to
surround it in quotes in your strLinkCriteria string:

strLinkCriteria = "[Accounting_Reference_No] > = " & _
& Chr(34) & Me![Accounting_Reference_No] & Chr(34)

Or you can use single quotes:

strLinkCriteria = "[Accounting_Reference_No] > = '" & _
Me![Accounting_Reference_No] & "'" ' Note single quotes inside doubles
in two places

HTH
Paul Johnson

 

[Emma]

It is a text field! Thanks so much! I have to keep it a
text field because of the way accounting assigns the
reference number using the data and if we have the same
territory make a request on the same date we have to
add "A", "B", "C", etc.

Thanks again

-----Original Message-----
I would make an unfounded guess that the

Accounting_Reference_No field is a

text data type. Your code ought to work if Accounting_Reference_No is
numeric. If it is a text field in your table design, then you need to
surround it in quotes in your strLinkCriteria string:

strLinkCriteria = "[Accounting_Reference_No] > = " & _
& Chr(34) & Me![Accounting_Reference_No] & Chr(34)

Or you can use single quotes:

strLinkCriteria = "[Accounting_Reference_No] > = '" & _
Me![Accounting_Reference_No] & "'" ' Note single quotes inside doubles
in two places

HTH
Paul Johnson

Using the following code to open a report based on the
current records Accounting_Reference_No, I'm getting an
error "Data type mismatch in criteria expression". Can
anyone tell me why?

Private Sub Print_Sponsorship_Consulting_Request_Click()
On Error GoTo
Err_Print_Sponsorship_Consulting_Request_Click

Dim strDocName As String, strLinkCriteria As String

'if Request Type is 1 then print the current record
Sponsorship Form
If Request_Type_Frame = 1 Then
strDocName = "Rpt_Sponsorship_Request"
'select only the current form accounting reference number
to the report accounting reference no.
strLinkCriteria = "[Accounting_Reference_No]
= " & Me![Accounting_Reference_No]
DoCmd.OpenReport strDocName, acViewPreview, ,
strLinkCriteria

Else

'if Request Type is 2 then print the current record
Consulting Form
If Request_Type_Frame = 2 Then
strDocName = "Rpt_Consulting_Request"
'select only the current form accounting reference number
to the report accounting reference no.
strLinkCriteria
= "[Accounting_Reference_No] = " & Forms!Frm_Request!
[Accounting_Reference_No]
DoCmd.OpenReport strDocName,
acViewPreview, , strLinkCriteria
End If
End If

Exit_Print_Sponsorship_Consulting_Request:
Exit Sub

Err_Print_Sponsorship_Consulting_Request_Click:
MsgBox Err.Description
Resume Exit_Print_Sponsorship_Consulting_Request

End Sub

Thanks,

Emma

.