473,324 Members | 2,268 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQL WHERE Query

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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. WHERE (((BayDetails.Side)='Left') AND ((BayDetails.JobNumber)='" & Me.JobNumber & "'))
  3.  
  4.  
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
Aug 18 '11 #1

✓ answered by 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.
    Expand|Select|Wrap|Line Numbers
    1. WHERE ([Side]='Left')
    2.   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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub JobNumber_AfterUpdate
    2.     Dim strSQL As String
    3.  
    4.     strSQL = "SELECT   [Department], [ShelfHeights], [Variable1], [Variable2], " & _
    5.                       "[Variable3], [BayWidth], [BayNumber], [Height], [BaseDepth] " & _
    6.              "FROM     [BayDetails] " & _
    7.              "WHERE    ([Side]='Left') " & 
    8.                "AND    ([JobNumber]='" & Me.JobNumber & "')" & _
    9.              "ORDER BY [BayNumber] DESC"
    10.     Me.YourListBox.RowSource = strSQL
    11. End Sub

5 2008
NeoPa
32,556 Expert Mod 16PB
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 :

Expand|Select|Wrap|Line Numbers
  1. strSQL = ...
  2. strSQL = strSQL & " WHERE (BayDetails.Side='Left') " & _
  3.                      "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.
Aug 18 '11 #2
Apologies, the full code is:

Expand|Select|Wrap|Line Numbers
  1. SELECT BayDetails.Department, BayDetails.ShelfHeights, BayDetails.Variable1, BayDetails.Variable2, BayDetails.Variable3, BayDetails.BayWidth, BayDetails.BayNumber, BayDetails.Height, BayDetails.BaseDepth
  2. FROM BayDetails
  3. WHERE (((BayDetails.Side)='Left')AND ((BayDetails.JobNumber)='" & Me.JobNumber & "'))   
  4. ORDER BY BayDetails.BayNumber DESC;
  5.  
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)
Aug 18 '11 #3
NeoPa
32,556 Expert Mod 16PB
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.
    Expand|Select|Wrap|Line Numbers
    1. WHERE ([Side]='Left')
    2.   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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub JobNumber_AfterUpdate
    2.     Dim strSQL As String
    3.  
    4.     strSQL = "SELECT   [Department], [ShelfHeights], [Variable1], [Variable2], " & _
    5.                       "[Variable3], [BayWidth], [BayNumber], [Height], [BaseDepth] " & _
    6.              "FROM     [BayDetails] " & _
    7.              "WHERE    ([Side]='Left') " & 
    8.                "AND    ([JobNumber]='" & Me.JobNumber & "')" & _
    9.              "ORDER BY [BayNumber] DESC"
    10.     Me.YourListBox.RowSource = strSQL
    11. End Sub
Aug 18 '11 #4
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.
Aug 18 '11 #5
NeoPa
32,556 Expert Mod 16PB
Very pleased to hear it James :-)
Aug 18 '11 #6

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

Similar topics

2
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
3
by: Harvey | last post by:
Hi, I try to write an asp query form that lets client search any text-string and display all pages in my web server that contain the text. I have IIS 6.0 on a server 2003. The MSDN site says...
14
by: Dave Thomas | last post by:
If I have a table set up like this: Name | VARCHAR Email | VARCHAR Age | TINYINT | NULL (Default: NULL) And I want the user to enter his or her name, email, and age - but AGE is optional. ...
0
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list...
15
by: Richard Hollenbeck | last post by:
I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the question: I have a crosstab query with rows...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
3
by: Bill Hutchison | last post by:
I have a query that returns different results (3508 rows for snapshot, 6288 for dynaset) and that is the only thing I change to get the different results. When I try to make a table from the...
3
by: pbd22 | last post by:
Hi. I need some help with structuring my query strings. I have a form with a search bar and some links. Each link is a search type (such as "community"). The HREF for the link's anchor looks...
4
by: Stan | last post by:
I am using MS Office Access 2003 (11.5614). My basic question is can I run a query of a query datasheet. I want to use more that one criteria and can not get that query to work. I thought I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.