Solved Dlookup Mismatch error

[Poco_90]

I know (I actually don't ) it is somthing stupid. I had this DLookup working but for some reason now it isn't. When it was working I had me.txtSerial, but with tiral an derror and other fourm suggestions it has changed to below.

If DLookup("[Serial Number]", "[tblPassed]", "[Serial Number] ='" & [Forms]![frmValidation]![txtSerial] & "'") Then
                          MsgBox "Serial Number is Good"

It is embarassing how long I have spent trying to resolve this, but would appreciate any input.


I have a form(frmValidation) that is taking in a delimited string into a field. I am splitting the elements of the string into individaul text boxes, one being txtSerial, and my aim os to see if this value is in the tblPassed table.

Thanks in advance.

 

[GaP42]

{Serial Number] is defined as of type integer? and txtSerial - is a string. Need to convert

 

[jdraw]

Can you post a copy of the database with info to highlight the issue?

Is there any more to the error message?

 

[Gasman]

If serial number is numeric, remove the single quotes from your form control. However, why would you lookup a serial number, using the serial number you already have? and not test equal
Perhaps use Dcount() with same criteria > 0

 

[XPS35]

The DLookup will return the value of er serial number. If .... Then expects an expression that is True or False where the .... are.
I think that is why you get a type mismatch.

 

[MajP]

if Dcount("*", "[tblPassed]", "[Serial Number] ='" & [Forms]![frmValidation]![txtSerial] & "'") > 0 Then
                          MsgBox "Serial Number is Good"

 

[plog]

Your If will never resolve to False. Never. It can only be True or throw an error.

Even if you have a serial number that is literally "FALSE", that will resolve to True. If it doesn't find the serial number at all it will blow up and throw an error, which is what I believed happened. You should not use DLookup you should use DCount. MajP has provided an example.

Additionally, you are not doing yourself any favors by having spaces in field names (e.g. [Serial Number]). It just makes coding and querying that much more difficult. Only use alphanumeric and underscores in Access names.

 

[Poco_90]

I don't know what happened. I posted the DB 4 hours ago but it only seem to go through now. Thanks for the replies and advice I appreciate it. I will try MajP suggestion.

The spaces in the field names are not by my design...Too many cooks!

 

[arnelgp]

you can also try:

    If DCount("1", "[tblPassed]", "[Customer Part Number] ='" & LArray(0) & "' And " & _
                "[Part Number] = '" & LArray(1) & "' And [Serial Number] = '" & LArray(2) & "'") <> 0 Then
                          MsgBox "SerialNumber is Good"
    Else
        MsgBox "SerialNumber is Bad, please double check"
        Exit Sub
    End If

 

[Isaac]

Dim serial As Variant
serial = DLookup("[Serial Number]", "[tblPassed]", "[Serial Number] ='" & [Forms]![frmValidation]![txtSerial] & "'")
If Not IsNull(serial) Then
MsgBox "Serial Number is Good"
Else
MsgBox "Serial Number not found"
End If

 

[Poco_90]

Thanks again for all the suggestions, tried them all and they all worked as expected. Now I am spoilt for choice!