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

[Nadnerb78]

Please see code below:

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

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

[mtaylor314]

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

\

In the example you gave, you put single quote ' ' around xx for Packaging Location but not for Week.

[Nadnerb78]

I did that intentionally because the field Week is a number data type; is that the incorrect way to handle that?

When I add the single quotes around Week I receive a data type mismatch error.

[Killer42]

You're correct, for a numeric field you don't use quotes. mtaylor314 just didn't realise it was numeric.

I would definitely remove some of the parentheses - there are 'way too many. This is probably the fault of Access, which throws them in everywhere it can possibly find an excuse. Let's try to trim it down a bit...

[CODE=vb]strSQL = "SELECT Max(EntryType) AS MaxOfEntryType " & _
"FROM tblForecastData GROUP BY BusinessLine, [Packaging Location], Week " & _
"HAVING [Packaging Location] = '" & xx & "' AND Week = " & xx[/CODE]By the way, I assume xx is just a placeholder here, and not your actual variable name?

One other thing - I could be completely wrong on this, but I think that a WHERE clause would be more efficient than a HAVING clause, in this case. Someone who is more on the ball with SQL could verify or refute that.

[Killer42]

P.S. You reported this Extra ) in query expression '((([Packaging Location])=") AND ((Week)=))'. In this case, it looks as though xx was empty, resulting in what does look like invalid SQL. For example, I'd guess that even if the extra parentheses all over the place are ok, ((Week)=)) should be ((Week)=0)).

[Nadnerb78]

Thank you both very much for your replies.

Here is what I've come up with using your input:

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

In the WHERE clause 8150 (text data type) and 1 (number data type) are my test values.

I now receive the following error message when running the procedure:

Run-time error '3075':

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

Any suggestions?

[Killer42]

I now receive the following error message when running the procedure:

Run-time error '3075':

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

Perhaps the missing space after '8150' is upsetting it?