MS Access Query - Syntax error (missing operator) in query expression ''.

[NSearch]

Hello all,

Thanks in advance for reviewing my post and the query below.

I cannot figure out where my issue is with this query:

SELECT inv_acct.invoice_number AS Invoice_Number,
inv_acct.account_number AS Account_Number,
Round(gsr.net_charges, 2) AS GSRs,
Round(cadj.net_charges) AS [Credit Adjustments],
Round(void.net_charges) AS Voids
FROM (SELECT sd1.invoice_number,
sd1.account_number
FROM shipment_details AS sd1
WHERE sd1.invoice_date >= '2019-12-18'
AND sd1.invoice_date <= '2019-12-18'
AND sd1.account_number IN (SELECT cca2.account_number
FROM carrier_client_accounts AS cca2
WHERE cca2.client_code = 'CRES'
AND cca2.carrier_code = 'UPSN')
GROUP BY sd1.invoice_number,
sd1.account_number) inv_acct
LEFT JOIN (SELECT sd2.invoice_number,
sd2.account_number,
Sum(sd2.net_amount) AS net_charges
FROM shipment_details sd2
WHERE sd2.charge_category_detail_code = 'GSR'
AND sd2.invoice_date >= '2019-12-18'
AND sd2.invoice_date <= '2019-12-18'
AND sd2.account_number IN (SELECT cca1.account_number
FROM
carrier_client_accounts AS cca1
WHERE
cca1.client_code = 'CRES'
AND cca1.carrier_code =
'UPSN')
GROUP BY sd2.invoice_number,


sd2.account_number) AS gsr
ON gsr.invoice_number = inv_acct.invoice_number
AND gsr.account_number = inv_acct.account_number
LEFT JOIN (SELECT sd2.invoice_number,
sd2.account_number,
Sum(sd2.net_amount) AS net_charges
FROM shipment_details sd2
WHERE sd2.charge_category_detail_code = 'VOID'
AND sd2.invoice_date >= '2019-12-18'
AND sd2.invoice_date <= '2019-12-18'
AND sd2.account_number IN (SELECT cca1.account_number
FROM
carrier_client_accounts AS cca1
WHERE
cca1.client_code = 'CRES'
AND cca1.carrier_code =
'UPSN')
GROUP BY sd2.invoice_number,
sd2.account_number) AS void
ON void.invoice_number = inv_acct.invoice_number
AND void.account_number = inv_acct.account_number
LEFT JOIN (SELECT sd2.invoice_number,
sd2.account_number,
Sum(sd2.net_amount) AS net_charges
FROM shipment_details sd2
WHERE sd2.charge_category_detail_code = 'CADJ'
AND sd2.invoice_date >= '2019-12-18'
AND sd2.invoice_date <= '2019-12-18'
AND sd2.account_number IN (SELECT cca1.account_number
FROM
carrier_client_accounts AS cca1
WHERE
cca1.client_code = 'CRES'
AND cca1.carrier_code =
'UPSN')
GROUP BY sd2.invoice_number,
sd2.account_number) AS cadj
ON cadj.invoice_number = inv_acct.invoice_number
AND cadj.account_number = inv_acct.account_number

[orange]

In plain English, what is the issue?
What is the purpose of the query?

I copied your SQL to Poor SQL and formatted it, and it did not indicate an error???

Just looking for a little context.

[Bulzie]

Suggest break out and run each of those select lines to see which one errors.

[NSearch]

Hi orange,

Thanks for the reply.

I am getting the following error when running the query above "MS Access Query - Syntax error (missing operator) in query expression ''."

This is what I am trying to do (and works in MySQL):

invoice_Number	Account_Number	GSRs	Credit Adjustments	Voids
00000007XXXXX29	0000079XXX	-109.58	11
00000014XXXXX39	0000142XXX	-253.37
00000014XXXXX49	0000142XXX	-1130.32		-64
00000014XXXXX59	0000142XXX

[orange]

You could try Bulzie's approach and see if you can narrow down the problem.

[CJ_London]

can't see the point of this bit

WHERE sd1.invoice_date >= '2019-12-18'
AND sd1.invoice_date <= '2019-12-18'

assuming this is access sql, you should be using #, at the moment it is text.

in the immediate window
?date()<= "2019-12-18"
True
?date()<= "#2019-12-18#"
False



even if it is working, you will only get records where sd1.invoice_date = '2019-12-18' since any other date cannot be less than and greater than 2019-12-18 at the same time

[NSearch]

Hi Ajax,

Thanks for the comments...

The date range is that way because I am using vba to prompt the user for a start and end date, along with the client code.

orange, I tried Bulzie's suggestion and each component works fine -- all individual join select statements, and the main select. The query also works as a whole in MySQL....

So my thought is that there is a problem with parenthesis -- specifically, joining on multiple elements.

Does anyone have any suggestions on how the query should be formatted according to MS Access's rules on parenthesis and brackets?

[Micron]

Sometimes if you run a faulty query in SQL View in Access, it will highlight the offending part. If you tried that and it highlights something at least it would narrow it down to one specific line rather than just getting a cryptic message. Maybe you should try that.

[CJ_London]

Every rdbms has its own variations of SQL. If you are running this as an access query, i.e. not a passthrough query or stored procedure which would be native mySQL, you need to use # not ' around your dates. You might also need to look at the bracketing around your FROM clause

how the query should be formatted according to MS Access's rules on parenthesis and brackets?

easiest way is to build it using the query builder