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

If a field is blank, how do I return a different field?

P: 2
I have a CSV file that is imported into Access. I have a TaxpayerCity field that is left empty if the taxpayer and the property owner are the same. Therefore, if "TaxpayerCity" is empty I want the query to return the information in "OwnerCity".

I have put "Expression" in the "Total" of the query.

I tried "IIf([TaxpayerCity] is null,[TaxOwnerCity],[TaxPayerCity])"

Didn't work.

I tried IsNull([ParcelMastertaxpayercity],[Parcelmasterownercity])

Didn't work.

Can't seem to find any information about giving me a different field when the field requested is empty.

Since I am working with real data, and that data is highly confidential, I cannot send any files or screen captures. I supposed it that would be required, I could alter the data with bogus information and send that.

Thanks for your help.
Jan 18 '17 #1

✓ answered by NeoPa

Hi Sheri.

I, too, am confused by one thing not working yet the same format used with different names working.

In case it helps, you can also use Nz() :
Expand|Select|Wrap|Line Numbers
  1. =Nz([MainField],[FailoverField])
This will use [MainField] if it's not null, or [FailoverField] if it is.

@Phil.
As the expression is in a QueryDef which is SQL and resolved by the Expression service, [X] is Null is a perfectly valid way to say it. I'm not sure where Sheri was going wrong but I suspect it was lost in translation from her project onto the post.

@Sheri.
An extra tip is to avoid names that are very long. They tend to confuse. Also, when making up names from multiple words, use capitals to indicate the start of the words which mitigates the problem a great deal. So, [MyVeryLongFieldName] is preferable to [myverylongfieldname], but [MyField] is better still. within any naming standards you have of course.

Share this Question
Share on Google+
5 Replies


PhilOfWalton
Expert 100+
P: 1,430
You were pretty close on your first attempt - all the right words, but not necessarily in the right order

Try
Expand|Select|Wrap|Line Numbers
  1. IIf(Is Null(TaxPayerCity), TaxOwnerCity, TaxPayerCity)
  2.  
Just make sure that there are no "funny characters" like a Carriage return or a line feed in the TaxPayerCity, as this will not work if there are.

Phil
Jan 19 '17 #2

P: 2
Thank you so much. However, this did not work. I found that my error was that I needed to have the by itself then the formula in a different field. Here is what ultimately worked.

http://berriencounty.org/uploaded/pr...cessfields.pdf

Field: parcelmastertaxpayername
Total: Group By

Field: Taxpayer Name: IIf([parcelmastertaxpayername] Is Null, [parcelmasterownersname],[parcelmastertaxpayername])
Total: Expression
Jan 19 '17 #3

Seth Schrock
Expert 2.5K+
P: 2,931
@Phil The IsNull() function is a single word, not Is Null().

@Sheri It looks like all you did was to change the field names. Your post says that you needed to have the ... by itself, but you don't say that "it" is.

Also, for future reference, it helps if you describe in more detail what "Didn't work" means. For example, did you get an error message (always provide if this is the case) or was the result not what you expected? Having this information helps the people helping you to know what to focus on instead of having to guess. Glad you got it figured out though.

Welcome to Bytes and good luck on your project!
Jan 19 '17 #4

NeoPa
Expert Mod 15k+
P: 31,186
Hi Sheri.

I, too, am confused by one thing not working yet the same format used with different names working.

In case it helps, you can also use Nz() :
Expand|Select|Wrap|Line Numbers
  1. =Nz([MainField],[FailoverField])
This will use [MainField] if it's not null, or [FailoverField] if it is.

@Phil.
As the expression is in a QueryDef which is SQL and resolved by the Expression service, [X] is Null is a perfectly valid way to say it. I'm not sure where Sheri was going wrong but I suspect it was lost in translation from her project onto the post.

@Sheri.
An extra tip is to avoid names that are very long. They tend to confuse. Also, when making up names from multiple words, use capitals to indicate the start of the words which mitigates the problem a great deal. So, [MyVeryLongFieldName] is preferable to [myverylongfieldname], but [MyField] is better still. within any naming standards you have of course.
Jan 20 '17 #5

zmbd
Expert Mod 5K+
P: 5,287
If I may, while we're on the topic of field names, or for that fact names for any Access object, Mr. Browne has a fairly exhaustive list of names and symbols one should avoid using:
Problem names and reserved words in Access

personally, I use only alphanumeric and the underscore for names, and - agreeing with Neopa, the shortest descriptive name you can use tend to be better... also, easier to code!
Jan 21 '17 #6

Post your reply

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