How to crop a leading blank space resulting from a query

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

Guest

If I create a query field such as this...
NewField: [Field1] & " " & [Field2]

then NewField will have a leading blank space for each record with a null
value in Field1.
How do you clear the space?
(I think I have seen once before a function that does this, but I have
searched Help under all the key words I can think of, and can not track it
down again.)
 
Hi, Peter.
How do you clear the space?
Click to expand...

Try:

NewField: Field1 & IIF(NZ(Field1, "") = "", "", " ") & Field2

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


Peter K said:
If I create a query field such as this...
NewField: [Field1] & " " & [Field2]

then NewField will have a leading blank space for each record with a null
value in Field1.
How do you clear the space?
(I think I have seen once before a function that does this, but I have
searched Help under all the key words I can think of, and can not track it
down again.)
Click to expand...
 
If I create a query field such as this...
NewField: [Field1] & " " & [Field2]

then NewField will have a leading blank space for each record with a null
value in Field1.
How do you clear the space?
(I think I have seen once before a function that does this, but I have
searched Help under all the key words I can think of, and can not track it
down again.)
Click to expand...

There's a sneaky trick to do this which takes advantage of NULL
propagation: both the & and the + operators concatenate strings, but &
treats a NULL as a zero length string, and + returns NULL if either
argument is NULL. So try:

NewField: ([Field1] + " ") & [Field2]

If Field1 is NULL the parenthetical expression will be NULL.

John W. Vinson[MVP]
 
Back
Top