In an IIf function in Access, how do I assign null values?

ms-access null calculated-field iif
LM6 picture LM6 · Dec 3, 2015 · Viewed 7.2k times · Source

I'm creating a calculated field (Field3) in a query in MS Access. In this example, Field2 contains both numeric and character values. I want Field3 to contain only numeric values from Field2 and to convert all character values to Null values so that I can later perform calculations on Field3 as a numeric field. This is in an IIf function because I want Field3 to contain only values from Field2 if Field1 = "AA". This is what I tried typing in the Field row in the Query Design View:

Field3: IIf([Field1]="AA",[Field2]*1,NULL)

This works except where Field2 is a character value then Field3 reads "#Error" instead of being blank.

What is the proper syntax for assigning NULL values if the IIf condition is not met?

Answer

HansUp picture HansUp · Dec 3, 2015

If you want the expression to return [Field2]*1 only when [Field1]="AA" and [Field2] contains a number, but otherwise return Null, use IsNumeric() in the condition ...

IIf([Field1]='AA' And IsNumeric([Field2]), [Field2]*1, Null)

If the purpose of [Field2]*1 is to cast the [Field2] text value as a number, consider the Val() function instead ...

IIf([Field1]='AA' And IsNumeric([Field2]), Val([Field2]), Null)

Related questions

How to I say Is Not Null in VBA

Hi I have the following expression. I'm trying to say "if the second field Is Not Null". Can you help. Thanks =Iif((Fields!approved.Value = "N" & Fields!W_O_Count.Value IsNotNull), "Red", "Transparent")

Read More
where to place CASE WHEN column IS NULL in this query

I'm having some trouble translating an MS Access query to SQL: SELECT id, col1, col2, col3 FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.id LEFT OUTER JOIN table3 ON table1.id = table3.id so far so good, …

Read More
How can you check for null in a VBA DAO record set?

I have an optional field in a database that I'm pulling out using a DAO Record Set. I need to check whether or not the field is set before I concatenate it with other fields. So far I have the …

Read More