Insert value in fields on conditions

[noriko]

Hello,

I have a table with 4 fields. I want to insert value into one of the fields, based on If condition of other fields...

Company1, Company2, Company3, NoOfCompanies
xxx,,,
xxx,xxx,,
xxx,xxx,xxx,

I would like to count how many companies (total number of companies) in each row.

So, if Company1 is not empty, and Company2 & Company3 is empty, then I would like to insert "1" into field "NoOfCompan ies"

If Company1 and 2 are not empty, and Company3 is empty, then I would like to insert "2" into field "NoOfCompan ies"

If Company1, 2, and 3 fields are not empty, then I would like to insert "3" into field "NoOfCompan ies"

Is this possible using Macro/VBA in Access? Please help. I was thinking that I need to create a function or procedure? I'm a beginner in VBA.

I'm using MS Windows 2000, MS Access version 2000

Thank you.

[RuralGuy]

Create a Public Function in a standard module and pass it all three fields and let it return a number.

[NeoPa]

A simple SQL solution (practical for only three fields but not too many more) might be :

NoOfCompanies: IIf([Company1] Is Null,0,1) +
               IIf([Company2] Is Null,0,1) +
               IIf([Company3] Is Null,0,1)