help with Where clause

[Walter]

Please tell me what's wrong with the Where clause in this SQL. This is in
the OnLoad event of a form to populate a list box. I've tried every
combination I can think of and either get a prompt for
tblOrders.RemainingQuantity , nothing displayed at all, or all records
including the ones with 0 remaining quantity.
The list box is set to 2 columns, bound column is 1, column widths is 0;1.
Thanks for your help!

With Me
.OrderDate.RowSource = _
"SELECT tblOrders.OrderID, tblOrders.Date, tblOrders.Customer "
& _
"FROM tblOrders " & _
"WHERE 'tblOrders.RemainingQuantity >' 0 " & _
"ORDER BY Date; "

 

[Dirk Goldgar]

Please tell me what's wrong with the Where clause in this SQL. This is in
the OnLoad event of a form to populate a list box. I've tried every
combination I can think of and either get a prompt for
tblOrders.RemainingQuantity , nothing displayed at all, or all records
including the ones with 0 remaining quantity.
The list box is set to 2 columns, bound column is 1, column widths is 0;1.
Thanks for your help!

With Me
.OrderDate.RowSource = _
"SELECT tblOrders.OrderID, tblOrders.Date, tblOrders.Customer "
& _
"FROM tblOrders " & _
"WHERE 'tblOrders.RemainingQuantity >' 0 " & _
"ORDER BY Date; "

Did you try:

.OrderDate.RowSource = _
"SELECT " & _
"tblOrders.OrderID, tblOrders.Date, tblOrders.Customer " & _
"FROM tblOrders " & _
"WHERE tblOrders.RemainingQuantity > 0 " & _
"ORDER BY tblOrders.Date; "

?

If you get a prompt for tblOrders.RemainingQuantity, that implies that there
is no field named "RemainingQuantity" in tblOrders. You'll want to check
the spelling on that.

I notice that your query is returning three columns, but you say your list
box has only 2. You should change one or the other.

 

[Walter]

Did you try:

.OrderDate.RowSource = _
"SELECT " & _
"tblOrders.OrderID, tblOrders.Date, tblOrders.Customer " & _
"FROM tblOrders " & _
"WHERE tblOrders.RemainingQuantity > 0 " & _
"ORDER BY tblOrders.Date; "

?

If you get a prompt for tblOrders.RemainingQuantity, that implies that there
is no field named "RemainingQuantity" in tblOrders. You'll want to check
the spelling on that.

I notice that your query is returning three columns, but you say your list
box has only 2. You should change one or the other.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I realized I had the field name wrong. It is "QuantityRemaining". I've
corrected that and removed the "customer" field as it was not needed in this
list. I have 2 list boxes with the same SQL, 1 set to display the OrderID
and the other the OrderDate. Neither is displaying any records and when I
paste the SQL into a blank query window and try to run it I get an error
message
"Invalid SQL statement; expected 'delete', 'insert', 'procedure', 'select',
or 'update'"
I've also tried creating the query in design view which returns the correct
records and copying that SQL into the code and still get nothing.
Here are the two as I now have them:

..OrderID.RowSource = _
"SELECT" & _
"tblOrders.OrderID, tblOrders.Date, QuantityRemaining" & _
"FROM tblOrders" & _
"WHERE (((tblOrders.QuantityRemaining) > 0))" & _
"ORDER BY Date;"

.OrderDate.RowSource = _
"SELECT tblOrders.OrderID, tblOrders.Date, QuantityRemaining" & _
"FROM tblOrders " & _
"WHERE (tblOrders.QuantityRemaining) > 0))" & _
"ORDER BY Date;"

Thanks again for your help!
Walter

 

[Dirk Goldgar]

I realized I had the field name wrong. It is "QuantityRemaining". I've
corrected that and removed the "customer" field as it was not needed in
this
list. I have 2 list boxes with the same SQL, 1 set to display the OrderID
and the other the OrderDate. Neither is displaying any records and when I
paste the SQL into a blank query window and try to run it I get an error
message
"Invalid SQL statement; expected 'delete', 'insert', 'procedure',
'select',
or 'update'"

I'm not sure what happened there, since I can't see exactly what you put in
the query window. I suspect that you didn't correctly transform what I
wrote from its VBA form to a straight SQL form, removing all quotes,
concatenation operators, and continuation characters.

I've also tried creating the query in design view which returns the
correct
records and copying that SQL into the code and still get nothing.
Here are the two as I now have them:

.OrderID.RowSource = _
"SELECT" & _
"tblOrders.OrderID, tblOrders.Date, QuantityRemaining" & _
"FROM tblOrders" & _
"WHERE (((tblOrders.QuantityRemaining) > 0))" & _
"ORDER BY Date;"

.OrderDate.RowSource = _
"SELECT tblOrders.OrderID, tblOrders.Date, QuantityRemaining" &
_
"FROM tblOrders " & _
"WHERE (tblOrders.QuantityRemaining) > 0))" & _
"ORDER BY Date;"

