Data Type Mismatch in Criteria Expression

  • Thread starter Thread starter Jeff M
  • Start date Start date
J

Jeff M

I use the following code to locate the appropriate record
based on user input. I changed the data type from number
to text for the CustID field of all associated tables so
that I could use the input mask to limit user input to !
00000000.0;0;" ". Now I am getting a "Data Type Mismatch
in Criteria Expression" error. I assume that it is due to
the criteria in the code below, but I don't know how to
fix this.


Private Sub FindCustID_Click()
On Error GoTo Err_FindCustID_Click

Dim stDocName As String
Dim count As Variant
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"
count = DCount("[Cust ID]", "CustDetailsTable", "[Cust
ID]=" & Me![Text3])
stLinkCriteria = "[Cust ID]=" & Me![Text3]

If count = 1 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindCustID"
ElseIf count = 0 Then
DoCmd.OpenForm "IDDoesNotExist"
Me.Text3.SetFocus
End If

Exit_FindCustID_Click:
Exit Sub

Err_FindCustID_Click:
MsgBox Err.Description
Resume Exit_FindCustID_Click

End Sub
 
count = DCount("[Cust ID]", "CustDetailsTable", "[Cust
ID]=" & Me![Text3])
Click to expand...

You are passing the value to [Cust ID] as if it is a number, but you have
changed it to a string. You need to wrap it in quotes.

count = DCount("[Cust ID]", "CustDetailsTable", "[Cust ID]='" & Me![Text3] &
"'")

If the value is stored with leading zeros, you'll need to format this as
well since the strings won't match without them (they're no longer numeric
values which don't care about leading zeros).

count = DCount("[Cust ID]", "CustDetailsTable", "[Cust ID]='" &
Format(Me![Text3], "00000000.0") & "'")

Be aware that if there is more than one decimal in number in Text3, the
formatting to a single decimal may cause a round-off in the value.

You may have the same problem (with the same fixes) in the strLinkCriteria
expression.

--
Wayne Morgan
MS Access MVP


Jeff M said:
I use the following code to locate the appropriate record
based on user input. I changed the data type from number
to text for the CustID field of all associated tables so
that I could use the input mask to limit user input to !
00000000.0;0;" ". Now I am getting a "Data Type Mismatch
in Criteria Expression" error. I assume that it is due to
the criteria in the code below, but I don't know how to
fix this.


Private Sub FindCustID_Click()
On Error GoTo Err_FindCustID_Click

Dim stDocName As String
Dim count As Variant
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"
count = DCount("[Cust ID]", "CustDetailsTable", "[Cust
ID]=" & Me![Text3])
stLinkCriteria = "[Cust ID]=" & Me![Text3]

If count = 1 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindCustID"
ElseIf count = 0 Then
DoCmd.OpenForm "IDDoesNotExist"
Me.Text3.SetFocus
End If

Exit_FindCustID_Click:
Exit Sub

Err_FindCustID_Click:
MsgBox Err.Description
Resume Exit_FindCustID_Click

End Sub
Click to expand...
 
Back
Top