Run-time error '3075': Extra ) in query expression...

[Nadnerb78]

Please see code below:

strSQL = "SELECT Max(EntryType) AS MaxOfEntryType " & _
"FROM tblForecastData GROUP BY BusinessLine, [Packaging Location],
Week " & _
"HAVING ((([Packaging Location])='" & xx & "') AND ((Week)=" & xx &
")) "

I don't understand why I am receiving the following error:

Run-time error '3075':

Extra ) in query expression '((([Packaging Location])=") AND
((Week)=))'.

If I remove the ('s I receive an error stating that there are
(operators missing). Is there anything obviously wrong someone would
please point out to me?

Thank you,

Nadnerb

 

[Graham Mandeno]

You are using a variable "xx" to substitute a match value for both
[Packaging Location] *and* [Week]. It appears that the variable is either
undeclared or Null, because there is nothing in your SQL string in either of
the locations where it is substituted.

I can understand why the message is confusing, because you have the right
number of parentheses (actually you have far too many, but at least they
balance!)

At the moment, you are selecting records where Week=. Week = what? This is
confusing the SQL parser into thinking the number of parentheses is wrong.

I presume you don't want to find only records where [Packaging Location] and
[Week] have the same value, so your two "xx"s should be different variables.
Also, you are enclosing the [Packaging Location] value in quotes, but not
the [Week] value. This implies that [PL] is a text field and [Week] is
numeric, and is correct so long as that is the case.

You can actually get rid of *all* the parentheses:

.... "HAVING [Packaging Location]='" & x1 & "' AND Week=" & x2

where x1 is the variable or control containing the [PL] match value and x2
is the variable or control containing the [Week] match value.

 

[Douglas J. Steele]

Did you copy and paste the code? The parentheses are different in what you
posted than in your error message.

Access has a bad habit of putting in far more parentheses than is really
required. Try:

strSQL = "SELECT Max(EntryType) AS MaxOfEntryType " & _
"FROM tblForecastData " & _
"GROUP BY BusinessLine, [Packaging Location], Week " & _
"HAVING [Packaging Location]='" & xx & "' " & _
"AND Week=" & xx

 

[Nadnerb78]

You are using a variable "xx" to substitute a match value for both
[Packaging Location] *and* [Week]. It appears that the variable is either
undeclared or Null, because there is nothing in your SQL string in either of
the locations where it is substituted.

I can understand why the message is confusing, because you have the right
number of parentheses (actually you have far too many, but at least they
balance!)

At the moment, you are selecting records where Week=. Week = what? This is
confusing the SQL parser into thinking the number of parentheses is wrong.

I presume you don't want to find only records where [Packaging Location] and
[Week] have the same value, so your two "xx"s should be different variables.
Also, you are enclosing the [Packaging Location] value in quotes, but not
the [Week] value. This implies that [PL] is a text field and [Week] is
numeric, and is correct so long as that is the case.

You can actually get rid of *all* the parentheses:

... "HAVING [Packaging Location]='" & x1 & "' AND Week=" & x2

where x1 is the variable or control containing the [PL] match value and x2
is the variable or control containing the [Week] match value.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Please see code below:

strSQL = "SELECT Max(EntryType) AS MaxOfEntryType " & _
"FROM tblForecastData GROUP BY BusinessLine, [Packaging Location],
Week " & _
"HAVING ((([Packaging Location])='" & xx & "') AND ((Week)=" & xx &
")) "

I don't understand why I am receiving the following error:

Run-time error '3075':

Extra ) in query expression '((([Packaging Location])=") AND
((Week)=))'.

If I remove the ('s I receive an error stating that there are
(operators missing). Is there anything obviously wrong someone would
please point out to me?

Thank you,

Nadnerb- Hide quoted text -

- Show quoted text -

Thank you both for your help. I am still having some trouble and I'm
sure that it is user error as I am very new at this.

Here is the code I have now:

strSQL = "SELECT Max(EntryType) AS MaxOfEntryType " & _
"FROM tblForecastData GROUP BY BusinessLine, [Packaging Location],
Week " & _
"WHERE [Packaging Location]='" & 8150 & "'AND Week=" & 1

And I am receiving the following error now:
Run-time error '3075':

Syntax error (missing operator) in query expression 'Week WHERE
[Packaging Location]='8150' AND Week=1'.

Any suggestions?

 

[Douglas J. Steele]

Your SQL is incorrect: the WHERE clause needs to precede the GROUP BY
clause:

strSQL = "SELECT Max(EntryType) AS MaxOfEntryType " & _
"FROM tblForecastData " & _
"WHERE [Packaging Location]='" & 8150 & "' AND Week=" & 1 & _
" GROUP BY BusinessLine, [Packaging Location], Week "

or

strSQL = "SELECT Max(EntryType) AS MaxOfEntryType " & _
"FROM tblForecastData " & _
"WHERE [Packaging Location]='8150' AND Week=1 " & _
"GROUP BY BusinessLine, [Packaging Location], Week "

(Note, too, that you appear to be missing a space between the quote and the
keyword AND)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You are using a variable "xx" to substitute a match value for both
[Packaging Location] *and* [Week]. It appears that the variable is
either
undeclared or Null, because there is nothing in your SQL string in either
of
the locations where it is substituted.

I can understand why the message is confusing, because you have the right
number of parentheses (actually you have far too many, but at least they
balance!)

At the moment, you are selecting records where Week=. Week = what? This
is
confusing the SQL parser into thinking the number of parentheses is
wrong.

I presume you don't want to find only records where [Packaging Location]
and
[Week] have the same value, so your two "xx"s should be different
variables.
Also, you are enclosing the [Packaging Location] value in quotes, but not
the [Week] value. This implies that [PL] is a text field and [Week] is
numeric, and is correct so long as that is the case.

You can actually get rid of *all* the parentheses:

... "HAVING [Packaging Location]='" & x1 & "' AND Week=" & x2

where x1 is the variable or control containing the [PL] match value and
x2
is the variable or control containing the [Week] match value.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Please see code below:

strSQL = "SELECT Max(EntryType) AS MaxOfEntryType " & _
"FROM tblForecastData GROUP BY BusinessLine, [Packaging Location],
Week " & _
"HAVING ((([Packaging Location])='" & xx & "') AND ((Week)=" & xx &
")) "

I don't understand why I am receiving the following error:

Run-time error '3075':

Extra ) in query expression '((([Packaging Location])=") AND
((Week)=))'.

If I remove the ('s I receive an error stating that there are
(operators missing). Is there anything obviously wrong someone would
please point out to me?

Thank you,

Nadnerb- Hide quoted text -

- Show quoted text -

Thank you both for your help. I am still having some trouble and I'm
sure that it is user error as I am very new at this.

Here is the code I have now:

strSQL = "SELECT Max(EntryType) AS MaxOfEntryType " & _
"FROM tblForecastData GROUP BY BusinessLine, [Packaging Location],
Week " & _
"WHERE [Packaging Location]='" & 8150 & "'AND Week=" & 1

And I am receiving the following error now:
Run-time error '3075':

Syntax error (missing operator) in query expression 'Week WHERE
[Packaging Location]='8150' AND Week=1'.

Any suggestions?