Syntax error missing operator in query expression

[neobrainless]

Ok, so I'm by no means good at VBA, and am tryign to make use of some code found on thei nterweb which I reckon does exactly what I want. My problem is that now I've moved the code into my database and changed what I thought were all the appropriate parts and removed the bits I don't want it is broken (of course!).

So the code the debugger points to is:

    sqlinput = "SELECT * FROM FileInfQry " & BuildFilter
    
    Debug.Print sqlinput
    
    Me.FileInfSubForm.Form.RecordSource = sqlinput

(I've added the debug section, and made the sqlinput variable as part of tryign to get it to work)

The expression quoted as wrong is '[ConsNo] a999x' which is the product of:

    ' Check for Concession Number
    If Me.txtConcessionNo > "" Then
        varWhere = varWhere & "[ConcessionNo] " & Me.txtConcessionNo & " And "
    End If

If anyone can help it would be greatly appreciated - my progress has been pretty glacial today!

[Seth Schrock]

In your second code group shown, add an equals sign after the [ConcessionNo], but before the ending double quote.

' Check for Concession Number
    If Me.txtConcessionNo > "" Then
        varWhere = varWhere & "[ConcessionNo] = " & Me.txtConcessionNo & " And "
    End If

[neobrainless]

Hi Seth,
Thanks for the reply, that has stopped the code error'ing which is a massive relief!
However, now I get a pop-up box asking for the search term - the code originally just used the value input ("a999x" for example) AS the search term, now I get a popup with "a999x" and a data entry box I have to put it in AGAIN for the search to work? I've clearly deleted or added soemthing weird somewhere :/

Am I going to need to post more info from the programme?

Cheers, Rohan

[Seth Schrock]

Ah, so ConcessionNo is a text field and not a number field. Then you will need to add some single quotes inside your double quotes like this:

varWhere = varWhere & "[ConcessionNo] = '" & Me.txtConcessionNo & "' And "

Just curious, do you add anything onto the end of this? I ask because as I see it, the SQL code will get left with an AND at the end and I don't see anything that would put additional criteria after it, nor take it away.

Also, if this doesn't work, then I would like to see the output from your Debug.Print to make sure that it is ending up correctly.

[neobrainless]

Hi Seth,
Sorry, I probably should have known it was an input value rather than text box content - this is the downside of using other people's code!

The extra ANDs are presumably surplus to requirements from the other code - I removed things I didn't think I'd need, but clearly didn't remove enough (as well as too much!)...

Having just gone through the various search fields to test I haven't broken anyhting else I have discovered a problem - I can't search for a date! I'm guessing the '/' in the date format are the issue - but there must be a way around that? I have formatted them 'dd/mm/yyyy'.

Thanks for the help, you're a star! :)

Cheers, Rohan

[Seth Schrock]

No problem. While new questions do need to be asked in another thread, I will try to point you in the right direction. Dates need to be surrounded in pound signs (#). If this doesn't provide enough information, start a new thread and post the SQL that is giving you problems as well as what exactly you are trying to do and someone will be able to help you out.