IIF function to display the query if false

[dhen21dx]

Hi I create IIF funtion in MS Access but dont know how to Open a Query if result is false. Thanks in advance for the help. Or is there is a way to make it in vba?

IIF( [center_checkqry]![Plant] <> "",MsgBox("No new Center/s"),

 

[Joe4]

An IIF function is used in a calculated field in a query (or in a table, in newer versions of Excel). An IIF function can only return a result, not open a query.

What you are talking about would require VBA. In VBA, it would be an IF...THEN function, not an IIF function, i.e.

If[COLOR=#333333] [center_checkqry]![Plant] <> ""[/COLOR] Then
    MsgBox [COLOR=#333333]"No new Center/s"
Else
    DoCmd.OpenQuery "QueryName"
End If[/COLOR]

 

[dhen21dx]

Hi,

I tried to put it in vba but there is an error "Run-time error '2465': Microsoft Access can't find the field '|1' refered to in your expression"
Thanks

 

[Joe4]

What is "[center_checkqry]"? A table or query? You may need to preface that with a table/query reference depending on where you are putting this code.
Where are you putting this VBA code? How exactly is the VBA code being invoked/called?

 

[dhen21dx]

I create button box and create Expression Code Builder.
center_checkqry is Query.

 

[Joe4]

I create button box and create Expression Code Builder.

Do you mean you have a Command Button on a Form?
What kind of Form is it? Is it a bound or unbound Form?
If it is bound, it is bound to the "center_checkqry" query (so the "Plant" field value it is pulling should be from the Form field value of the current record)?

 

[dhen21dx]

Hi Joe,

Yes a command Button in a Form, i don't know how to check if it bound or unbound, but just want to open a query if it has value, it NULL only message will pop up.

 

[Joe4]

Open up the Form in Design View, then go to the Properties of the Form.
What does it show as the "Record Source" property (if anything) of the Form?

 

[dhen21dx]

The Data Source is Blank, i need to select the center_checkqry?

 

[Joe4]

The Data Source is Blank, i need to select the center_checkqry?

That means it is an unbound form. So "[center_checkqry]![Plant]" in your code would apply to the WHOLE column, not just one record.
If the "Plant" field is on your Form, and you want the code to apply to the value entered in the field for that particular record, I think you will want to use:
"Me.[Plant]" instead of "[center_checkqry]![Plant]".

But perhaps we should take a step back, and have you describe exactly what this code is supposed to be doing, and how it pertains to the Form/Record that you are on.