IIF function to display the query if false

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
166
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?

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

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
Code:
If[COLOR=#333333] [center_checkqry]![Plant] <> ""[/COLOR] Then
    MsgBox [COLOR=#333333]"No new Center/s"
Else
    DoCmd.OpenQuery "QueryName"
End If[/COLOR]
 
Upvote 0
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top