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:
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:
Step 2:
Select If from the drop down list:
Step 3:
2 additional text boxes are added:
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:
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”:
Step 4:
After selecting “SetField” 2 new fields are added:
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
Value: The value to replace the field with. In this example it is the text string “N/A”:
Step 5:
Result:
As you can see in the figure below the [First Name] field of the 9th record is empty:
The macro will change its value to “N/A”:
You can download the sample file used in this article from the link below:
See also:
- Access, Add Leading Zeros Custom Number Formats
- Access, Convert User Input to Uppercase
- Access, Convert User Input to Lowercase
- Access Field Properties Locked
- Access, Fixed Decimal Places Number Format
- First Letter Uppercase Only Access Tables
- First Letter Uppercase Only Access Form
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