Help with DLookup Mis-Match (1 Viewer)

[Clayhead22]

Hi All

I have been working for hours and got to the stage where i can not spot what is going wrong with my DLookup.

Could anyone please cast an eye? Getting a Mis-Match error.

If (IsNull(DLookup("[ID]", "Contacts", "[ID] ='" & Me.Contact_Selector.Value & "' And [Date_of_Birth] = #" & Me.DPA_Date_of_Birth & "# And [Email] = '" & Me.DPA_Email.Value & "' And [Mobile] = '" & Me.DPA_Mobile.Value & "' And [Postal_Code] = '" & Me.DPA_Postal_Code.Value & "'"))) Then
MsgBox "Incorrect Details Entered"
Else
MsgBox "DPA Passed!"
End If

Thank you!

 

[isladogs]

Hi All

I have been working for hours and got to the stage where i can not spot what is going wrong with my DLookup.

Could anyone please cast an eye? Getting a Mis-Match error.

If (IsNull(DLookup("[ID]", "Contacts", "[ID] ='" & Me.Contact_Selector.Value & "' And [Date_of_Birth] = #" & Me.DPA_Date_of_Birth & "# And [Email] = '" & Me.DPA_Email.Value & "' And [Mobile] = '" & Me.DPA_Mobile.Value & "' And [Postal_Code] = '" & Me.DPA_Postal_Code.Value & "'"))) Then
MsgBox "Incorrect Details Entered"
Else
MsgBox "DPA Passed!"
End If

Thank you!

The above looks ok for text and date fields
If ID is a number field, you don't need the single quotes

If you still have problems use the Nz function instead of IsNull

 

[jdraw]

Perhaps you could tell readers more about the fields/controls involved.
If you review/validate each item individually, you could give the user more precise error message(s).

 

[plog]

Are you sure its that part of code? Since you've treated all the criteria as text there shouldn't be any mismatches.

Strip out all your criteria from the Dlookup and just get it to work. Once it does, add the criteria back one criterion at a time.

 

[isladogs]

plog's comment is very sensible but...

Hang on a minute.... it makes no sense to look up the ID value and include ID in the list of criteria. Remove it

 

[Clayhead22]

Thanks for the Advice.

To let everyone know a little more. Its a form that opens so that we can verify a customer.

Someone would enter the details into the boxes and it checks them against the Table (Contacts)

To make sure i am looking at the correct contact i added an invisible text box on the right hand side which holders the contact ID This is

Me.Contact_Selector.Value

I am trying to lookup the below and check the details against the record.

Lookup ID in table contacts
where ID in contacts table = contact id in text box contact_Selector.

DLookup("[ID]", "Contacts", "[ID] ='" & Me.Contact_Selector.Value

 

[Clayhead22]

Ahh i see. Remove the second ID?

 

[isladogs]

Yes exactly that

Incidentally I tried to just type Yes as my answer but the forum software said it was too short!
Minimum of 10 characters

 

[Clayhead22]

Thinking about it if i remove that how will it know which contact to lookup?

Sorry still quite new to this.

 

[isladogs]

Surely that's what the lookup is for
Otherwise its looking at itself in the Access version of a mirror ... and it might not like what it sees!

And anyway am I right in thinking ID is a number field?

 

[Clayhead22]

ok have just done that and it worked! Thank you.

If (IsNull(DLookup("[ID]", "Contacts", " '" & Me.Contact_Selector.Value & "' And [Date_of_Birth] = #" & Me.DPA_Date_of_Birth & "# And [Email] = '" & Me.DPA_Email.Value & "' And [Mobile] = '" & Me.DPA_Mobile.Value & "' And [Postal_Code] = '" & Me.DPA_Postal_Code.Value & "'"))) Then
MsgBox "Incorrect Details Entered"
Else
MsgBox "DPA Passed!"
End If

 

[jdraw]

Did you test it with "good" and "bad" data?

 

[isladogs]

not quite what I meant. Amazed it worked!

Modified version below

If (IsNull(DLookup("[ID]", "Contacts","[Date_of_Birth] = #" & Me.DPA_Date_of_Birth & "# And [Email] = '" & Me.DPA_Email.Value & "' And [Mobile] = '" & Me.DPA_Mobile.Value & "' And [Postal_Code] = '" & Me.DPA_Postal_Code.Value & "'"))) Then
MsgBox "Incorrect Details Entered"
Else
MsgBox "DPA Passed!"
End If