VBA syntax error in Date but no Date used

[electricjelly]

Hello,

I am trying to use a dlookup to find information based on specific criteria but, every time I use it I receive an error "syntax error in date in query expression". however, I am not using any date format and I am confused as to why this is not working.

Private Sub Quick_Look_Click()

'On Error GoTo ErrorHandler

Dim Lookup As String
Dim Box As String
Dim LastName As String
Dim Address As String

Box = InputBox("Input cat's release number", "Release Lookup")
Lookup = DLookup("First", "QryQuickLookUp", "Release#=" & Box)
LastName = DLookup("Last", "QryQuickLookUp", "Release#=" & Box)
Address = DLookup("Address", "QryQuickLookUp", "Release#=" & Box)

MsgBox "FIRST NAME: " & Lookup & " LAST NAME: " & LastName & " ADDRESS: " & Address, vbOKOnly, "Quick Look UP"

GoTo Exitsub

ErrorHandler:

MsgBox "Sorry there is no record with that release #"

Exitsub:
End Sub

 

[vbaInet]

You're using the hash (#) literal in three of your functions and when Access sees that it's expecting a date.

The entire function looks completely wrong anyway.

Why the DLookup? What are you trying to achieve?

 

[pbaldy]

I assume that's a field name; with the inadvisable symbol it would have to be bracketed:

[Release#]

 

[vbaInet]

The entire function looks completely wrong anyway.

The entire code had &quot written all over the place when I looked at it. I think the browser or the site hadn't rendered the quotes at all.

 

[pbaldy]

With 3 lookups I'd probably use a recordset to grab all 3 at once, but that's me.

 

[electricjelly]

With 3 lookups I'd probably use a recordset to grab all 3 at once, but that's me.

how would I go about doing this?

Sometimes I have people that want to look up information on an animal based on their release # and I wanted to make it so that they could receive a simple message with all the information they need to know about who adopted out the animal. BTW i changed the [release#] field to release ID which fixed that problem but only to receive another error of "data type mismatch"

 

[TJPoorman]

To do this with a recordset you would do this:

Private Sub Quick_Look_Click()

'On Error GoTo ErrorHandler

Dim Box As String
Dim rs As Recordset

Box = InputBox("Input cat's release number", "Release Lookup")
Set rs = CurrentDb.OpenRecordset("SELECT * FROM QryQuickLookUp WHERE [Release#]=" & Box)    'Note you will need to add single quotes if your Release# field is text

MsgBox "FIRST NAME: " & rs!First & " LAST NAME: " & rs!Last & " ADDRESS: " & rs!Address, vbOKOnly, "Quick Look UP"

GoTo Exitsub

ErrorHandler:

MsgBox "Sorry there is no record with that release #"

Exitsub:
End Sub

 

[vbaInet]

And for one lookup something like this (aircode):

    Dim arrValues As Variant
    
    Box = InputBox("Input cat's release number", "Release Lookup")
    
    arrValues = Split(DLookup("[First] & Chr(0) & [Last] & Chr(0) & [Address]", "QryQuickLookUp", "[Release#]=" & Box), vbNullChar, 3)
    
    MsgBox "FIRST NAME: " & arrValues(0) & " LAST NAME: " & arrValues(1) & " ADDRESS: " & arrValues(2), vbOKOnly, "Quick Look UP"

I'll definitely do it as pbaldy and TJ explained but this is just to show you what you can do with a DLookup in general.

By the way, use a Snapshot or Forward Only for the recordset.

 

[Galaxiom]

Or a single Dlookup could be used:

str = DLookup("'FIRST NAME: ' & [First] & ' LAST NAME: ' & [Last] & 'ADDRESS: ' & [Address]", "QryQuickLookUp", "[Release#]=" & Box)
 
Msgbox str

Many developers forget that the first argument of a DLookup is an expression, not just a fieldname.

However I would recommend you change the field names. First and Last asre SQL functions and special characters shuld never be used in any object or variable name.

 

[vbaInet]

Or a single Dlookup could be used:

str = DLookup("'FIRST NAME: ' & [First] & ' LAST NAME: ' & [Last] & 'ADDRESS: ' & [Address]", "QryQuickLookUp", "[Release#]=" & Box)
 
Msgbox str

Didn't even cross my mind to just do that.

 

[electricjelly]

I have been trying the different suggestions, however I keep receiving an error of "3075 syntax". I thought it was the formatting of my code but I cant seem to pinpoint it.

Dim Qlookup As String
    
    Box = InputBox("Input cat's release number", "Release Lookup", "380")
    
    
    Qlookup = DLookup("'FIRST NAME: ' & [First] & ' LAST NAME: ' & [Last] & 'ADDRESS: ' & [Address]", "QryQuickLookUp", "[ReleaseID]=" & Box)

    
    MsgBox Qlookup, vbOKOnly, "Adoption Info"

 

[electricjelly]

I solved it, I just had to hard code the input box with Chr(34)

 

[vbaInet]

I solved it, I just had to hard code the input box with Chr(34)

Can you show us what you did?

 

[electricjelly]

Can you show us what you did?

Sure here you go

Private Sub QuickLook_Click()

On Error GoTo ErrorHandler

Dim Qlookup As String
    
    Box = InputBox("Input cat's release number", "Release Lookup", "380")
    
    BoxFormat = Chr(34) & Box & Chr(34)
    
    Debug.Print
    
    Qlookup = DLast("'FIRST NAME: ' & [First] & ' LAST NAME: ' & [Last] & ' ADDRESS: ' & [Address]", "QryQuickLookUp", "[ReleaseID]=" & BoxFormat)
    
Debug.Print
    
    MsgBox Qlookup, vbOKOnly, "Release Info"
    
ErrorHandler:

    MsgBox "Sorry there is no record with that release number", vbOKOnly, "No release"

End Sub

 

[vbaInet]

Thanks for posting what you did.

By the way, it seems ReleaseID is a String, but the default value for your InputBox is a number, 380. What's going on there?

 

[electricjelly]

By the way, it seems ReleaseID is a String, but the default value for your InputBox is a number, 380. What's going on there?

Well sometimes the Release ID will have a letter in it, so I felt that it could either be variant or string and I remember that you are advised not to use variant as a data type

 

[vbaInet]

I was just making sure that you're using the correct data type in the table level. And there are times Variant is useful, just used sparingly.