4

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?

HansUp
  • 95,961
  • 11
  • 77
  • 135
LM6
  • 149
  • 3
  • 11
  • Maybe this is the wrong question to be asking - it appears the NULL assignment is working, but the problem is that when Field2 is character then it returns an error instead of converting to NULL. – LM6 Dec 03 '15 at 22:01
  • The next time you ask a question, please try to include as much information as possible right from the beginning. The `This works except where Field2 is a character value` part *(which was added after I answered the question)* was crucial. – Christian Specht Dec 04 '15 at 23:15

2 Answers2

2

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)
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • This is the answer I needed - thanks! Wasn't quite asking the right question since I wasn't sure at first where the problem was... – LM6 Dec 03 '15 at 22:53
0

Null is the correct syntax.

But at least in the old German Access version which I have on this machine, I need to use semicolons instead of commas in the Query Design View.

So can you try this?

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

In SQL View, the syntax is exactly like in your question:

SELECT IIf([Field1]="aa",[Field2]*1,Null) AS Field3
FROM TestTable;
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • I tried it with semicolons but it gave an "invalid syntax" error. When I run the query with Field3: IIf([Field1]="AA",[Field2]*1,NULL) it works except that instead of NULL values it produces #ERROR as the value. – LM6 Dec 03 '15 at 21:07
  • Can you try switching to SQL View, modifying the `IIf` so that it looks like in my second example? If the query works then, switch back to Query Design View and you should see the correct Query Design view syntax. – Christian Specht Dec 03 '15 at 21:14
  • Hmm....I just googled "ms access iif" and looked at some screenshots of English Access versions, and apparently [commas actually work in the English version](https://www.fmsinc.com/microsoftaccess/query/sort/query-date-sort-calculated-design.jpg) (I get a syntax error when I use commas in my German Access 2010). Which Access version in which language are you using? – Christian Specht Dec 03 '15 at 21:23
  • In SQL view it matches your example but it still returns #ERROR values when it runs. I'm using English Access 2010 on Windows. – LM6 Dec 03 '15 at 21:35