By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,602 Members | 1,072 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,602 IT Pros & Developers. It's quick & easy.

Query result - how to filter by record set

P: 6
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?
Mar 29 '07 #1
Share this Question
Share on Google+
2 Replies

P: 6
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?
Mar 29 '07 #2

Expert Mod 10K+
P: 12,441
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"
Mar 29 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.