469,588 Members | 2,848 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

Query result - how to filter by record set

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
2 1333
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
12,516 Expert Mod 8TB
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.

Similar topics

1 post views Thread by shilsum | last post: by
5 posts views Thread by Tim::.. | last post: by
reply views Thread by JC Voon | last post: by
1 post views Thread by starke1120 | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.