Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    14

    Exclamation Unanswered: Manipulating concatenated data

    I am using the function fConcatFld located at http://www.mvps.org/access/modules/mdl0008.htm to concatenate email addresses from a customers table to then send in an Outlook email message. The problem is that many of the records do not yet have an email address in the record. This causes to function to return many unneeded semi-colons like this: ;;;;;;;;;;user@host.com;;;;;;;. Is there some way I could modify the function itself to skip over the records with null values for the Email address field? I was also thinking I could leave the function as is and filter out the unneeded records using the InStr function in a query but I was unsure or the exact syntax of that as I am admittedly a novice at this type of programming. Any help would be GREATLY appreciated. Thanks.

  2. #2
    Join Date
    May 2002
    Posts
    13
    You can edit your function where it finishes loSQL to end up with a query such as:
    SELECT Email
    FROM EmailTable
    WHERE Email IS NOT NULL
    AND Email > ""

    You can change
    loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
    to
    loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ & _
    " AND Email IS NOT NULL AND Email > " & cQ & cQ
    if you want to keep the ability to pass a filter to the function, or
    loSQL = loSQL & " Email IS NOT NULL AND Email > " & cQ & cQ
    if you want all the records.

    Changing the SQL query to return the results you want should be more efficient than using INSTR to filter out data after the records are returned.
    ==========================
    John Lambert
    Team Database
    The International Association Of Database Professionals
    www.TeamDatabase.com
    ==========================

  3. #3
    Join Date
    Jul 2002
    Posts
    14
    John,

    Thanks a lot, that solved my problem perfectly!

Posting Permissions

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