Comparing entries based on Form, Date Range, and Type

[kai597]

I appreciate everyone's help in my prior posts, but yet again my inexperience is showing. I am attempting to create a query wherein, if an individual selects a certain reporting period from a form, the results will yield either a 1 or 0 in a new column. Instead of pasting the entire code, here is an example:

SELECT R_D.Name, R_D.CDate, R_D.CAmount, D_Type.Type, R_D.Trans, IIf([Forms]![Reports]![Report_Type]=1 And [R_D].[Trans]=1 And [R_D].[CDate] Between ("1/1/" & [Forms]![Reports]![Q_Year]) And ("3/31/" & [Forms]![Reports]![Q_Year]),1,0) OR IIf([Forms]![Reports]![Report_Type]=2 And [R_D].[Trans]=1 And [R_D].[CDate] Between ("4/1/" & [Forms]![Reports]![Q_Year]) And ("6/30/" & [Forms]![Reports]![Q_Year]),1,0)AS Receipts.

If I just enter just one of the IIf statements, I will get the expected results, however when I enter the second IIf statment, my results comeback with #Error.

Any thoughts? Thanks/

 

[MSAccessRookie]

I think the problem lies in the association of the members of the compound elements. Below is my attempt to try to get them sorted out. I hope I have all of the "(" and ")" correct.



SELECT R_D.Name, R_D.CDate, R_D.CAmount, D_Type.Type, R_D.Trans, (IIf( ( ([Forms]![Reports]![Report_Type]=1) And ([R_D].[Trans]=1) And ([R_D].[CDate] Between ("1/1/" & [Forms]![Reports]![Q_Year]) And ("3/31/" & [Forms]![Reports]![Q_Year]),1,0) ) OR ( (IIf([Forms]![Reports]![Report_Type]=2) And ([R_D].[Trans]=1) And ([R_D].[CDate] Between ("4/1/" & [Forms]![Reports]![Q_Year]) And ("6/30/" & [Forms]![Reports]![Q_Year]) ) ),1,0) AS Receipts.

 

[kai597]

I appreciate the quick response. Unfortunately that did not work; maybe the expression was too complicated for Access to handle. However I found a way around. Thank you again for your advice.