Seach function in Access syntax error: missing a comma in query expression

Question

I have a problem while I'm working on a search function at Windows Access that allowed me to search after 3 things. In this case, it's first name, Last name and CPR. Now the CPR means it's a serial of unique numbers to each users on my Database.

It started as a macro then I changed it into VBA code because I want it to have the option to search after 3.

when it's in macro, it can only find 2 things because the limit for write characters in macro is 255, which is the reason that I changed it to VBA code which the characters limit should be unlimited.

Ah, I almost forgot, the code's in danish, but I think the basic functions of the code is almost pretty much same everywhere so...

Here is the problem:

On Error GoTo SøgEfterFornavn_Err

    If (IsNull(Forms!Navigationsformular!NavigationUnderformular!SearchFor)) Then
        Beep
        Exit Function
    Else
        DoCmd.SetFilter "Søgnings forespørgelse", "[Fornavn] Like ""*"" & [Forms]![Navigationsformular]![NavigationUnderformular]![SearchFor] & ""*"" Or [CPR] Like ""*"" & [Forms]![Navigationsformular]![NavigationUnderformular]![SearchFor] & ""*"" Or [Efternavn] Like ""*"" & [Formularer]![Navigationsformular]![NavigationUnderformular]![SearchFor] & ""*"", """"
        Exit Function
    End If


SøgEfterFornavn_Exit:
    Exit Function

SøgEfterFornavn_Err:
    MsgBox Error$
    Resume SøgEfterFornavn_Exit

It keeps saying that "Syntax error: missing a comma in query expression" which I don't understand? Did I do something wrong or?

You can make your life a lot easier by using single quotes for SQL that you create in VBA: "[Fornavn] Like '*" & [Forms]![Navigationsformular]![NavigationUnderformular]![SearchFor] & "*' Or [CPR] Like '*" & [Forms]!... -- No more double and triple quotes. — Andre
– Andre, Commented Sep 30, 2015 at 13:18

agold · Accepted Answer · 2015-09-30 12:21:00Z

1

You have forgotten to put some quote signs ("):

DoCmd.SetFilter "Søgnings forespørgelse", "[Fornavn] Like ""*""" & [Forms]![Navigationsformular]![NavigationUnderformular]![SearchFor] & """*"" Or [CPR] Like ""*""" & [Forms]![Navigationsformular]![NavigationUnderformular]![SearchFor] & """*"" Or [Efternavn] Like ""*""" & [Formularer]![Navigationsformular]![NavigationUnderformular]![SearchFor] & ""*""", """"

You probably forgot them because you need to put the quote character twice to have it in the string (as escape character).

edited Sep 30, 2015 at 12:21

answered Sep 30, 2015 at 12:15

agold

6,3069 gold badges41 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

DRapp Over a year ago

Just to clarify... the missing " was at the end of the last ""*"" section. Otherwise without knowing they might miss it as it looks like a blur anyhow :)

agold Over a year ago

Actually they were missing after and before the others (""*"") also.

Danishdjinn Over a year ago

ah, now it's sort of working. There're no more syntax error, but "Compile error: External name not defined" showed up instead. Hmm... any idea?

agold Over a year ago

To which name does it refer to? Have you checked all the column, form, and field names?

Andre Over a year ago

It's Forms!Mainform!Subform1.Form!ControlName i.e. [Forms]![Navigationsformular]![NavigationUnderformular].Form![SearchFor]. Here is a good reference: access.mvps.org/access/forms/frm0031.htm

|

Collectives™ on Stack Overflow

Seach function in Access syntax error: missing a comma in query expression

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related