Re: help with Where clause



"Dirk Goldgar" wrote:

"Walter" <Walter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:682FDE79-3221-46F2-9105-316E5C6C1E3B@xxxxxxxxxxxxxxxx
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.

--
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


.

Relevant Pages

  • Re: Dynamic query problem
    ... Imagine you want to desing a query. ... to see the SQL. ... comment out the report and comment in the msgbox. ... I'll assume outcome 1 - no prompt so is ok ...
    (microsoft.public.access.queries)
  • Re: Dynamic query problem
    ... You've included all fields from tblBasicData and tblComments - good. ... Imagine you want to desing a query. ... to see the SQL. ... I'll assume outcome 1 - no prompt so is ok ...
    (microsoft.public.access.queries)
  • Re: Slow Query when Prompts enabled
    ... That is why I suggest you create a pass-through query to your database ... server and change the SQL property as needed. ... I set just the query to prompt me for 1 thing, ...
    (microsoft.public.access.queries)
  • Re: Slow Query when Prompts enabled
    ... pass-through query. ... I create a function in most of my mdbs to change the sql property of saved ... I set just the query to prompt me for 1 thing, ...
    (microsoft.public.access.queries)
  • Re: SQL Not Updateable
    ... Dirk Goldgar wrote: ... I'm thinking that it's my syntax. ... Is HoldSection a table or a query? ... but I do notice that there's nothing in the SQL ...
    (microsoft.public.access.formscoding)