Query result - how to filter by record set

[Gareith Stanley]

Hi again :)

I have a form that user enters parameters into which then pops up another form with results filtered by the chosen parameter (field in table)

What I'd like to do is order the results from the query it fires by a record set of my choosing. I think I'm on the right track by trying to edit the sql that is passed to the query but it doesn't seem to work.....

Example from form that runs the query filtering by 'section/faculty' record:

If [Section/Faculty] <> "" Then
If Counter <> 0 Then
b = "AND (([Car Records].[Section/Faculty]) = [Forms]![Search Facility]![Section/Faculty])"
Else: b = "FROM [Car Records] WHERE ((([Car Records].[Section/Faculty]) = [Forms]![Search Facility]![Section/Faculty])"
End If
Counter = Counter + 1
Else: b = ""
End If


I thought I could add:

ORDER BY [Car Records].[FullName]

to the end of the sql query but i'm getting something wrong - it tends to delete the query and I get a missing operator message after it runs??

Am I close or on the wrong track please?

[Gareith Stanley]

I know why the query is getting deleted and maybe I'm totally in the wrong area.

This is where the actual query is updated each time:

Set qdf = dbs.CreateQueryDef("Results", "SELECT [Car Records].FullName, [Car Records].FName,[Car Records].SName, [Car Records].[Section/Faculty], [Car Records].[ID Number], [Car Records].[Vehicle Make], [Car Records].[Vehicle Model], [Car Records].[Vehicle Colour],[Car Records].[Licence Plate Number], [Car Records].[Notes], [Car Records].[Parking Record], [Car Records].[Application Received]" & a & b & c & d & e & h & f & g "ORDER BY [Car Records].[FullName]")

Maybe I am meant to order the results at this point (as shown) but my syntax is wrong isn't it?

[Rabbit]

Well, at first glance and without more information, it looks to me you are missing spaces at the beginning of your string concatenations.

"Bob" & "Bill" = "BobBill"
Not "Bob Bill"