Populating Column that has some blank fields

[erajim]

Hello again! Everyone was so helpful last time, and I can feel so dumb sometimes.
Basically I am trying to create a new field with the value from field [Street Address] if it is present, if it is blank then populate it with the value from the [mailing address]. I tried an if then statement but couldn't get it to work.

[code] Address: If [Street Address] is null then [Mail Address] Else if [Street Address] not null then [Steet Address] [code]


Thanks

[Rabbit]

you can't use Is Null and Is Not Null in VBA, you have to use the function:
IsNull()

[erajim]

thanks Rabbit for you assistance. However, I am trying to do this in the sql view not in vb. I tried the isnull but got a syntax error. Is that because the isnull is not usable in the sql view in access? I tried it with an endif and still got the syntax error.

 Address: If IsNull([Street Address]) Then [Address] = [Mail Address]  Else [Address] = [Street Address]  

Any more suggestions?

Thanks
Jim

[Rabbit]

Oh, I shoulda caught that. Then the problem is not the Is Null / Is Not Null.

SQL can't use the If Then Else Structure. You have to use the iif() function.

iif(Condition, Value if True, Value if False)

[erajim]

Rabbit,
That worked! It's funny after your previous post didn't work I happened to look at another post you made where someone used the IIF function. so I tried it and it worked. So you actually helped me before your last post!

I always seem to forget about the IIF function.

Thanks for you help!!!!!!!!!!!!!
Jim

[Rabbit]

Not a problem, good luck!

[NeoPa]

There's a better function for your particular requirement.
Nz().
You can do the whole job in one go.

Address: Nz([Street Address],[Mail Address])