Type mismatch with DLookup

N

ndunwoodie

I'm looking in a table for a particular name (the one in a combo box on a
form) that has today's date. I used the following code, but I'm getting a
type mismatch. I was not surprised because I was guessing at the code.

If DLookup("txtName", "tblSilTrans", "txtName = " & Chr$(39) & cboName &
Chr$(39) And "datDate = " & Chr$(39) & Date & Chr$(39)) Then

Can someone correct this? Thanks.

Nick
 
D

Duane Hookom

This might work.
If DLookup("txtName", "tblSilTrans",
"txtName = " & Chr$(39) & cboName & Chr$(39) & " And datDate = #" & Date
& "#") Then
 
K

Klatuu

If DLookup("[txtName]", "tblSilTrans", "txtName = '" Me.cboName &
"' And [datDate = #" Date "#") Then
 
D

Douglas J Steele

Note that if the Regional Settings have set the short date format to
dd/mm/yyyy, that won't work for the first 12 days of each month. (okay,
it'll work on January 1st, February 2nd, March 3rd and so on, so there are
only 132 days when it won't!)

More reliable is:

If DLookup("txtName", "tblSilTrans", _
"txtName = " & Chr$(39) & cboName & _
Chr$(39) & " And datDate = " & _
Format(Date, "\#mm\/dd\/yyyy\#")) Then
 
N

ndunwoodie

Copying what you sent gave me a syntax error - it highlighted the "Me" as the
error

Klatuu said:
If DLookup("[txtName]", "tblSilTrans", "txtName = '" Me.cboName &
"' And [datDate = #" Date "#") Then


ndunwoodie said:
I'm looking in a table for a particular name (the one in a combo box on a
form) that has today's date. I used the following code, but I'm getting a
type mismatch. I was not surprised because I was guessing at the code.

If DLookup("txtName", "tblSilTrans", "txtName = " & Chr$(39) & cboName &
Chr$(39) And "datDate = " & Chr$(39) & Date & Chr$(39)) Then

Can someone correct this? Thanks.

Nick
 
N

ndunwoodie

I took your code and did the following:
dim x as variant
x = DLookup("txtName"......)
if x is Null then...

X was never "Null" - even when the table it was searching was empty.
 
N

ndunwoodie

Using your code with...

If DLookup("txtName",.......) then

gave me a type mismatch when the stmt was "true" - meaning it found a match
in the table. It seemed to work fine if the stmt was false. Suggestions?
 
D

Douglas J Steele

Are you certain that cboName is bound to a text field? If so, what name were
you using?

If the names can have apostrophes in them (like O'Reilly), try replacing the
Chr$(39) with Chr$(34). If the names can have both apostrophes and double
quotes in them (like O'Reilly's "Auld Sod Pub"), you'll need to do a little
more work.
 
D

Douglas J. Steele

Try

Dim strWhere As String

strWhere = "txtName = " & Chr$(39) & cboName & _
Chr$(39) & " And datDate = " & _
Format(Date, "\#mm\/dd\/yyyy\#")

Debug.Print strWhere

If DLookup("txtName", "tblSilTrans", _
strWhere) Then


Go to the Debug window (Ctrl-G) and check what's printed for strWhere. Does
it make sense?
 
T

Tim Ferguson

If DLookup("txtName", "tblSilTrans", "txtName = " & Chr$(39) & cboName
& Chr$(39) And "datDate = " & Chr$(39) & Date & Chr$(39)) Then

Apart from the acute error, this is not a sensible query. If the name
exists for today's date, then it will return a string value containing
the same name you queried on. If there is no matching record, then it
will return a null. In each case, you are expecting boolean value by
putting it inside an If...Then clause.

Try getting the logic right, and you can clarify and simplify it too.

' note correct quotes for delimiting the string, and
' reliable date format too.
strWhere = "txtName = """ & cboName.Value & """ " & _
"AND datDate = " & Format(Date(), "\#yyyy\-mm\-dd\#")

' now see if there is a record or not
If DCount("*", "tblSilTrans", strWhere)>0 Then
' there is a record
DoSomething

Else ' there isn't a record
DoSomethingElse

End If

PS: I see you said that there was an error involving "Me", but you did
not have any reference to the Me object in the code you posted!

Hope that helps


Tim F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top