Access Tip of the Day

Conditionally include static text when concatenating data (97/2000/2002)

When you concatenate fields in a query, you may also include static characters. For instance, you may add a comma between city and state data or combine data from two fields and enclose the second field's data in parenthesis. Unfortunately, using the concatenation operator "&" alone causes the text to appear even when the second field is Null, which you may not want. To work around this, use the IIF() function, which takes the form:

IIF(condition, result if yes, result if no)

For example, let's say that you use the following as the Field description:

Field_XY: IIf([Field_Y] Is Not Null, [Field_X] & " (" & [Field_Y] & ")", [Field_X])

If Field_X = "Hello" and Field_Y = Null, then Field_XY = "Hello"

However, if Field_X = "Hello" and Field_Y = "to all", then Field_XY = "Hello (to all)"

(Content provided by Element K Journals)

(Note: Your browser is set to refuse cookies. As a result, you may frequently see previously-viewed tips)
PrintPrint CloseClose

Copyright © 2001 - 2023 MJ Technologies, LLC.  All rights reserved.