From the looks of it, you're missing some necessary spaces between keywords,
and you need to clarify that you want to order by the (badly-named) "Date"
field, not the VBA Date function. Try this:

.OrderID.RowSource = _
"SELECT " & _
"tblOrders.OrderID, tblOrders.Date, QuantityRemaining " & _
"FROM tblOrders " & _
"WHERE (((tblOrders.QuantityRemaining) > 0)) " & _
"ORDER BY tblOrders.Date;"

.OrderDate.RowSource = _
"SELECT tblOrders.OrderID, tblOrders.Date, QuantityRemaining " & _
"FROM tblOrders " & _
"WHERE (tblOrders.QuantityRemaining) > 0)) " & _
"ORDER BY tblOrders.Date;"

Note that, on the continued lines of the SQL strings, there's a space before
the close of the quoted literal.

 

[Walter]

I guess the problem was with the "Date" field. I originally had spaces
before the quotes and I was trying all possibilities I could think of.
Anyway, I changed the "Order By" line to tblOrders.Date as you suggested and
that solved the problem.
I'll probably go back and change that to OrderDate as it should be. Thanks
for calling that to my attention.
Walter

 

[Walter]

With those two List boxes working, is it possible to have other list boxes to
display the other fields of the table where the OrderID matches the OrderID
of the first or second List box? I believe I could do away with the OrderID
list box since I'm not interested in the OrderID only the OrderDate and the
other corresponding information. Since the OrderID is the bound column in
the OrderDate list, I should be able to limit the other lists to the orders
where the OrderID matches. Is this correct? If so, I need some advice as
how to write this Where clause. Here is what I've tried for 2 of the other
lists. They are showing all records in order by the OrderID and not limiting
or matching the other lists.
..Customer.RowSource = _
"SELECT tblOrders.OrderID, tblCustomers.CustomerName " & _
"FROM tblCustomers INNER JOIN tblOrders ON
[tblCustomers].[CustomerID]=[tblOrders].[Customer] " & _
"WHERE 'tblOrders.OrderID =' &
[forms]![frmOutstandingLimeOrders]![OrderID];"


.PONumber.RowSource = _
"SELECT tblOrders.OrderID, tblOrders.PONumber " & _
"FROM tblOrders " & _
"WHERE 'tblOrders.OrderID =' &
[forms]![frmOutstandingLimeOrders]![OrderDate];"
Walter

 

[Walter]

After sleeping on it I realized I could include all the fields in multiple
columns in one list box.
Thanks again!
Walter

With those two List boxes working, is it possible to have other list boxes to
display the other fields of the table where the OrderID matches the OrderID
of the first or second List box? I believe I could do away with the OrderID
list box since I'm not interested in the OrderID only the OrderDate and the
other corresponding information. Since the OrderID is the bound column in
the OrderDate list, I should be able to limit the other lists to the orders
where the OrderID matches. Is this correct? If so, I need some advice as
how to write this Where clause. Here is what I've tried for 2 of the other
lists. They are showing all records in order by the OrderID and not limiting
or matching the other lists.
.Customer.RowSource = _
"SELECT tblOrders.OrderID, tblCustomers.CustomerName " & _
"FROM tblCustomers INNER JOIN tblOrders ON
[tblCustomers].[CustomerID]=[tblOrders].[Customer] " & _
"WHERE 'tblOrders.OrderID =' &
[forms]![frmOutstandingLimeOrders]![OrderID];"


.PONumber.RowSource = _
"SELECT tblOrders.OrderID, tblOrders.PONumber " & _
"FROM tblOrders " & _
"WHERE 'tblOrders.OrderID =' &
[forms]![frmOutstandingLimeOrders]![OrderDate];"
Walter

I'm not sure what happened there, since I can't see exactly what you put in
the query window. I suspect that you didn't correctly transform what I
wrote from its VBA form to a straight SQL form, removing all quotes,
concatenation operators, and continuation characters.


From the looks of it, you're missing some necessary spaces between keywords,
and you need to clarify that you want to order by the (badly-named) "Date"
field, not the VBA Date function. Try this:

.OrderID.RowSource = _
"SELECT " & _
"tblOrders.OrderID, tblOrders.Date, QuantityRemaining " & _
"FROM tblOrders " & _
"WHERE (((tblOrders.QuantityRemaining) > 0)) " & _
"ORDER BY tblOrders.Date;"

.OrderDate.RowSource = _
"SELECT tblOrders.OrderID, tblOrders.Date, QuantityRemaining " & _
"FROM tblOrders " & _
"WHERE (tblOrders.QuantityRemaining) > 0)) " & _
"ORDER BY tblOrders.Date;"

Note that, on the continued lines of the SQL strings, there's a space before
the close of the quoted literal.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)