#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] How can Access form run a query if field is not null?

## jgomez

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...

----------


## Norie

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?

----------


## jgomez

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.
```

----------


## Norie

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

----------


## jgomez

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.

----------


## Norie

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

----------


## jgomez

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.

----------


## Norie

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.

----------


## jgomez

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.

----------


## jgomez

Got it...

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

I had 




```
Please Login or Register  to view this content.
```

----------


## Norie

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?

----------

