=IIf("jul"<"[Total Stock Qty]","ALERT","GOOD")

A

Andy97

I have a form based on a query the pulls data from a table that has
months and numeric data associated to that particular month.

(e.g) [Jan] may have a value of 302, [Feb] may have a value of 107,
etc.

On my form I have a [Total] Numeric value that I want to test against a
specific month. I'd like to be able to test for the current month if
possible but I'll keep it simple.


What I am trying to say below is if the text field 'jul' is less than
the value in [Total Stock Qty] then I want my unbound field to display
ALERT. else GOOD

I have an unbound form field with the following control source.:
=IIf("jul"<"[Total Stock Qty]","ALERT","GOOD")

The problem is the value for my 'jul' text box is less than [Total
Stock Qty] but my unbound box still displays GOOD.
 
W

Wayne Morgan

In your IIf statement you are asking if "jul" is less than the string
"[Total Stock Qty]". The ASCII value for [ is 91 and the ASCII value for j
is 106, so it is giving you the corret result because the comparison is
False.

Try this syntax:
=IIf("jul <" & [Total Stock Qty],"ALERT","GOOD")
 
A

Andy97

Hi Wayne
Thanks for the reply but still no dice!

'jul' is a text form field that contains a numeric value but using your
code validates to ALERT all the time. Both the textbox 'jul' and Total
Stock Qty contain numeric values.
 
T

Tom Lake

Andy97 said:
I have a form based on a query the pulls data from a table that has
months and numeric data associated to that particular month.

(e.g) [Jan] may have a value of 302, [Feb] may have a value of 107,
etc.

On my form I have a [Total] Numeric value that I want to test against a
specific month. I'd like to be able to test for the current month if
possible but I'll keep it simple.


What I am trying to say below is if the text field 'jul' is less than
the value in [Total Stock Qty] then I want my unbound field to display
ALERT. else GOOD

I have an unbound form field with the following control source.:
=IIf("jul"<"[Total Stock Qty]","ALERT","GOOD")
Click to expand...

If jul is a field name, you have to do this:

=IIf([jul]<[Total Stock Qty],"ALERT","GOOD")

Tom Lake
 
A

Andy97

Thanks Tom!

That's what I would have thought as well but it just doesn't work. I
have even tried it with unbound fields.
 
W

Wayne Morgan

Ok, if you are storing numbers as text, they won't sort in numeric order and
your < won't be what is expected. I understand that both Jul and [Total
Stock Qty] are controls on the same form as the control you're using this
control source in. If they aren't on the same form, a different syntax will
be needed. Even if one or more of them is on a subform (or if this is a
subform, one or more of them is on the main form or a different subform) the
syntax will need to be adjusted.

To get the comparison treated as a number, you'll need to use a conversion
function (CInt, CLng, CDec, etc) appropriate for the type of number
contained. Also, if the control and the field it is bound to have the same
name, you may get an error. For example, if the control Jul is bound to a
field also called Jul, you may get an error. If so, change the name of the
control to txtJul, then try this:

=IIf(CLng([txtJul]<CLng([txtTotalStockQty], "ALERT", "GOOD")

I've adjusted both names, assuming you had to change the name of both
textboxes. Use the actual names you used.
 
A

Andy97

Hi Guys!
Thanks for the help!

Finally resolved the issue.

What I had to do was create the two fields as unbound and then set them
to 'general numbers' and then this worked:
=IIf([jul]<[Total Stock Qty],"ALERT","GOOD")
 
W

Wayne Morgan

I went back and looked and I was missing the closing parentheses on the
CLng() functions. It should have been:

=IIf(CLng([txtJul])<CLng([txtTotalStockQty]), "ALERT", "GOOD")
 
Top