By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,386 Members | 1,774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,386 IT Pros & Developers. It's quick & easy.

Multiple-liine "OR" QBE vs. Single line "OR"

P: n/a
I am constructing a SQL command from a function. Some code builds the WHERE
clause in a looping structure and passes that as an argument to the
SQL-building function. But the results do not always execute as I
envisioned they should. When I trap the finished SQL statement in the
Immediates window and paste it to a query SQL window, I can see in the QBE
why I am not getting what I want.

Basically, I want the QBE window to have the OR commands on one line, but
what I get is multiple lines:

Category Ticker
'Large Cap Value' Not Like 'GTVLX'
'Large Cap Growth' Not Like 'FAEGX'
'Large Cap Growth' Not Like 'MIGFX'
In the above example, I want the output to include all Large Cap Value and
the Large Cap Growth funds, but exclude the two whose tickers I have
specified (FAEGX and MIGFX). When I build this in the QBE window and put
the commands on one line, this executes properly. If it is on multiple
lines, the exclusion does not work. Here is the code that builds the WHERE
clause:

strWhereSql = strWhereSql & " OR (((Sorted_Template.Select_List_Grouping) =
'" & .ItemData(count) & "'))" '& vbCrLf
Me.FundAlternativesList.BoundColumn = 3
strWhereSql = strWhereSql & " AND (((Select_List_Performance.Ticker) NOT
LIKE '" & .ItemData(count) & "'))" '& vbCrLf
strValueList_Excluded = strValueList_Excluded & .ItemData(count) & ";" &
vbCrLf
Me.FundAlternativesList.BoundColumn = 1

Here is the function that receives the WHERE clause and creates the SQL
code:

Private Function BuildSqlStmt_Create_Select_List_Alternatives(strWh ereSql As
String) As String
Dim strMySql As String
strMySql = "SELECT DISTINCT " & vbCrLf
strMySql = strMySql & "Select_List_Performance.*,
Sorted_Template.Morningstar_Category " & vbCrLf
strMySql = strMySql & "FROM Select_List_Performance " & vbCrLf
strMySql = strMySql & "INNER JOIN Sorted_Template ON
Select_List_Performance.Morningstar_Category =
Sorted_Template.Morningstar_Category " & vbCrLf
strMySql = strMySql & strWhereSql & vbCrLf
strMySql = strMySql & "ORDER BY Sorted_Template.Morningstar_Category;"
BuildSqlStmt_Create_Select_List_Alternatives = strMySql
End Function

It seems that when Access builds the SQL statement from the QBE form, the
difference between multiple-lines and a single line OR statement is in the
use of parentheses. Is this what I need to change or is something else the
issue.
Thanks
Alan
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
> Here is the code that builds the WHERE clause:

I forgot to include this next block. The code is looping through a ListBox
and the first time the loop iterates, it initializes the string variable
here, then during the next loop, if this variable already has data in it, it
simply appends to it in the block that follows...

strWhereSql = "WHERE (((Select_List_Performance.Cat_Bnch) Like 'c*') AND
((Sorted_Template.Select_List_Grouping) = '" & .ItemData(count) & "'))" &
vbCrLf
Me.FundAlternativesList.BoundColumn = 3
strWhereSql = strWhereSql & " AND (((Select_List_Performance.Ticker) NOT
LIKE '" & .ItemData(count) & "'))" '& vbCrLf
strValueList_Excluded = strValueList_Excluded & .ItemData(count) & ";" &
vbCrLf
Me.FundAlternativesList.BoundColumn = 1
strWhereSql = strWhereSql & " OR (((Sorted_Template.Select_List_Grouping) = '" & .ItemData(count) & "'))" '& vbCrLf
Me.FundAlternativesList.BoundColumn = 3
strWhereSql = strWhereSql & " AND (((Select_List_Performance.Ticker) NOT
LIKE '" & .ItemData(count) & "'))" '& vbCrLf
strValueList_Excluded = strValueList_Excluded & .ItemData(count) & ";" &
vbCrLf
Me.FundAlternativesList.BoundColumn = 1

Here is the function that receives the WHERE clause and creates the SQL
code:

Private Function BuildSqlStmt_Create_Select_List_Alternatives(strWh ereSql As String) As String
Dim strMySql As String
strMySql = "SELECT DISTINCT " & vbCrLf
strMySql = strMySql & "Select_List_Performance.*,
Sorted_Template.Morningstar_Category " & vbCrLf
strMySql = strMySql & "FROM Select_List_Performance " & vbCrLf
strMySql = strMySql & "INNER JOIN Sorted_Template ON
Select_List_Performance.Morningstar_Category =
Sorted_Template.Morningstar_Category " & vbCrLf
strMySql = strMySql & strWhereSql & vbCrLf
strMySql = strMySql & "ORDER BY Sorted_Template.Morningstar_Category;"
BuildSqlStmt_Create_Select_List_Alternatives = strMySql
End Function

It seems that when Access builds the SQL statement from the QBE form, the
difference between multiple-lines and a single line OR statement is in the
use of parentheses. Is this what I need to change or is something else the issue.
Thanks
Alan

Nov 12 '05 #2

P: n/a
"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:oWxpc.9058$qA.959537@attbi_s51...
I am constructing a SQL command from a function. Some code builds the WHERE clause in a looping structure and passes that as an argument to the
SQL-building function. But the results do not always execute as I
envisioned they should. When I trap the finished SQL statement in the
Immediates window and paste it to a query SQL window, I can see in the QBE
why I am not getting what I want.

Basically, I want the QBE window to have the OR commands on one line, but
what I get is multiple lines:

Category Ticker
'Large Cap Value' Not Like 'GTVLX'
'Large Cap Growth' Not Like 'FAEGX'
'Large Cap Growth' Not Like 'MIGFX'
In the above example, I want the output to include all Large Cap Value and
the Large Cap Growth funds, but exclude the two whose tickers I have
specified (FAEGX and MIGFX). When I build this in the QBE window and put
the commands on one line, this executes properly. If it is on multiple
lines, the exclusion does not work. Here is the code that builds the WHERE clause:

I suggest you abandon the design grid and learn how to write sql. These
sorts of problems become trivial. You need something that looks like this:

select * from alan
where
(
Category = 'Large Cap Value'
or
Category = 'Large Cap Growth'
)
and Ticker <> 'FAEGX '
and Ticker <> 'MIGFX'

Now if you trace through the parentheses you can easily follow the logic of
the where clause constraint. We have three conditions that must be satisfied
for each returned record :

1. Category must be one of 'Large Cap Value' or 'Large Cap Growth'
2. Ticker cannot be 'FAEGX
3. Ticker cannot be 'MIGFX'







Nov 12 '05 #3

P: n/a
"John Winterbottom" <as******@hotmail.com> wrote in message
news:2g************@uni-berlin.de...


In the above example, I want the output to include all Large Cap Value and the Large Cap Growth funds, but exclude the two whose tickers I have
specified (FAEGX and MIGFX). When I build this in the QBE window and put the commands on one line, this executes properly. If it is on multiple
lines, the exclusion does not work. Here is the code that builds the

WHERE
clause:

I suggest you abandon the design grid and learn how to write sql.


Thanks for the help.
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.