Re: Concatenation

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 03/11/04 

Date: Thu, 11 Mar 2004 16:42:42 -0700

On Thu, 11 Mar 2004 14:06:06 -0800, "BradleyJ"
<anonymous@discussions.microsoft.com> wrote:

>I have a query that returns the following fields - First_Name, MI, Last_Name,Suffix, Address_1, Address_2, Address_3, as well as others.
>I want to concatenate the name fields and address fields. I created an IF statement that look like is should work but its not. Now there may not be data in several of the fields. When I use my IF I get syntax, oerpand errors. I since have made 3 of the 4 name fields required entry, but that leaves the address fields. What I want is that if Add_3 is null give me Add_1, Add_2, If Add_2 is null I just want Add_1, ia all three have valuse I want all three. I hope this is clear. For the life of me I can't figure out where I'm going wrong. Please help, this is urgent

There's actually a very sneaky and implementation-dependent way to do
this, but it works so well I use it routinely anyway. The & and +
operators both concatenate text strings - but the + operator returns
NULL if either string is NULL, and & treats a NULL as a zero-length
string. This lets you put punctuation between the components that
you're concatenating without having annoying extra blanks or commas:

[First_Name] & (" " + [MI]) & (" " + [Last_Name]) & (", " + [Suffix])
& (", " & [Address_1]) & (" " + [Address_2]) & (" " + [Address3])

                  John W. Vinson[MVP]
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Relevant Pages

  • Re: Message for Duane Hookum re Concatenate
    ... Function Concatenate(pstrSQL As String, _ ... >>Doug Steele, Microsoft Access MVP ... >>> in Duanes code? ... >>>>> Concatenate code which Ive used successfully on a ...
    (microsoft.public.access.modulesdaovba)
  • Re: Excel needs to expand text concatenation capability
    ... Optional DelimitWith As String) As String ... > Optional DelimitWith As String) As String ... > Dim Cel As Range ... >> I would love to be able to concatenate any non-blank entries from a list. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: append memo fields together sequentially
    ... Did you go to the site and download the database and then extract the function and paste it into a module in your database. ... did you save the module with a name OTHER than Concatenate. ... Optional pstrDelim As String = ", ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.queries)
  • Re: Concatenate Function
    ... I am attempting to use Duane Hookom's concatenate function (reproduced ... Function Concatenate(pstrSQL As String, Optional pstrDelim As String = "; ... Dim rs As DAO.Recordset ... Dim strConcat As String 'build return string ...
    (microsoft.public.access.modulesdaovba)
  • Re: Number Formatting in VBSCRIPT
    ... >>>One approach is to concatenate enough zeroes in front of the target ... >would seem to probably save many nanoseconds and is a better approach if you ... Scripts are generally ... >between numeric and string data types. ...
    (microsoft.public.scripting.vbscript)