Type Mismatch in Dlookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help me with the type mismatch in the following? Everything is of
type text. Thanks in advance.

If Not (IsNull(DLookup("txtName", "tblISS", "txtName = """ &
cboName.Column(0) & """" _
And "txtDate = """ & txtDateToServe & """"))) Then
 
Any chance that cboName.Column(0) or txtDateToServe might contain a Null
value? That will give that error if either of them is Null.
 
The AND in the WHERE portion of the statement must be in the quotes as well:

If Not (IsNull(DLookup("txtName", "tblISS", "txtName = """ &
cboName.Column(0) & "" And txtDate = """ & _
txtDateToServe & """"))) Then


If txtDate is a Date field (as opposed to Text), you need to delimit using #
characters (and you need to ensure that the format is consistent with what
Access will accept. In general, that means mm/dd/yyyy, regardless of what
you've set the Short Date format to using Regional Settings). The Format
statement I've included below takes care of both issues:

If Not (IsNull(DLookup("txtName", "tblISS", "txtName = """ &
cboName.Column(0) & """ And txtDate = " & _
Format(txtDateToServe, "\#mm\/dd\/yyyy\#")))) Then
 
Slight typo on my part:

If Not (IsNull(DLookup("txtName", "tblISS", "txtName = """ & _
cboName.Column(0) & "" And txtDate = """ & _
txtDateToServe & """"))) Then

That's supposed to be on 3 separate lines (with the first two lines both
ending with an underscore character preceded by a space)
 
Back
Top