Why Doesn't This SQL Statement (VBA based) Work?

[Vaulcul]

Hello,

I have a form with 2 list boxes (1 for buildings and 1 for room numbers) and 2 text boxes (1 for low room numbers 1 for high room numbers). I'm using these to filter a form. For the most part, all of my code works, however I'm having a tricky time getting the VBA based SQL statements to work. I know the query itself works in Access, as I built it in the Query builder before copying it into the code and tweaking it to how I thought would work. Generally speaking, I've gotten some of the more simple queries to work... but this one has a bit more to it... I'm thinking the issue is with how VBA is interpreting quotation marks and such... I'm just not sure how to go about making it work:

"SELECT RM_Room_T.Room_ID, RM_Room_T.RoomNumber, RM_Room_T.Building_ID, RM_Room_T.Not_Dept " & _
"FROM RM_Room_T " & _
"WHERE (((RM_Room_T.RoomNumber) Between " & Nz(Me.RMLowFilter, "0") & " AND " & Nz(Me.RMHighFilter, "Z") & ") AND((RM_Room_T.Building_ID) Like ""*" & Nz(Me.BuildingList, "") & "*"")) " & _
"ORDER BY RM_Room_T.RoomNumber;"

Your help and pointers are very appreciated.

Thank you in advance.

[jimatqsi]

Yes, you're right, you've got some double-quote problems. I finally managed to complete eliminate that problem by doing a little, tiny bit of extra typing up front. Now I always form my SQL strings like this:

strSQL = "Select ... WHERE [strFieldName]='\fldname\' "
strsql = replace(strsql,"\fldname\",some value)

If it's a numeric field in the criteria I leave out the single quotes. Works first time, every time.

Your

Like ""*" & Nz(Me.BuildingList, "") & "*"")

should be

Like '* & Nz(Me.BuildingList, "") & "*'")

Jim

[Vaulcul]

Hi jimatqsi,

I've tried making the change that you suggested:

Like '* & Nz(Me.BuildingList, "") & "*'")

However, after doing that it stops on the last single quote with the error: "Expected:Expre ssion"

I keep changing out the quotes and I'm just not finding the right mix.

[Vaulcul]

Okay... So I got the statement to work for the most part...

But now I'm having an issue where I can't refer to the control as "me.[controlname]"

How would I do that with the below (fixed) SQL statement:

"SELECT RM_Room_T.Room_ID, RM_Room_T.RoomNumber, RM_Room_T.Building_ID, RM_Room_T.Not_Dept " & vbCrLf & _
"FROM RM_Room_T " & vbCrLf & _
"WHERE (((RM_Room_T.RoomNumber) Between Nz([Forms]![RM_Room_Filter]![RMLowFilter],""0"") And Nz([Forms]![RM_Room_Filter]![RMHighFilter],""Z"")) AND ((RM_Room_T.Building_ID) Like ""*"" & Nz([Forms]![RM_Room_Filter]![BuildingList],"""") & ""*"")) " & vbCrLf & _
"ORDER BY RM_Room_T.RoomNumber;"

BTW - I was able to figure out the SQL statement with a tool that Allen Browne makes avialable here: http://allenbrowne.com/ser-71.html

The information he has provided has saved me multiple times.

Thanks again for all of your help.

[Vaulcul]

I found my final answer here: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[NeoPa]

It's not easy to help with such a question as you haven't actually posted the SQL you're having trouble with. See Before Posting (VBA or SQL) Code.

If you follow these basic instructions for posting you'll find they also help greatly in understanding and solving such problems for yourself.

Another helpful link, embedded on the page of the other link, is How to Debug SQL String.

[Vaulcul]

Thanks NeoPa.

I'll try and keep that information (which is good to know) in mind as I post code.

Sorry for any inconvenience.

[NeoPa]

: - )