 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2006Null propogation - combining fields
I have two name fields, name1 and name2. Every record has name1, some records have name2. I want a combined field that looks like: name1 or name1 / name2 I've tried "[name1] & ("/ " + [name2])" but when the name2 field is empty I get name1 / This thing is killing me! Any words of wisdom will be greatly appreciated. untested: [name1] & NZ("/ " + [name2]) > I have two name fields, name1 and name2. Every record has name1, some records > have name2. [quoted text clipped - 9 lines] > This thing is killing me! > Any words of wisdom will be greatly appreciated. Thanks for the prompt response - unfortunatley, it gave the same results as my attempts. I'll keep pluggin' away at it... David > untested: > [quoted text clipped - 14 lines] > > This thing is killing me! > > Any words of wisdom will be greatly appreciated. Post your SQL. > Thanks for the prompt response - unfortunatley, it gave the same results as > my attempts. I'll keep pluggin' away at it... [quoted text clipped - 18 lines] > > > This thing is killing me! > > > Any words of wisdom will be greatly appreciated. From your posting I would guess that Name2 is not null but is string with zero to many spaces. Try the following Name1 & IIF(Trim(Name2 &"")="","","/ " & Name2) That will take care of Nulls, zero-length strings, multi-space strings. > I have two name fields, name1 and name2. Every record has name1, some records > have name2. [quoted text clipped - 9 lines] > This thing is killing me! > Any words of wisdom will be greatly appreciated. Thanks, John, and others, The original record field was defined with zero length strings permitted, so my original expression was actually working. Thanks for all the help to an absolutely unabashed Access newbie. I'm learning... David > From your posting I would guess that Name2 is not null but is string with zero > to many spaces. [quoted text clipped - 17 lines] > > This thing is killing me! > > Any words of wisdom will be greatly appreciated. John, That worked well, but I'm a bit forrgy about part of the expression. I used this variation: Name1 & IIF(Trim(Name2)="","","/ " & Name2) and got the same results. What does the extra boolean operator acomplish? Thanks, David Holt aka Poppacat > From your posting I would guess that Name2 is not null but is string with zero > to many spaces. [quoted text clipped - 17 lines] > > This thing is killing me! > > Any words of wisdom will be greatly appreciated. PMFBI there are several functions in Access that will choke on a Null--Trim() is one of them. To make a string field "null-safe" so you can use in your query, one method is to use a *concatenating* ampersand (not boolean operator) with a zero-length string [stringfield] & "" the amperand will ignore any null on the left, and return a zero-length string if it is null. if it is not null, you are still going to get the exact same field string. you lucked out and did not *really* have a field with a null value apparently or your query as you changed it would have choked on the Trim function. > That worked well, but I'm a bit forrgy about part of the expression. I > used [quoted text clipped - 28 lines] >> > This thing is killing me! >> > Any words of wisdom will be greatly appreciated. Sorry, just tested and Trim() does *not* have a problem with Null in Access 2003. astr=null ?trim(astr) Null the use of & "" actually had to do with the boolean result of the expression, IIF(Trim(Name2)="" if Name2 were null. Trim(Name2)="" would be NULL which would be treated as "NOT TRUE" by the IIF By concatenating the zero-length string, IIF(Trim(Name2 & "")="" condition will be TRUE if Name2 is - null -zero-length string -one or more spaces ///////////////////////////////////////// here's a short (incomplete) list of some Access functions that "I think" will choke on null: Rnd() CStr() CCur() Val() Asc() Replace() DateValue() TimeValue() But, maybe I should test them as well..... sorry Gary, Thanks for your kind and patient remarks - one of my employees came to me with the problem (the unwanted "/") while trying to prepare a church members phone directory - after several attempts to construct an effective query in Access, she concluded that her syntax strucxture was somehow defective. After a lengthy period of research, as I don't know Access, I concluded her syntax was correct, but there was something else wrong. Seems as though I've opened a completely new area for future muddling of the thought processs. Thanks for taking the time to help - with the dataset we're dealing with, we seem to have found a solution that works. Regards, David Holt aka Poppacat > Sorry, just tested and Trim() does *not* have > a problem with Null in Access 2003. [quoted text clipped - 46 lines] > > sorry |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.