If formula syntax for if field is blank
Hi
I have a formula rich text field to help indicate if a number added in the "Measured" field next to it is within the desired criteria. It looks like this:
This is the example I found in the QB formula support that I used to create my formula:
If([Total]>=100 and [Total]<250, 25,
[Total]>=250 and [Total]<1000, 50,
[Total]>=1000, 100, 0)
If([Moisture Content]>=[Abrasive - Moisture threshold of Current Incoming inspection], "<div style=\"color:white; background-color:#FF0000;\"> The sample is out of spec!</div>",
[Moisture Content]<[Abrasive - Moisture threshold of Current Incoming inspection], "Sample is within the acceptable range of <" &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%",
"The acceptable range is <" &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%")
The problem is that when the field is blank, instead of getting what I want, I get this:
It doesn't seem to recognize that the field is blank. I realize in the QB example that there there could be other numbers (i.e. anything <100) in the field that would trigger the "0" whereas my formula covers all real numbers and is only looking for the lack of data.
Is there some way to instead write for if the field is = blank? I tried using "ISNULL" in my formula, but either I didn't do it correctly or it just didn't work. How can I make this a correct statement: If[field]="blank"
The point is I want the people entering the data to first see what the threshold is supposed to be. However, I don't really want them to have to think about whether or not it is within the spec, it's designed to tell them if it is. That's why there's the warning if it isn't. (Note: regardless of whether the data is out of spec, it needs to be recorded. It's part of QA documentation to determine if material needs to be rejected.)
A secondary issue is that I couldn't seem to figure out how the correct syntax for "The sample is out of spec!" so that it would also contain the desired criteria:
"The sample is out of spec! It should be < " &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%"
For some reason it didn't sit well with the color coding, but I'm not sure why.
Any suggestions would be much appreciated. If I should be going about this a different way, do let me know. It did cross my mind to try form rules for warnings instead. The challenge is the moisture specifications come from a different table that is the "grandchild" to this table.
------------------------------
Kim Gardner
------------------------------
I have a formula rich text field to help indicate if a number added in the "Measured" field next to it is within the desired criteria. It looks like this:
This is the example I found in the QB formula support that I used to create my formula:
If([Total]>=100 and [Total]<250, 25,
[Total]>=250 and [Total]<1000, 50,
[Total]>=1000, 100, 0)
If([Moisture Content]>=[Abrasive - Moisture threshold of Current Incoming inspection], "<div style=\"color:white; background-color:#FF0000;\"> The sample is out of spec!</div>",
[Moisture Content]<[Abrasive - Moisture threshold of Current Incoming inspection], "Sample is within the acceptable range of <" &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%",
"The acceptable range is <" &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%")
The problem is that when the field is blank, instead of getting what I want, I get this:
It doesn't seem to recognize that the field is blank. I realize in the QB example that there there could be other numbers (i.e. anything <100) in the field that would trigger the "0" whereas my formula covers all real numbers and is only looking for the lack of data.
Is there some way to instead write for if the field is = blank? I tried using "ISNULL" in my formula, but either I didn't do it correctly or it just didn't work. How can I make this a correct statement: If[field]="blank"
The point is I want the people entering the data to first see what the threshold is supposed to be. However, I don't really want them to have to think about whether or not it is within the spec, it's designed to tell them if it is. That's why there's the warning if it isn't. (Note: regardless of whether the data is out of spec, it needs to be recorded. It's part of QA documentation to determine if material needs to be rejected.)
A secondary issue is that I couldn't seem to figure out how the correct syntax for "The sample is out of spec!" so that it would also contain the desired criteria:
"The sample is out of spec! It should be < " &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%"
For some reason it didn't sit well with the color coding, but I'm not sure why.
Any suggestions would be much appreciated. If I should be going about this a different way, do let me know. It did cross my mind to try form rules for warnings instead. The challenge is the moisture specifications come from a different table that is the "grandchild" to this table.
------------------------------
Kim Gardner
------------------------------
