Access Search Seminar

Access Search Seminar
By Richard Rost

10 years ago

Search with Combo Box Upload Images Link
Ina van Rooyen
10 months ago

Hi Richard
I have three combo boxes on a continuous form with approximately 600 records in total. I use the exact same code you used in the Genealogy seminar with different field names, obviously.
All three combo boxes get their values from a table.
When I select an option from the combo box I get a Run-time error '3138': Syntax error in ORDER BY clause.
This line is yellow when I press debug.
    Me.RecordSource = SQL

This is my code:

Private Sub RequeryForm()

    Dim SQL As String, WhereStr As String, OrderBy As String

    SQL = "SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour " & _
        "FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) INNER JOIN ColoursT " & _
            "ON DogT.ColourID = ColoursT.ColourID " & Wh


    ' WHERE -----------------------------------------------
    WhereStr = ""

    If CallNameSearch <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "CallName LIKE ""*" & CallNameSearch & "*"" "
    End If

    If Not IsNull(GenderCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Gender = " & GenderCombo & " "
    End If

    If Not IsNull(StatusCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Colour = " & ColourCombo & " "
    End If

    If WhereStr <> "" Then
        WhereStr = "WHERE " & WhereStr
    End If

    SQL = SQL & WhereStr

    ' ORDER BY -----------------------------------------------
    Select Case SortCombo
        Case 1: OrderBy = "Status, Gender, CallName"
        Case 2: OrderBy = "Gender, CallName"
        Case 3: OrderBy = "BirthDate DESC, Gender, CallName"
        Case 4: OrderBy = "CallName"
        Case 5: OrderBy = "MChip ASC"
    End Select

    SQL = SQL & "ORDER BY " & OrderBy

    Me.RecordSource = SQL

End Sub

Richard Rost
10 months ago

Your code looks solid. Usually this problem is because people forget the space before "ORDER BY" but you've got that covered at the end of the WhereStr. Only time you'd have a problem would be if there was no WhereStr. So you might want to add a space in there. Let's see what your final SQL statement looks like. MsgBox it and post a screen shot.

Kevin Robertson
10 months ago

What is the "& wh" at the end of sql?
Your WhereStr appears to go straight after with no space.

Richard Rost
10 months ago

Good catch. As the code is written, it would evaluate to an empty string, unless it's a form field. Probably shouldn't be there, but I don't see that causing the problem. That's why I'd like to see the final SQL as compiled.

Alex Hedley
10 months ago

Debug.Print SQL

Ina van Rooyen
10 months ago

Thank you for the quick response, guys. My WhereStr appears to have spaces except where it appears at the end of a line. I've put spaces in there but the space disappears again. Not quite sure what you mean by 'WhereStr appears to go straight after with no space'.
I'm not quite sure which SQL Statement you want to see and how do I run it. My Record Source in my form is a Query and this is the SQL Statement
SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour
FROM (DogT LEFT JOIN GenderT ON DogT.GenderID = GenderT.GenderID) LEFT JOIN ColoursT ON DogT.ColourID = ColoursT.ColourID
WHERE (((DogT.DogID)<988) AND ((DogT.Status)="Breeding"))
ORDER BY DogT.CallName;
I'm also not sure what Alex means with 'Debug.Print SQL'

Ina van Rooyen
10 months ago

Scott Axton
10 months ago

All -
Fresh eyes here.  I think Kevin Robertson was on to something.  It appears to me that you are mixing up 2 different   code variations.
In the code in the first message you have:
"ON DogT.ColourID = ColoursT.ColourID " & Wh (last line of the SQL statement)

You did your dim as WhereStr As String, but used Wh in the SQL statement.
In the screen captures I see Wh As String.  Which ever you decide to use they all need to be the same - Either WH or WhereStr.

I'm also not sure what Alex means with 'Debug.Print SQL'
What Alex was telling you was to use the Immediate window to display the SQL statement after it is built.
Comment out "Me.RecordSource = SQL" and put the "Debug.Print SQL" above it.  When you run the code it will put the full SQL statement in the immediate Window that you should be able to copy and paste here so we can look at it.

Scott Axton
10 months ago

Access Developer 15 Lesson 2 covers Debugging more in depth including the use if Immediate window & Debug.Print as well as the Watch Window.

Richard Rost
10 months ago

Is it just me or do you have an ORDER BY at the end of the line all by itself? You need to put a field after it. ORDER BY FirstName, for example.

Debug.Print just prints a string to the Immediate Window instead of using MsgBox or my StatusBox thing from the Blank template.

Richard Rost
10 months ago

Yeah, it's possible for your SortCombo to have no value. Make sure you set a Case Else with a default ORDER BY value.

Ina van Rooyen
10 months ago

Hi Guys
This is my SQL Statement. According to me, it looks fine

SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) INNER JOIN ColoursT ON DogT.ColourID = ColoursT.ColourID WHERE Gender = -1 ORDER BY

I might just say, my CallNameSearch is working, so does the Select Case SortCombo.

I'm posting a copy of my code again in a different Comment

Ina van Rooyen
10 months ago

Private Sub RequeryForm()

    Dim SQL As String, WhereStr As String, OrderBy As String

    SQL = "SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour " & _
        "FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) INNER JOIN ColoursT " & _
            "ON DogT.ColourID = ColoursT.ColourID " & WhereStr

      WhereStr = ""

    If CallNameSearch <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "CallName LIKE ""*" & CallNameSearch & "*"" "
    End If

    If Not IsNull(GenderCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Gender = " & GenderCombo & " "
    End If

    If Not IsNull(StatusCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Status = " & StatusCombo & " "

Ina van Rooyen
10 months ago

End If
    If Not IsNull(ColourCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Colour = " & ColourCombo & " "
    End If
    If WhereStr <> "" Then
        WhereStr = "WHERE " & WhereStr
    End If

    SQL = SQL & WhereStr

    ' ORDER BY -----------------------------------------------
    Select Case SortCombo
        Case 1: OrderBy = "Status, Gender, CallName"
        Case 2: OrderBy = "Gender, CallName"
        Case 3: OrderBy = "BirthDate DESC, Gender, CallName"
        Case 4: OrderBy = "CallName"
        Case 5: OrderBy = "MChip ASC"
    End Select

    SQL = SQL & "ORDER BY " & OrderBy

    Me.RecordSource = SQL

End Sub

Alex Hedley
10 months ago

You could try creating a new Query
Change to SQL View
Paste it in
Run to see if there are any errors

Scott Axton
10 months ago

Grasping at straws here, I can't recall which video it was in, but Richard was doing lots of editing, saving, running the query with both the form open and the code window open. I also forget the exact scenario of what caused it but the "bad" Order By ended up being "hard coded" by Access into the form properties Order By.

You might check that in your form just to make sure it hasn't happened to you.

Scott Axton
10 months ago

Richard Rost
10 months ago

You've got nothing after the ORDER BY in the final SQL you posted above. That's no bueno. Gotta have something there or remove the ORDER BY altogether.

Ina van Rooyen
10 months ago

Richard, I totally agree with you. It doesn't make a difference if I remove it. I will remove it though.
I have it there because I've tried to mimic your code from the Genealogy seminar LOL.

Kevin Robertson
10 months ago

How is your ComboBox set up? Is the bound column numeric or text?

Ina van Rooyen
10 months ago

The bound column is numeric.

Ina van Rooyen
10 months ago

I'm very confused. I've just run the Genealogy database and the Gender Combo runs exactly how it should. All my code is exactly what it is there. I then copied the Gender Combo and rename it with an after update event running the exact same procedure and I get two different results. The second combobox doesn't sort because there's not a Where clause in the SQL. However, I don't get the Run Time Error 🤔😏
Original GenderCombo:
SELECT * FROM PersonListQ WHERE Gender = -1 ORDER BY LastName, FirstName;
The GenderCombo copy with a different name (it doesn't pick up the Where Clause):
SELECT * FROM PersonListQ ORDER BY LastName, FirstName;

Ina van Rooyen
10 months ago

I did a fresh SQL statement and I moved the SQL statement inside the Select Case Statement. The Select Case Statement needs the "ORDER BY "
I'm now one step further, the Run Time Error doesn't pop up anymore.
The problem I have is that the Where Clause doesn't end up showing in the SQL Statement.
Looks like a problem somewhere in my WhereStr ??

Select Case SortCombo
        Case 1: OrderBy = "Status, Gender, CallName"
        Case 2: OrderBy = "Gender, CallName"
        Case 3: OrderBy = "BirthDate DESC, Gender, CallName"
        Case 4: OrderBy = "CallName"
        Case 5: OrderBy = "MChip ASC"
    SQL = SQL & "ORDER BY " & OrderBy
    End Select

    'SQL = SQL & "ORDER BY " & OrderBy

    Debug.Print SQL
    Me.RecordSource = SQL

SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) INNER JOIN ColoursT ON DogT.ColourID = ColoursT.ColourID

Richard Rost
10 months ago

I don't see your WhereStr there anywhere. Get it? AnyWHERE? LOL

Scott Axton
10 months ago

Oooo... I think Kevin may be on to it.
If you place the actual text at the end of the SQL instead of the results of the SortCombo does the SQL run correctly?
so
SQL = SQL & "ORDER BY " & CallName

if the above works, try placing a stop on the Select statement and step through. I'm betting rather than 1 the case is returning "1" etc.

Also - You are checking for Null everywhere else but the SortCombo
I would add a Case Else to the select as a fall through.

Case else: OrderBy = "YourID"

Ina van Rooyen
10 months ago

I might just add that the WhereStr works with the CallName Search

WhereStr = ""

    If CallNameSearch <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "CallName LIKE ""*" & CallNameSearch & "*"" "
    End If

    If Not IsNull(GenderCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Gender = " & GenderCombo & " "
    End If
     If WhereStr <> "" Then
        WhereStr = "WHERE " & WhereStr
    End If

    SQL = SQL & WhereStr

SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) INNER JOIN ColoursT ON DogT.ColourID = ColoursT.ColourID WHERE CallName LIKE "*lil*"

Ina van Rooyen
10 months ago

Richard, that was just part of the code. This is it all:
Private Sub RequeryForm()

    Dim SQL As String, WhereStr As String, OrderBy As String

    DoCmd.SetWarnings True

    SQL = "SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, " & _
        "GenderT.Gender, DogT.Status, ColoursT.Colour " & _
        "FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) " & _
        "INNER JOIN ColoursT ON DogT.ColourID = ColoursT.ColourID " & WhereStr

    DoCmd.SetWarnings False

    ' WHERE -----------------------------------------------
    WhereStr = ""

    If CallNameSearch <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "CallName LIKE ""*" & CallNameSearch & "*"" "
    End If

    If Not IsNull(GenderCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Gender = " & GenderCombo & " "
    End If

Ina van Rooyen
10 months ago

If WhereStr <> "" Then
        WhereStr = "WHERE " & WhereStr
    End If

    SQL = SQL & WhereStr


    ' ORDER BY -----------------------------------------------
    Select Case SortCombo
        Case 1: OrderBy = "Status, Gender, CallName"
        Case 2: OrderBy = "Gender, CallName"
        Case 3: OrderBy = "BirthDate DESC, Gender, CallName"
        Case 4: OrderBy = "CallName"
        Case 5: OrderBy = "MChip ASC"
    SQL = SQL & "ORDER BY " & OrderBy
    End Select

    'SQL = SQL & "ORDER BY " & OrderBy

    Debug.Print SQL
    Me.RecordSource = SQL

End Sub

Kevin Robertson
10 months ago

The ComboBoxes that filter your results need to be a single column (No ID) They will return a string value so need to be enclosed in double quotes. I built a quick DB just to test it would work. Below is the modified code:

Kevin Robertson
10 months ago

Ina van Rooyen
10 months ago

Thank you, Kevin, it worked but it resulted in my SortCombo not working the way it should.
May I ask why the Gender selection won't work if it's selected by the gender ID, the same with the StatusCombo and the ColourCombo, which get its values from different tables.
I figured that the WhereStr variable is defined as a string and those comboboxes display numeric values which are the id fields in the tables.
I tried: Dim WhereStr As Integer

And replaced the
WhereStr = ""
with
WhereStr = Null

It doesn't like the "Null" - what should it be then?

My code is based on the code Richard use in the Genealogy Seminar in the PersonListF.

Kevin Robertson
10 months ago

Maybe we set our forms up differently. This is how I set mine up. Let me know if yours is different.

Kevin Robertson
10 months ago

Ina van Rooyen
10 months ago

Mine looks different but I can do it your way. My issue is that I don't want to create text values again just for the purpose of searching. I already have those values set up in separate tables, eg. GenderT, StatusT and ColourT. My RowSource for my DogF form is a Query that gets data from the DogT, GenderT, StatusT and ColourT. That is how I differentiate the dogs. I use that Query on other forms as well.
So your Sort By ComboBox gives you the option of sorting by any of those fields?
And your Filters ComboBoxex are on each individual field?

Ina van Rooyen
10 months ago

This is my form

Ina van Rooyen
10 months ago

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Access Search Seminar Comments