Dlookup returning a "type mismatch" error. HELP 1

[dmon000]

The following code is returning a type mismatch error:
ShowWarn = DLookup("[status_id]", "[q_dm_Fallout]", "[SN] = " & [Forms]![f_dm_DashBoard_1]![txtSerialNumber])

The above is part of this subroutine: "txtSerialNumber" is textbox on a form into which a serial number is typed.

Private Sub NMR_Warn()
Dim ShowWarn As Long
ShowWarn = DLookup("[status_id]", "[q_dm_Fallout]", "[SN] = " & [Forms]![f_dm_DashBoard_1]![txtSerialNumber])
If ShowWarn < 20 Then
Me.lblOpenNMR.Visible = True
Else: Me.lblOpenNMR.Visible = False
End If
End Sub

Why is it returning this error? And how can I fix it?

Thanks!!!

 

[LonnieJohnson]

I would guess one of three reasons.

1) ShowWarn and [status_id] are two different data types.
2) [SN] and [Forms]![f_dm_DashBoard_1]![txtSerialNumber] are two different data types.
3) [Forms]![f_dm_DashBoard_1]![txtSerialNumber] is a text value and you need to put tick marks around it.

Hope that helps.


ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[Ed2020]

Does this work?

ShowWarn = DLookup("[status_id]", "[q_dm_Fallout]", "[SN] = " & """" & [Forms]![f_dm_DashBoard_1]![txtSerialNumber] & """")

If so it's because your serial number field is a text field and therefore the value passed to the criteria needs to be qualified with quotes.

Ed Metcalfe.

Please do not feed the trolls.....

 

[TheAceMan1]

How are ya dmon000 . . .

. . . or perhaps:

[blue]DLookup("[status_id]", "[q_dm_Fallout]", "[SN] = '" & [Forms]![f_dm_DashBoard_1]![txtSerialNumber] & "'"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[Ed2020]

TheAceMan1,

You're missing a closing parethesis at the end of that statement...

Ed Metcalfe.

Please do not feed the trolls.....

 

[dmon000]

Ed, your first piece of code worked fine. I still don't understand why. I had to add the Nz function to the whole expression because some of the records do not have a status_id.

Thanks again for all the help guys!!!

 

[Ed2020]

dmon000,

Access adds a qualifier character around certain data types.

Strings or text have quotes around them.
Date fields have a # (e.g. #12/02/2007 14:41:00#)
Numeric fields have no qualifier.

Your serial number field is text, so the criteria you use in DLookup must enclose the value in quotes.

Ed Metcalfe.

Please do not feed the trolls.....