Null propogation - combining fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Thanks for the prompt response - unfortunatley, it gave the same results as
my attempts. I'll keep pluggin' away at it...
David

JethroUK© said:
untested:

[name1] & NZ("/ " + [name2])



Poppacat said:
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.
Click to expand...
Click to expand...
 
Post your SQL.

Poppacat said:
Thanks for the prompt response - unfortunatley, it gave the same results as
my attempts. I'll keep pluggin' away at it...
David

JethroUK© said:
untested:

[name1] & NZ("/ " + [name2])



Poppacat said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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

John Spencer said:
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.
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.
Click to expand...
Click to expand...
 
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

John Spencer said:
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.
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.
Click to expand...
Click to expand...
 
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.



Poppacat said:
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

John Spencer said:
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.
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top