How do I write a VB Function in Access testing for Null & Strings

[Guest]

I am need to use conditional formatting in an Access Report based on four
values. I have tried writing a module but I cannot get passed a Null value
error message.
The four fields are PrimaryFirst, PrimaryLast, SecondaryFirst, SecondaryLast.
This is what I am trying to do:
IF SecondaryFirst AND SecondaryLast are Null (no data in these fields) than
Name=PrimaryFirst + PrimaryLast
IF SecondaryLast AND PrimaryLast are EQUAL than
Name = PrimaryFirst + "and" + SecondaryFirst + PrimaryLast
or
IF SecondaryFirst AND PrimaryFirst are NOT EQUAL than
Name = PrimaryFirst + PrimaryLast + "and" + SecondaryFirst + SecondaryLast

Any help would be greatly appreciated!

 

[Larry Linson]

I am need to use conditional formatting in an
Access Report based on four values.

"Conditional Formatting" is, in Access terms, a property of a Control on a
Form or Report that affects _how_ that Control is displayed based on various
logic -- that is not what you describe.

What you describe is calculating the content of a Control, arithmetically
and logically... if the fields are numeric, the + sign is the arithmetic
operator; if they are text, then it has a special meaning, and one of your
choices does present it as text...

"Name" is an Access reserved word, so is not a good choice for the name of a
Control or a Field -- can be confusing.

You test for Null using the IsNull builtin function. Or you can handle Nulls
using the NZ (Null-to-Zero) function which returns 0 for a Null Numeric
Field and the empty string "" for a Null Text Field.

Please clarify the type of the Fields in the Table.

Larry Linson
Microsoft Access MVP

I have tried writing a module but I cannot get passed a Null value

 

[Ian]

You can avoid the null problem by appending "" to any field that might turn
up a null. So

You would go:
IF SecondaryFirst & "" <> PrimaryFirst & "" THEN

Ian

 

[lewie]

NZ function does this
NZ(data,replacementdata)
if it is null it will replace the data with the replacement data else
it will let the data pass