RE: [MS_AccessPros] concatenation of nz expressions

 

Hi Crystal

I think you're thinking of Trim$ which would give an error for Null.

Trim(Null) gives Null, which will behave the same way as an empty string in
concatenation.

Best wishes :-)

Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Crystal
Sent: Friday, 24 August 2012 08:29
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] concatenation of nz expressions

Hi Liz,

Trim will have issues if there is nothing to trim ... this will fix errors

Trim(nz( [field],""))

Warm Regards,
Crystal

*
(: have an awesome day :)
*

________________________________
From: Liz Ravenwood <liz_ravenwood@beaerospace.com
<mailto:liz_ravenwood%40beaerospace.com> >
To: "'MS_Access_Professionals@yahoogroups.com
<mailto:%27MS_Access_Professionals%40yahoogroups.com> '"
<MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> >
Sent: Thursday, August 23, 2012 2:04 PM
Subject: RE: [MS_AccessPros] concatenation of nz expressions

The true sum of my experience. :-)

Liz Ravenwood
Programmer/Analyst
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: Thursday, August 23, 2012 11:59 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] concatenation of nz expressions

Weird.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Liz
Ravenwood
Sent: Thursday, August 23, 2012 8:51 PM
To: 'MS_Access_Professionals@yahoogroups.com
<mailto:%27MS_Access_Professionals%40yahoogroups.com> '
Subject: RE: [MS_AccessPros] concatenation of nz expressions

John, this seems to be working:

ProblemAndSuggestion: "Problem: " & Trim([RFC Input]![Problem]) & "
Suggestions:
" & Trim([RFC Input]![Suggestions]) & " WIP: " & Trim([RFC
Input]![WIPNotes])

Liz Ravenwood
Programmer/Analyst
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: Thursday, August 23, 2012 9:57 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] concatenation of nz expressions

Liz-

That shouldn't happen using the & operator. You should at least see
"Problem:
Suggestions: WIP:". There's something else going on. No need to use NZ
when
you use &. Maybe try this:

ProblemAndSuggestion: "Problem: " & CStr([Problem]) & " Suggestions: " &
CStr([Suggestions]) & " WIP: " & CStr([WIPNotes])

That might truncate long memo fields, but at least you'll get something.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Liz
Ravenwood
Sent: Thursday, August 23, 2012 6:44 PM
To: 'MS_Access_Professionals@yahoogroups.com
<mailto:%27MS_Access_Professionals%40yahoogroups.com> '
Subject: RE: [MS_AccessPros] concatenation of nz expressions

John, Wow that was fast.

I was thinking ahead of myself when I said ProblemAndSuggestion is a memo
field
because I'm actually turning around and appending that expression to a field
in
another table called ProblemAndSuggestion. Duh.

However, to just look at the query as a select query, the expression yields
a
blank - or a null.

I'm wondering if I should convert all the null field values to separate
expressions first and then concatenate?

Liz Ravenwood
Programmer/Analyst
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: Thursday, August 23, 2012 9:08 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] concatenation of nz expressions

Liz-

If they were ordinary Text fields, I would at least expect the constant
literals
to show up. Trying to concatenate very large memo fields can be
problematic.

I don't understand your comment that ProblemAndSuggestion is a memo field -
it's
the name of your expression. What is in the Problem and Suggestion and
WIPNotes
fields?

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Liz
Ravenwood
Sent: Thursday, August 23, 2012 5:55 PM
To: 'MS_Access_Professionals@yahoogroups.com
<mailto:%27MS_Access_Professionals%40yahoogroups.com> '
Subject: [MS_AccessPros] concatenation of nz expressions

Pros, I'm wondering why this expression is showing blank:

ProblemAndSuggestion: "Problem: " & nz([Problem],"") & " Suggestions: " &
nz([Suggestions],"") & " WIP: " & nz([WIPNotes],"")

(part of a query)

ProblemAndSuggestion is a memo, and the other fields are also memo fields--
I
believe.

Liz Ravenwood
Programmer/Analyst
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com
This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you
are
not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

------------------------------------

Yahoo! Groups Links

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you
are
not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

------------------------------------

Yahoo! Groups Links

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you
are
not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

------------------------------------

Yahoo! Groups Links

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you
are not the intended recipient, please contact the sender by reply e-mail
and destroy all copies of the original message.

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (16)

Recent Activity:

• New Members 4

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___