+ Reply to Thread
Results 1 to 11 of 11

How can Access form run a query if field is not null?

  1. #1
    Valued Forum Contributor
    Join Date
    12-22-2010
    Location
    Texas
    MS-Off Ver
    Excel 2003 & Excel/Access 2007
    Posts
    438

    How can Access form run a query if field is not null?

    I have a form where a user has four choices to search by…. A macro runs when the “Run” icon is clicked. The macro has four conditions:

    [Forms]![frm_Build_ARMS]![ACCOUNT_REFERENCE_NUMBER] Is Not Null
    [Forms]![frm_Build_ARMS]![EIN] Is Not Null
    [Forms]![frm_Build_ARMS]![SSN] Is Not Null
    [Forms]![frm_Build_ARMS]![SS] Is Not Null

    They system thinks that when the “Run” Icon is hit that the user wants to search for all four choices & gets error messages. You should only get results for whatever field is not null. Any ideas of what I'm missing? I would think the system wouldn't run that query if that field is null...

    I attached a picture of my form...
    Attached Files Attached Files
    Last edited by jgomez; 01-14-2013 at 02:35 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,612

    Re: How can Access form run a query if field is not null?

    The query shouldn't run when what field is null?

    What criteria do you want to use anyway?

    Should it only be one field in the criteria?

    Can you post the SQL for the query?
    If posting code please use code tags, see here.

  3. #3
    Valued Forum Contributor
    Join Date
    12-22-2010
    Location
    Texas
    MS-Off Ver
    Excel 2003 & Excel/Access 2007
    Posts
    438

    Re: How can Access form run a query if field is not null?

    Yes, the query shouldn't run if ACCOUNT_REFERENCE_NUMBER, EIN, SSN or SS fields are null. What criteria are you referring to?

    There are four queries... here is the SQL to one of them:

    Please Login or Register  to view this content.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,612

    Re: How can Access form run a query if field is not null?

    So if any of the fields are Null the query shouldn't run?

    I'm referring to the criteria you are using in the WHERE clause of the query.

    Do you have code behind the form or just a macro?

    If you do can you post it?

    Also, are the 4 queries the same apart from the field in the WHERE clause?s

  5. #5
    Valued Forum Contributor
    Join Date
    12-22-2010
    Location
    Texas
    MS-Off Ver
    Excel 2003 & Excel/Access 2007
    Posts
    438

    Re: How can Access form run a query if field is not null?

    Yes if any of the fields in the pic. I posted are null then nothing should run....

    Please Login or Register  to view this content.
    is the code I have in my query. Its just a macro behind the "Button"....

    The four queries are different... different tables. Three of them allow you to search by EIN and one by account number.

    If one field is not null & I hit run I get the correct results... only issue is that its opening the other 3 queries.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,612

    Re: How can Access form run a query if field is not null?

    So if I've entered an EIN but not entered an SSN, SS or ACCOUNT_REFERENCE the relevant query shouldn't run?

  7. #7
    Valued Forum Contributor
    Join Date
    12-22-2010
    Location
    Texas
    MS-Off Ver
    Excel 2003 & Excel/Access 2007
    Posts
    438

    Re: How can Access form run a query if field is not null?

    I know its confusing because of the EIN, SSN & SS but those are three different queries. If you enter the EIN the queries tied to SSN, SS or ACCOUNT_REFERENCE should not run.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,612

    Re: How can Access form run a query if field is not null?

    You can add conditions to the existing macro but I don't know how that would fit in with what you have.

    If it was a new macro you would add If from the Add New Action dropdown, then click the wand to open the Expression Builder and add this expression.

    [Forms]![frm_Build_ARMS]![ACCOUNT_REFERENCE_NUMBER] Is Not Null

    Then you would select OpenQuery from the Add New Action dropdown and select the appropriate query from the Query Name dropdown.

    You would repeat these steps for each field/query.

    Does that make any sense?

    If it was code you were using it would be far easier to explain, and I could probably post some code.

  9. #9
    Valued Forum Contributor
    Join Date
    12-22-2010
    Location
    Texas
    MS-Off Ver
    Excel 2003 & Excel/Access 2007
    Posts
    438

    Re: How can Access form run a query if field is not null?

    What do you mean by "If it was a new macro you would add If from the Add New Action dropdown, then click the wand to open the Expression Builder and add this expression."???

    Kind of get it.... kind of lost.

  10. #10
    Valued Forum Contributor
    Join Date
    12-22-2010
    Location
    Texas
    MS-Off Ver
    Excel 2003 & Excel/Access 2007
    Posts
    438

    Re: How can Access form run a query if field is not null?

    Got it...

    CODE][Forms]![frm_Build_ARMS]![ACCOUNT_REFERENCE_NUMBER]<>""[[/CODE]

    I had

    Please Login or Register  to view this content.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,612

    Re: How can Access form run a query if field is not null?

    Well it's hard to explain, especially when I don't really know anything about the existing macro.

    That's why I thought it might help to explain how you would do it if you started the macro from scratch.

    Did you try creating a new macro and following my exacting instructions?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1