Access adds parenthesis in SQL string from VBA

  • Thread starter Thread starter James Allen
  • Start date Start date
J

James Allen

I have a SQL string that is written in VBA, and is too
comlex to create in the Access QBE.
However, the problem is that when my query is passed into
Access to execute it groups the criteria in the WHERE
clause with parenthesis and accordingly the query does not
work right.
Example:
My SQL string is:

SELECT DISTINCT StudentDownload.ID,
StudentDownload.Name, StudentDownload.AmountAwarded,
Criteria.SchDesignation, StudentDownload.Major,
StudentDownload.GPA,5800 AS SpenAmount INTO tblResults
FROM StudentDownload, Criteria WHERE
Criteria.SchDesignation ="BOSWE" AND
StudentDownload.AmountAwarded <= 5800 AND
StudentDownload.Major Like "*" & "Business" OR
StudentDownload.Major Like "*" & "Accounting" OR
StudentDownload.Major Like "*" & "Finance" OR
StudentDownload.Major Like "*" & "Marketing" OR
StudentDownload.Major Like "*" & "Management" OR
StudentDownload.Major Like "*" & "Electronic Business"

Access does this in in the SQL design view:

SELECT DISTINCT StudentDownload.ID, StudentDownload.Name,
StudentDownload.AmountAwarded, Criteria.SchDesignation,
StudentDownload.Major, StudentDownload.GPA, 5800 AS
SpenAmount INTO tblResults
FROM StudentDownload, Criteria
WHERE (((Criteria.SchDesignation)="BOSWE") AND
((StudentDownload.AmountAwarded)<=5800) AND
((StudentDownload.Major) Like "*" & "Business")) OR
(((StudentDownload.Major) Like "*" & "Accounting")) OR
(((StudentDownload.Major) Like "*" & "Finance")) OR
(((StudentDownload.Major) Like "*" & "Marketing")) OR
(((StudentDownload.Major) Like "*" & "Management")) OR
(((StudentDownload.Major) Like "*" & "Electronic
Business"));
In the query design view Access places each criteria
vertically , I need the criteria to remain in one field in
the design view for this work.
How can I prevent Access from grouping my criteria the way
it wants to?
This is a problem because this is not the query that I
need, it does not return the correct results.

James
 
Access will always add the parentheses. SO add them first. You can probably do
so by just adding one set around the or clauses. I think that what you want is
records where
SchDesignation = "BOWSE" And AmountAwarded is Less than or equal to 5800

AND the major is in one of the areas listed.

SELECT DISTINCT StudentDownload.ID,
StudentDownload.Name, StudentDownload.AmountAwarded,
Criteria.SchDesignation, StudentDownload.Major,
StudentDownload.GPA,5800 AS SpenAmount INTO tblResults
FROM StudentDownload, Criteria WHERE
Criteria.SchDesignation ="BOSWE" AND
StudentDownload.AmountAwarded <= 5800 AND
(
StudentDownload.Major Like "*" & "Business" OR
StudentDownload.Major Like "*" & "Accounting" OR
StudentDownload.Major Like "*" & "Finance" OR
StudentDownload.Major Like "*" & "Marketing" OR
StudentDownload.Major Like "*" & "Management" OR
StudentDownload.Major Like "*" & "Electronic Business"
)

The parens are on separate lines for emphasis.

By the way, in many systems your query would have returned records for the last
5 majors (ignoring the school and amount for those records) and only using the
school and amount in conjuction with the Business majors.
 
Thanks John for the help, that's what I needed.
I'm also interested to know what systems would not return
the results I need as I have written them for Access?
James
 
I don't recall the exact systems but I have run into the problem before where I
needed to add parentheses to enforce the desired grouping of criteria. I am
just cautious. I often include parens in my arithmetic calculations. I know
that certain operations (such as, multiplication and division) have precedence
over other operations, but just to be sure the operations occur in the order I
want I will add parentheses to force that order.
 
Back
Top