By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,334 Members | 2,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,334 IT Pros & Developers. It's quick & easy.

Populating Column that has some blank fields

P: 9
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
Mar 8 '07 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,359
you can't use Is Null and Is Not Null in VBA, you have to use the function:
IsNull()
Mar 8 '07 #2

P: 9
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.

Expand|Select|Wrap|Line Numbers
  1.  Address: If IsNull([Street Address]) Then [Address] = [Mail Address]  Else [Address] = [Street Address]  
Any more suggestions?

Thanks
Jim
Mar 8 '07 #3

Rabbit
Expert Mod 10K+
P: 12,359
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.
Expand|Select|Wrap|Line Numbers
  1. iif(Condition, Value if True, Value if False)
Mar 8 '07 #4

P: 9
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
Mar 8 '07 #5

Rabbit
Expert Mod 10K+
P: 12,359
Not a problem, good luck!
Mar 8 '07 #6

NeoPa
Expert Mod 15k+
P: 31,434
There's a better function for your particular requirement.
Nz().
You can do the whole job in one go.
Expand|Select|Wrap|Line Numbers
  1. Address: Nz([Street Address],[Mail Address])
Mar 9 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.