Using SQL Query problem

[shophoney]

Hi I've written the statement below. But it's complaining about the "HAVING" part. I'm trying to prefilter the data prior to using Power Query.

SELECT tbljournal.posted_dt AS [JOURNAL DATE],
tblreceive.link_journal,
tblreceive.dest_loc_code,
tblreceive.reference_no,
tblreceive.vendor_code,
tblreceive.transact_type,
tblreceiveitem.sku_no,
tblreceiveitem.qty,
tblreceiveitem.cost,
tblreceiveitem.landed_cost AS [EXT LANDED COST],
tblreceive.rebate_perc,
tblreceiveitem.rebate_amount,
tblreceive.currency_code,
tblreceive.exchange_rate,
tblreceive.gst,
tblreceiveitem.retail,
tblreceive.note
FROM (tblreceiveitem
INNER JOIN tblreceive
ON ( tblreceiveitem.source_loc_code =
tblreceive.source_loc_code )
AND ( tblreceiveitem.dest_loc_code =
tblreceive.dest_loc_code )
AND ( tblreceiveitem.vendor_code =
tblreceive.vendor_code )
AND ( tblreceiveitem.reference_no =
tblreceive.reference_no ))
INNER JOIN tbljournal
ON tblreceive.link_journal = tbljournal.link_journal
GROUP BY tbljournal.posted_dt,
tblreceive.link_journal,
tblreceive.dest_loc_code,
tblreceive.reference_no,
tblreceive.vendor_code,
tblreceive.transact_type,
tblreceiveitem.sku_no,
tblreceiveitem.qty,
tblreceiveitem.cost,
tblreceiveitem.landed_cost,
tblreceive.rebate_perc,
tblreceiveitem.rebate_amount,
tblreceive.currency_code,
tblreceive.exchange_rate,
tblreceive.gst,
tblreceiveitem.retail,
tblreceive.note,
tblreceiveitem.line_no
HAVING (( ( tbljournal.posted_dt ) ># 1 / 31 / 2015# ));

 

[mole999]

I'm very rusty on Access and hardly ever use GROUP and HAVING, but it occurs to me you have 17 top SELECTS and 18 GROUP BY

 

[anvg]

Hi
You have a mistake with

[COLOR=#333333](( ( tbljournal.posted_dt ) ># 1 / 31 / 2015# ));[/COLOR]

It is not allowed in HAVING clause. Use

Select fileldList From JoinedTables Where [COLOR=#333333]( tbljournal.posted_dt ) >#1/31/2015# Group By groupFields[/COLOR]