Enter parameter value error with an SQL OR statement in an access query

[franlo]

Hi,

I'm trying to get a query to use the value from drop drown menus from multiple forms, but only the current open form. I want it to ignore the other criteria if the form is closed. Here is the generated SQL from Access.

SELECT tbl_invoices.invoiceID, tbl_invoices.clientID, tbl_clients.companyName, tbl_clients.salutation, tbl_clients.clientsName, tbl_clients.address, tbl_clients.[town/city], tbl_clients.region, tbl_clients.postCode, tbl_invoices.dateAdded, tbl_invoices.jobDescription, tbl_invoices.[1], tbl_invoices.[2], tbl_invoices.[3], tbl_invoices.[4], tbl_invoices.[5], tbl_invoices.[6], tbl_invoices.[7], tbl_invoices.[8], tbl_invoices.[9], tbl_invoices.[10], tbl_invoices.[11], tbl_invoices.[12], tbl_invoices.[13], tbl_invoices.[14], tbl_invoices.[15], tbl_invoices.[16], tbl_invoices.[17], tbl_invoices.[18], tbl_invoices.[19], tbl_invoices.[20], tbl_invoices.price, tbl_invoices.vat, tbl_invoices.total
FROM tbl_clients INNER JOIN tbl_invoices ON tbl_clients.clientID = tbl_invoices.clientID
WHERE (((tbl_invoices.invoiceID)=[Forms]![frm_newClient]![invoiceID])) OR (((tbl_invoices.invoiceID)=[Reports]![rpt_listCustmerInvoices]![Invoice No]));

If I have one of the forms closed then I am get the Enter Parameter Prompt box. How do I bypass this? I thought access would ignore the first criteria and move on to the next.

I'm new to using Access and SQL in general. But I think this should work, do I have a syntax error. Or do I need to have separate queries for each form or use an IIF function? What is the best method for getting a query to use only the current open form to supply the input criteria? I have no doubt this would be a breeze with VBA, something else for me to learn.

Thanks in advance



Fran.

[June7]

No, it won't work. Access needs both parameters. Both are used to evaluate if each record meets one or the other.

I never use dynamic parameterized queries.

I use VBA to apply filter criteria to form or report.

[franlo]

Ok, Ill need to dig into vba then, thanks for the prompt reply.

[June7]

Review http://www.allenbrowne.com/ser-62code.html

Instead of applying filter criteria to Filter property of form, could use in code that opens form or report:

DoCmd.OpenForm "formname", , , strWhere