Calculated Fields - Iif IsAlpha (1 Viewer)

[kyoch]

Hello All,

I have a calculated field which is based off a text field which can contain text values and numeric values. I'd like to add a statement in the criteria which performs some action if the value is text. IsNumeric is not a function I can use in expression builder so I'm thinking of some variation of Left should be used.

Here's what I'm wanting...

Iif(Left([TestResult],1)=alpha,"Pass","Fail")

This way, the calculated field will display pass for any values where the first character is an alpha. I can perform this action fine in a query using Not IsNumeric but I need this calculated field to do it for me.

Any suggestions?

 

[pr2-eugin]

Would this not work for you?

IIF(IsNumeric(Left([TestResult],1)), "Fail", "Pass")

 

[SmallTomato]

Something like this?

Iif(IsNumeric(TestResult)=TRUE,"Fail","Pass")

 

[kyoch]

IsNumeric is not a valid expression I can use in a calculated field

 

[pr2-eugin]

IsNumeric is not a valid expression I can use in a calculated field

Say what ??

 

[kyoch]

Try creating a calculated field in a new table real quick. IsNumeric cannot be used in a calculated column!

 

[pr2-eugin]

Try creating a calculated field in a new table real quick. IsNumeric cannot be used in a calculated column!

Calculated field in a table !

You are not supposed to store calculated values in tables. This is breaking one of the fundamental principle of DB Design. Read Allen Browne's take on this issue : http://allenbrowne.com/casu-14.html

 

[kyoch]

I'm using this field in a Temporary Table so it's almost like a query lol.

I have a calculated field in my temporary table then some code which puts the values into a permanent table. So my calculated field just calculates whether something passes or fails then stores "Pass" or "Fail" in a separate table. Every time I load the temporary table the values are deleted and it's requeried.

I might not have explained that well enough but is that still breaking the principle of storing calculated fields in a table?

 

[pr2-eugin]

How is the main table updated from this temporary table? You can use the expression there !

 

[kyoch]

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tResults (ProductName, TestName, LotNumber, TestingDate, Technician, TestResult, PassFail) " _
& "SELECT '" & Forms!fMain.Product & "', tTempTblResult.TestName, """ & Forms!fMain.LotNo & """,#" & Forms!fMain.TestingDate & "#, '" & Forms!fMain.Technician & "', tTempTblResult.TestResult, tTempTblResult.PassFail " _
& "FROM tTempTblResult"
DoCmd.SetWarnings True

 

[pr2-eugin]

You are still storing a Calculation based on another field. Why? You do not need to.

 

[kyoch]

Ok, I guess will revisit the idea of storing that value.

 

[gemma-the-husky]

the only thing to beware of, as we have seen in some recent threads, is that certain letters (eg D and E) are legitimate within some number formats - so testing isnumeric may still not filter these out. (and they are hard to dig out by normal means!)

 

[pr2-eugin]

Dave, I have been playing with this. If the argument passed to the IsNumeric function is a String, then E is considered as a String rather than a HEX value. If it is passed as not a Formatted String then it returns True.

? IsNumeric([COLOR=Red][B]E[/B][/COLOR])
[COLOR=Red][B]True[/B][/COLOR]
? IsNumeric([COLOR=Blue][B]CStr(E)[/B][/COLOR])
[COLOR=Blue][B]False[/B][/COLOR]
? IsNumeric([COLOR=Blue][B]"E"[/B][/COLOR])
[COLOR=Blue][B]False[/B][/COLOR]

However with Number (Numerals 0-9) If you pass them as String they still are validated for Numbers.

? IsNumeric(2)
True
? IsNumeric(CStr(2))
True
? IsNumeric("2")
True

You always can use a UDF to get the ASCII comparison, with optional inclusion of HEX value.

So given the above scenario, if the OP uses Left$ function as opposed to Left we can be rest assured the return will be a String, so the right result is obtained.

My 2 pennies worth !