can someone look at my sql?

[Sam_Bur]

I am trying to get a report to show a list acquisitions for a particular product between two dates i have set up a query and a form and a report.

The SQL for the query is as follows

SELECT tblAcq.Log_In_ID, tblAcqDetail.Product_ID, tblAcq.Log_Date, tblProducts.Product_Name, tblProducts.Unit_Price, tblProducts.QuantityProd, tblAcq.Log_Note
FROM tblAcq INNER JOIN (tblProducts INNER JOIN tblAcqDetail ON tblProducts.Product_ID = tblAcqDetail.Product_ID) ON tblAcq.Log_In_ID = tblAcqDetail.Log_In_ID
WHERE (((tblAcqDetail.Product_ID)=[Forms]![FrmAcquisitionReport]![CboProduct]) AND ((tblAcq.Log_Date) Between [Forms]![FrmAcquisitionReport]![Begin] And [Forms]![FrmAcquisitionReport]![End])) OR (((tblAcq.Log_Date) Between [Forms]![FrmAcquisitionReport]![Begin] And [Forms]![FrmAcquisitionReport]![End]) AND (([Forms]![FrmAcquisitionReport]![CboProduct])=0));

Ultimatly i am trying to get the report to be produced when the dates and the product are entered on the form. Maybe my sql is wrong as it keeps coming up with the message that the exspression is too complicated. Can anyone help?:

 

[MarkK]

In the where clause you have the same date range comparison twice. This is not required. Reformulate your where clause from...

WHERE (DateRange AND Item1) OR (DateRange AND Item2)

...to...

WHERE (DateRange) AND (Item1 OR Item2)

...and see if it works.

 

[Sam_Bur]

Thanks that could well be the answer will give that a go in a minute, just also realised tho that the info that i am typing into the form is not actually being picked up by the query. When i press the preview report command button it brings up the boxes to enter the data into, instead of just reading the data from the text boxes and the combo box on the form. I have check all the names of the text boxes and combo box and also the name of the form and they are all correct any other possible reasons for this?

 

[namliam]

WHERE (tblAcqDetail.Product_ID=[Forms]![FrmAcquisitionReport]![CboProduct]
AND    tblAcq.Log_Date Between [Forms]![FrmAcquisitionReport]![Begin] 
                           And [Forms]![FrmAcquisitionReport]![End]) 
OR (   tblAcq.Log_Date Between [Forms]![FrmAcquisitionReport]![Begin] 
                           And [Forms]![FrmAcquisitionReport]![End]
AND    [Forms]![FrmAcquisitionReport]![CboProduct]=0);

After stripping the access ()'s the where clause actually looks OK....

But I think that access cannot handle this:
[Forms]![FrmAcquisitionReport]![CboProduct]=0
in a where clause of a query... I think you have to do something with an Iif to be able to handle it...

Add a field in your select
HiddenWhere: Iif([Forms]![FrmAcquisitionReport]![CboProduct]=0,0,tblAcqDetail.Product_ID)
And then use this to match to your [Forms]![FrmAcquisitionReport]![CboProduct]

Or:

WHERE (Iif([Forms]![FrmAcquisitionReport]![CboProduct]=0,0,tblAcqDetail.Product_ID) =[Forms]![FrmAcquisitionReport]![CboProduct]
AND    tblAcq.Log_Date Between [Forms]![FrmAcquisitionReport]![Begin] 
                           And [Forms]![FrmAcquisitionReport]![End])

Not sure this will work tho...

 

[Wiz47]

Thanks that could well be the answer will give that a go in a minute, just also realised tho that the info that i am typing into the form is not actually being picked up by the query. When i press the preview report command button it brings up the boxes to enter the data into, instead of just reading the data from the text boxes and the combo box on the form. I have check all the names of the text boxes and combo box and also the name of the form and they are all correct any other possible reasons for this?

Chances are very good that you have misspelled or mismatched a textbox or field name somewhere. In a long query, it can be very frustrating trying to find that one error. Also look at the names in the report for the error. Been there, have a t-shirt