Help, beginner expression required!

[jw1234]

I have two fields in a query, [Stock] and [Minimum_Stock]. I want a new field called "Order", that displays a "Y" if the Stock field is equal to or less than the Minimum Stock Field.

Can you please explain how I set this up inside me query.

Thanks

 

[CraigDolphin]

Order: IIF([Stock]<=[Minimum_Stock],"Y","N")

 

[dnnr]

Without getting into too much detail, you can right click on the Field Row in Design View and if you look towards the bottom, and option called "Build . . ." will be present. Click on it.

You are now in the expression builder. If you are familiar with Excel, this should be similar for you. Where you can type, start off with "Order: "

This puts the name "Order" in the column heading. Now you'll want to use the IIf function, located in the functions folder on the bottom left of the window. It is extremely similar to Excel. Simply click on the columns and symbols you need to use and then set the criteria for when the statement is true or false.

 

[jw1234]

Get an error please help again

Order: IIF([Stock]<=[Minimum_Stock],"Y","N")

I get an error which states
The expression you have entered has an invalid . (dot) or ! operator or invalid parentheses.

You may have entered an invalid identifier or parentheses following the NULL constant

Are you able to give more advice.

Thanks for your help

 

[CraigDolphin]

Try this:
Order: IIF(Nz([Stock],0)<=Nz([Minimum_Stock],0),"Y","N")

I think the problem is that you have nulls appearing in your query in the stock and minimum stock fields. So, this will replace nulls with the 0 value before performing the logical test.

 

[jw1234]

Thanks

Try this:
Order: IIF(Nz([Stock],0)<=Nz([Minimum_Stock],0),"Y","N")

I think the problem is that you have nulls appearing in your query in the stock and minimum stock fields. So, this will replace nulls with the 0 value before performing the logical test.

Thanks for your help, appreciate it!!