Access Check If Field is Null, Conditional Macro

This article explains how you can create a conditional macro that will check if the value in a field is Null or not. If the value is null, it will set the field with a default value.

Consider the database below:
Sample Database
We want to create a macro that will check the value of the field [First Name]. If the field is empty, it will fill it with the value “N/A”.


Contents

Step 1:

Select the table you want to apply the macro to from the Navigation Pane on the left. Click on the Before Change button on the Table ribbon:
Table Ribbon


Step 2:

Select If from the drop down list:
If


Step 3:

2 additional text boxes are added:
Next

Conditional Expression: In this field you can create a conditional expression. If it returns true the action specified in the next field will be executed. For this example we will use the conditional expression below:

IsNull([First Name])

It checks if the value in the first field is null or not:
Conditional Expression
Add New Action: This field determines the action that will take place if the conditional expression in the previous field returns true. From the drop down list select “SetField”:
Add New Action


Step 4:

After selecting “SetField” 2 new fields are added:
Set Field

Name: The name of the field to modify. In this example we want to modify the [First Name] field when it is null, therefore input the value below in this field:

Students.First Name

Name
Value: The value to replace the field with. In this example it is the text string “N/A”:
Value


Step 5:

Save and close the macro:
Save and close


Result:

As you can see in the figure below the [First Name] field of the 9th record is empty:
Empty Field
The macro will change its value to “N/A”:
NA

You can download the sample file used in this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *