Errors from an Access Query

[gquery]

Hey guys, I am moving a query used in SQL management studio to access and some problems have occurred.
I understand that parenthesis placement is important to look at whenever dealing with an error such as this,
but I am not really sure where to begin. I have attempted a couple of changes with the parenthesis, but have not
had much luck. Thanks to whoever may be able to point me in the right direction.

[Micron]

The only thing I can see (I think - text is small) is an un-paired ( in the WHERE line. I count one extra left parenthesis.
EDIT: never mind. I magnified the screen and I see that I missed one. What happens if you put the sql in sql view and attempt to run it? Sometimes the offending part will be highlighted, or at least close to it.

ISNULL is vba Is Null is sql. Not sure but I believe query design will automatically change ISNULL to Is Null; not sure about sql view.

[CJ_London]

ISNULL in access returns a Boolean value, use nz instead

you have named your table cfo, but your link refers to cfo1

[gquery]

@Micron I thought that was the problem as well, but apparently that is correct and not at error.

[gquery]

@Ajax I made the changes, but I am still getting the same error. I have tried taking out the sub-queries that handle 'operator' and 'unassigned description' (also taking out the "Nz(cfo.StringType, cfo1.StringType) AS Operator") while leaving only the sub that handles 'Transmission' and it shows the result set fine, so according to what I have tested, and what the error shows, it has to be some anomaly with the two queries mentioned previously.

[gquery]

@Micron
I looked at that previously as well. Here is what it highlighted. I put brackets around it to prevent any issues. Having that symbol shouldn't be an issue though.

[Micron]

Having that symbol shouldn't be an issue though.

I don't understand that point of view. There's a reason why experienced developers NEVER use such characters unless they are required. That character is a date delimiter and may very well be the issue - being treated as an improperly delimited date. Suggest you do away with all such characters even if it doesn't solve this problem.

Not sure if above you've said you also broke this down to a query that contains only the very first part and it still errors out. If not, suggest you start small and build from there, even stripping down to only a portion of each sql block. However, I'd do away with the # as a start.

[gquery]

I tested it without the symbol and nothing changed. I am not super experienced, so I will happily take any of your advice. The symbol is gone! Also, I did say that I broke it down and it worked, but with only the sub-query that pulls transmission data. I will go ahead and try breaking it down again and try some other things. If you see any issues that are pretty obvious though please do not hesitate to inform me.

[June7]

I build nested queries by first building and saving the inner queries as query objects. Build the outer query that references the saved query objects. Copy/paste the sql statements of the inner queries into the outer query and add parens and " AS ". When that works I delete the inner query objects. Don't include the semi-colon from the inner query sql.

Also recommend not using spaces nor punctuation/special characters (underscore only exception) in name. Also don't use reserved words as names. Year is a reserved word (it is a function).

[Micron]

gquery, if you just can't solve it based on prior comments here, consider posting a zipped copy of your compacted db. You should be able to strip out and/or modify any private data if need be.

[CJ_London]

@gquery - suggest you copy/paste your sql text rather than an image - makes it much easier to analyse and send back an alternative

I suspect Access brackets tables/joins differently to MySQL.

In access you would have

SELECT ...
FROM ((T1 INNER JOIN T2 ON T1.T1PK = T2.T1FK) INNER JOIN T3 ON T1.T1PK = T3.T1FK) INNER JOIN T4 ON T1.T1PK = T4.T1PK

I don't see the equivalent brackets in red in your code