whats wrong with this sql statement?

[simon_marklar]

SELECT Fields
FROM table1 RIGHT JOIN table2 ON table1.Pubid = table2.PubID
WHERE (table1.PubType=[Forms]![frmCirculationByTitle]![comMemberType] Or [Forms]![frmCirculationByTitle]![comMemberType] Is null) 
AND (table2.lAuditPeriod=[Forms]![frmCirculationByTitle]![comStartPeriod] Or [Forms]![frmCirculationByTitle]![comStartPeriod] IS null) 
And (table2.lAuditPeriod=[Forms]![frmCirculationByTitle]![comEndPeriod] Or [Forms]![frmCirculationByTitle]![comEndPeriod] is null) 
AND (table2.Unfinancial=[Forms]![frmCirculationByTitle]![chkCurrent] Or [Forms]![frmCirculationByTitle]![chkCurrent] IS null);

even if i only have one parameter (a member type, which should bring back at least 20 for each type of member) it returns nothing. if i do the same sql statement in a query and hard codee the value in i get data. it seems to be ignoring whatever is on the form.

*shrug*

%simon

 

[neileg]

I think a lack of brackets is causing the problem. Try this:

SELECT Fields
FROM table1 RIGHT JOIN table2 ON table1.Pubid = table2.PubID
WHERE ((table1.PubType=[Forms]![frmCirculationByTitle]![comMemberType]) Or ([Forms]![frmCirculationByTitle]![comMemberType] Is null)) 
AND ((table2.lAuditPeriod=[Forms]![frmCirculationByTitle]![comStartPeriod]) Or ([Forms]![frmCirculationByTitle]![comStartPeriod] IS null)) 
And ((table2.lAuditPeriod=[Forms]![frmCirculationByTitle]![comEndPeriod]) Or ([Forms]![frmCirculationByTitle]![comEndPeriod] is null)) 
AND ((table2.Unfinancial=[Forms]![frmCirculationByTitle]![chkCurrent]) Or ([Forms]![frmCirculationByTitle]![chkCurrent] IS null));

 

[simon_marklar]

Perfect. I thought i had the brackets like that in one attempt but obviously not

Thank you.
%simon