Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Posts
    127

    Unanswered: Concatenating two fields

    I'm joining to fields together - Surname and Initials,

    Which are seperated by a "," (comma).

    But if there is no Surname or Initials present, it will still leave a "," in the field... I dont want it to do this.

    Please help

    Staff: ([Surname]) & ", " & ([Initials])

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Either one could be NULL or empty?

    SELECT staff: ([Surname]) & Iif(Nz([Surname], "") = "" OR nz([Initials], "") = "", "", ", ")
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2007
    Posts
    127
    Quote Originally Posted by Teddy
    Either one could be NULL or empty?

    SELECT staff: ([Surname]) & Iif(Nz([Surname], "") = "" OR nz([Initials], "") = "", "", ", ")
    I have tried the query, but it doesnt fully work...

    It doesnt show the "," in the blank fields.. which is great, exactly what I need...

    But it does show the Initials... all with it.. only the Surname followed by the ","

    Thanks in advance

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    oh, I missed a bit

    SELECT staff: [Surname] & Iif(Nz([Surname], "") = "" OR nz([Initials], "") = "", "", ", ") & [Initials]
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Oct 2007
    Posts
    127
    Mate you a STAR...

    My hat goes off to you..

    thanks a lot


  6. #6
    Join Date
    Oct 2007
    Posts
    127
    Got another similar query to set up... instead of me creating a new post... thought I just add it to this one... as its similar

    I have a Date field (09/07/2007)

    I have created a new query where is only show the year, where it is grouped by the financial year... hope the query makes more sense!!!

    Year: IIf(Mid([DateofVisit],4,2)>"03",Right([DateofVisit],4) & "/" & Right([DateofVisit],4)+1,(Right([DateofVisit],4)-1) & "/" & Right([DateofVisit],4))

    It returns 2006/2007 which is great... Again, if the field is blank... I dont want it to leave a "/" in it

    Thanks in advance
    Last edited by akhlaq768; 10-18-07 at 06:20.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Simple, just apply the exact same logic using nz()
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Oct 2007
    Posts
    127
    I've tried the following code -

    Year: IIf(Mid(Nz([DateofVisit]),4,2)>"03",Right(Nz([DateofVisit]),4) & "/" & Right(Nz([DateofVisit]),4)+1,(Right(Nz([DateofVisit]),4)-1) & "/" & Right(Nz([DateofVisit]),4))

    but I get a "#Error" in the blanks

  9. #9
    Join Date
    Oct 2007
    Posts
    127
    I'm still getting errors.. please can anyone help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •