Order of Operations in a Query

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

Hello SQL experts! I thought my SQL was working, but it actually only works
when I click the ‘And’ radio button. Here is my design: DateFrom (ComboBox)
and DateTo (ComboBox); Customer (ListBox) and Trader (ListBox); finally ‘And’
(RadioButton) and ‘Or’ (RadioButton). If I click the ‘And’ button, the query
works fine FOR THE DATES. If I click the ‘Or’ button, I get only matching
records from Customer and Trader, BUT I GET ALL DATES.

I have some VBA that captures all the values and strings on my Form, passes
all to the stuff to a final string where I create this query:
' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo] And Trades.[Cust] " & strCust & _
strTraderCondition & "Trades.[Trader] " & strTrader & ";"


Actual SQL is here:
SELECT *
FROM Trades
WHERE Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo] And Trades.[Cust] Like '*' AND Trades.[Trader]
Like '*';

I think it may be a matter of adding parentheses, to get the order of
operations right, but I’m not sure. I can post all VBA code if that helps,
but I’m not sure its necessary and my just be more confusing.

Any thoughts on this?

Thanks!
Ryan---
 
SELECT *
FROM Trades
WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo]) And Trades.[Cust] Like '*' AND Trades.[Trader]
Like '*';


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Did you try putting parentheses between each condition?
I'm not sure what the point of the Like '*' conditions is?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Thanks Dorian and Jeanette. You were both right. I thought it was something
like that. Now I know!!


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Jeanette Cunningham said:
SELECT *
FROM Trades
WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo]) And Trades.[Cust] Like '*' AND Trades.[Trader]
Like '*';


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



ryguy7272 said:
Hello SQL experts! I thought my SQL was working, but it actually only
works
when I click the 'And' radio button. Here is my design: DateFrom
(ComboBox)
and DateTo (ComboBox); Customer (ListBox) and Trader (ListBox); finally
'And'
(RadioButton) and 'Or' (RadioButton). If I click the 'And' button, the
query
works fine FOR THE DATES. If I click the 'Or' button, I get only matching
records from Customer and Trader, BUT I GET ALL DATES.

I have some VBA that captures all the values and strings on my Form,
passes
all to the stuff to a final string where I create this query:
' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom]
And
[Forms]![QueryForm]![cboTo] And Trades.[Cust] " & strCust & _
strTraderCondition & "Trades.[Trader] " & strTrader & ";"


Actual SQL is here:
SELECT *
FROM Trades
WHERE Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo] And Trades.[Cust] Like '*' AND Trades.[Trader]
Like '*';

I think it may be a matter of adding parentheses, to get the order of
operations right, but I'm not sure. I can post all VBA code if that
helps,
but I'm not sure its necessary and my just be more confusing.

Any thoughts on this?

Thanks!
Ryan---
Click to expand...


.
Click to expand...
 
Back
Top