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
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 : - 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)
- 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
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 : - 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.
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 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 : - 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)
- 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
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 32,556
Expert Mod 16PB
Very pleased to hear it James :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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.
...
|
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...
|
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...
|
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 ...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
| |