Dlookup multiple criteria type mismatch

[Penstar]

I have the following code, but get type mismatch error... OrderID & PackID
are Integars and ProductID is a string. Each one works on their own, but get
the mismatch error when I try to put more then one in as the criteria.

Dim strType As String, strWhere As String, strType1 As String, strWhere1
As String, strType2 As String, strWhere2 As String

strType = Me.OrderID
strWhere = "[OrderID]=" & strType
strType1 = Me.ProductID
strWhere1 = "[ProductID]=""" & strType1 & """"
strType2 = Me.PackID
strWhere2 = "[PackID]=" & strType2

If IsNull(DLookup("OrderID", "dbrderDetails", strType And strType1
And strType2)) Then

MsgBox ("test")
Else
MsgBox "This Product and Packaging are already entered."
Cancel = True
Me.Undo
End If

 

[Klatuu]

It should look more like this. Notice the indentation of the code. It makes
it much easier to read:

Dim strWhere As String

If Not IsNull(Me.OrderID) Then
strWhere = "[OrderID] = " & Me.OrderID
End If
If Not IsNull(Me.ProductID) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[ProductID] = """ & Me.ProductID & """"
End If
If Not IsNUll([PackID]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & Me.PackID
End If

If Len(strWhere) = 0 Then
MsgBox "No Criteria Entered"
Else
If IsNull(DLookup("OrderID", "dbrderDetails", strWhere) Then
MsgBox "test"
Else
MsgBox "This Product and Packaging are already entered."
Cancel = True
Me.Undo
End If
End If

 

[Penstar]

Thank you. Works perfectly. I didn't realise I was so far off the mark with
this one...

Penny

It should look more like this. Notice the indentation of the code. It makes
it much easier to read:

Dim strWhere As String

If Not IsNull(Me.OrderID) Then
strWhere = "[OrderID] = " & Me.OrderID
End If
If Not IsNull(Me.ProductID) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[ProductID] = """ & Me.ProductID & """"
End If
If Not IsNUll([PackID]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & Me.PackID
End If

If Len(strWhere) = 0 Then
MsgBox "No Criteria Entered"
Else
If IsNull(DLookup("OrderID", "dbrderDetails", strWhere) Then
MsgBox "test"
Else
MsgBox "This Product and Packaging are already entered."
Cancel = True
Me.Undo
End If
End If
--
Dave Hargis, Microsoft Access MVP

I have the following code, but get type mismatch error... OrderID & PackID
are Integars and ProductID is a string. Each one works on their own, but get
the mismatch error when I try to put more then one in as the criteria.

Dim strType As String, strWhere As String, strType1 As String, strWhere1
As String, strType2 As String, strWhere2 As String

strType = Me.OrderID
strWhere = "[OrderID]=" & strType
strType1 = Me.ProductID
strWhere1 = "[ProductID]=""" & strType1 & """"
strType2 = Me.PackID
strWhere2 = "[PackID]=" & strType2

If IsNull(DLookup("OrderID", "dbrderDetails", strType And strType1
And strType2)) Then

MsgBox ("test")
Else
MsgBox "This Product and Packaging are already entered."
Cancel = True
Me.Undo
End If