Results 1 to 4 of 4
  1. #1
    franlo is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2014
    Location
    Glasgow, UK
    Posts
    2

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

    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.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,290
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    franlo is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2014
    Location
    Glasgow, UK
    Posts
    2
    Ok, Ill need to dig into vba then, thanks for the prompt reply.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,290
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Enter Parameter Value error
    By bronson_mech in forum Queries
    Replies: 9
    Last Post: 11-30-2013, 05:00 PM
  2. ComboBox to Run Query and enter Parameter
    By tristangemus in forum Forms
    Replies: 13
    Last Post: 06-25-2013, 09:54 AM
  3. Replies: 2
    Last Post: 04-04-2013, 03:13 PM
  4. Enter Parameter Value Error in Macro
    By kasimagj in forum Programming
    Replies: 3
    Last Post: 09-19-2011, 03:53 PM
  5. Replies: 2
    Last Post: 04-18-2011, 06:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums