UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Isnull Function Multiple Fields, Any Version    
 
   
likajoho
post Apr 15 2017, 07:34 AM
Post#1



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


I am concatenating text in 4 fields with varying separators and often blank fields. So my question is, can you have multiple fields in an IsNull function and if so how?

Difficulty, if the preceding fields are blank, then I don't need a separator, but if one or more of the fields is blank, then I do. So that is why I am using the IsNull function. Perhaps there is a better way?

Thank you for reading my post. Any suggestion appreciated, but would like to keep this in a simple query and not use VBA, etc., if possible.
Go to the top of the page
 
likajoho
post Apr 15 2017, 08:07 AM
Post#2



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


I was able to answer my own question through a web search. So this is for someone else asking the same question:

Yes you can have multiple IsNull expressions. Here is the syntax: "isnull([Field1])and isnull([field2])...

In my case they were nested in an Iif expression so entire formulas looked something like this:

<<Code: ConcatenatedField: iif(isnull([Field1]),"",[Field1])&iif(isnull([Field2]),"",iif(isnull([Field1]),[Field2],"; "&[Field2]))&iif(isnull([Field3]),"",iif(isnull([field1])and isnull([Field2]),[Field3],"; "&[Field3]))>>
Go to the top of the page
 
ScottGem
post Apr 15 2017, 08:40 AM
Post#3


UtterAccess VIP / UA Clown
Posts: 32,181
Joined: 21-January 04
From: LI, NY


Try using a + instead of the & as your concatenation character. The + deals with nulls differently.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
moke123
post Apr 15 2017, 08:45 AM
Post#4



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



i would also look into the "Law of Propagating Nulls" Which is basically a null plus anything is null.
for instance concatenating a name you would use (FirstName + " ") & (MiddleName + " ") & (LastName) & (" " + Suffix)
so in this case if there were no middle name or suffix the space that is added(+) would not be added to the string.

heres one reference ....nulls and their behavior
Go to the top of the page
 
likajoho
post Apr 15 2017, 08:47 AM
Post#5



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


Another Option I found by accident:

Copy the unique ID and the fields to be concatenated in the order you want them to be concatenated into Excel. Use the TextJoin function. This only works though if the delimeter is the same for each fields being concatenated.

Then import the Excel table into your Access database and use an update query to populate your new field for the concatenated text.


Option 2 might also be helpful in some circumstances. You can copy and paste the data from Excel to Word where you can use search and replace to manipulate the data. Then copy and paste back to a new column in Excel.

Of course these options are for one time use when you are first setting your table up from existing data, not something you need to do as you add new data. For that you would need to find an Access function or use VBA to make it feasible.
Go to the top of the page
 
likajoho
post Apr 15 2017, 09:02 AM
Post#6



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


Re LOPN, I remembered something like that from quite some time ago, but could not remember which database I used it in. Nonetheless, I don't think it would have worked here as I had too many variables. My delimeter changed depending on whether or not the previous field was blank and even the subsequent field was questionable. I suppose I could have combined Iif function with your suggestion if I had remembered how to do it.

In either case, I am now going to add all of these to my personal tip list. Remembering what I have done before is not my forte, especially when I go months and even longer without digging into changing or creating queries.

Thank you for your suggestion.
Go to the top of the page
 
likajoho
post Apr 15 2017, 09:09 AM
Post#7



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


Wish I had remembered that. I've added it to my personal tips document. I know I'll need it again some day.

Thanks!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 05:50 PM