SQL WHERE Query

[James Bowyer]

I am using SQL to populate a Listbox's rowsource. I need the WHERE component to reference a text box on the form that the query is on. However, I can't figure out the parenthesis needed to do this!

The WHERE function currently is:

WHERE (((BayDetails.Side)='Left') AND ((BayDetails.JobNumber)='" & Me.JobNumber & "'))

But this doesn't work. It doesn't show any errors, but the query always returns no results. I have tried various parenthesis, and none of them seem to work.

Help!

Thanks

[NeoPa]

James, for some reason you've posted only part of a line of code. I think I can work from there, but it does leave me having to explain assumptions that are needed due to the lack of completeness of the question. Please bear this in mind for future posts.

Assuming then, that the WHERE component is part of a longer piece of code which assigns the SQL code for the .RowSource property of your ListBox to a string (probably a string variable), we would need some code to handle adding the WHERE clause to that string :

strSQL = ...
strSQL = strSQL & " WHERE (BayDetails.Side='Left') " & _
                     "AND (BayDetails.JobNumber='" & Me.JobNumber & "')"

I would assume from the fact that you report this doesn't work, that the JobNumber field is numeric. In that case the single-quotes (') used around the literal value are not required (Explicitly. They must not be there. See Quotes (') and Double-Quotes (") - Where and When to use them). Try the following instead and report if it works. If that isn't your problem, then you need to give a much better explanation of what you are working with. Details are not a luxury if you want someone to help you.

[James Bowyer]

Apologies, the full code is:

SELECT BayDetails.Department, BayDetails.ShelfHeights, BayDetails.Variable1, BayDetails.Variable2, BayDetails.Variable3, BayDetails.BayWidth, BayDetails.BayNumber, BayDetails.Height, BayDetails.BaseDepth
FROM BayDetails
WHERE (((BayDetails.Side)='Left')AND ((BayDetails.JobNumber)='" & Me.JobNumber & "'))   
ORDER BY BayDetails.BayNumber DESC;

and is the Rowsource property of a listbox, not a vba code. Also, the Job Number Field is a text box, and is definately not Numeric (for example, the Job Number for the example job I am using is G001)

[NeoPa]

OK James. This is a bigger problem than I thought. You have VBA code references in your SQL string. Not good. The SQL engine will see exactly what we see and will filter on the string value " & Me.JobNumber & ". Notice, the double-quotes are included in this string value and none of this is a reference. It's the exact string value.

To get this to work we need to do one of two things :

1. Include an explicit reference in the .RowSource to the actual control itself. In this case the value wouldn't be a string literal, but a string reference, so the quotes wouldn't be required.
   

   WHERE ([Side]='Left')
     AND ([JobNumber]=Forms!YourForm.JobNumber)

2. Build the .RowSource up into a string using VBA code and apply it in the AfterUpdate event procedure of the control. In this case the control value would be a string literal so your usage of the single-quotes would be spot on.
   

   Private Sub JobNumber_AfterUpdate
       Dim strSQL As String
   
       strSQL = "SELECT   [Department], [ShelfHeights], [Variable1], [Variable2], " & _
                         "[Variable3], [BayWidth], [BayNumber], [Height], [BaseDepth] " & _
                "FROM     [BayDetails] " & _
                "WHERE    ([Side]='Left') " & 
                  "AND    ([JobNumber]='" & Me.JobNumber & "')" & _
                "ORDER BY [BayNumber] DESC"
       Me.YourListBox.RowSource = strSQL
   End Sub

[James Bowyer]

Brilliant, tried the first one, and it Worked!

I am more used to using SQL within VBA, hence why I had written it as I had, I knew there must be a way of doing it in normal SQL.

Thanks Very much.

[NeoPa]

Very pleased to hear it James :-)