[RESOLVED] SQL Command Locks Machine

[George1111]

I am trying to do the following (but the machine just locks up)

strSQL = "Select [O].[RecID], [O].[TransactionSource], [O].[AccountNumber], [S].[FullName], [O].[TransactionType], "
strSQL = strSQL & " [O].[TransactionDate], [O].[ReferenceNumber], [O].[Description], [O].[TransactionMemo], "
strSQL = strSQL & " [O].[TransactionAmountDR], [O].[TransactionAmountCR], "
strSQL = strSQL & " [O].[CreatedByOpID], [O].[DataWarehouseUpdated], [O].[TransactionAllocation] "
strSQL = strSQL & " from ITM_Transactions [O], Members [S] Where [O.AccountNumber] = [S.CardNumber] "
strSQL = strSQL & " and O.TransactionType = 51 OR O.TransactionType = 65 "
strSQL = strSQL & "and O.TransactionDate Between #" & ChkDateFrom & "# and #" & ChkDateTo & "# "
strSQL = strSQL & "order by O.AccountNumber, O.TransactionDate"

If I remove the code in RED it will work OK

The problem seems to be caused by the OR Statement - basically I want all transactions which have a transaction type of 51 or 65 with the other account and date criteria selected as well

???

JUST ENTERED MY HEAD TO PUT BRACKETS AROUND

and (O.TransactionType = 51 OR O.TransactionType = 65 )

AND IT WORKS !!!!!

[davide++]

You shouldn't use OR operator.
Never.
It slows down query execution; without brackets the db engine links all ANDs condition with the two OR choices so the query is very slow.

Instead of ORs, you can use IN operator:


O.TransactionType IN (51, 65)


Usually it's faster and more readeable.

[George1111]

David - you are a legend ! Thanks so much for that - what a great way to solve selection from a random list of possible matches - great stuff !