Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Unanswered: Creating concatenated eMail "TO" string

    I want to generate an eMail “TO” string for use in bulk eMails. My ISP won’t accept eMails with too many addresses, so I decided to split them up by alphabet. Eventually I will implement this totally within Access, but for now I want to create a Form with the results so I can copy/paste the “TO” line into my mail client. The Form I’m trying to create will have text boxes each with the string of addresses for a character of the alphabet.
    e.g. John Abbot <jabbot@some.com>; Susan Addams <suzieq@another.net>; etc.

    I’m having difficulty concepting how to do this. The data source is an existing query that combines several tables, sorts, and selects a list of people and valid activity. I’ve used Duane Hookom’s 2003 Concatenate function elsewhere in the application and it looks like the perfect fit for this. The code I’m using is:

    Code:
    Concatenate(“SELECT DISTINCT FirstName & chr(32) & LastName & chr(32) & chr(60) & PrimaryEMail & chr(62) FROM qPersonThisYrQuery WHERE LastName LIKE ‘A%’ “, ‘; ‘)
    So far I’ve tried using this statement directly as the source for the text box. When that didn’t work I tried putting the statement as a calculated field in a sub-query based on the main one. When I run the query, I get a Run-time Error 3061, too few parameters, expected 2. Debug points to the line “set rs = db.OpenRecordset (pstrSQL) of Duane’s concatenate function. I also get the message “Expression typed incorrectly or is too complex”.

    Can anyone see what is causing the error? Or tell me a better way to build the “TO” string?

    Pete Townsend
    PGT

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is your sql valid?
    have you examined the SQL at runtime to make certain its doing what you think its doing.

    generallyt its better to assign the SQL to a varaible and then use that variable to do the dirty deed later on.

    too few variables usually means you are calling a function with to fewe variables (no shit sherlock I hear you say). but that coudl be cuased by a missing or wrongly placed text delimiter, or date delimiter or a missign parameter (value).

    as I don't know duane hookoms concatenate function its possible thats where the problem lies. you may need to modify the function to use variant or memo datatype as it looks ,like it chokes on a string thats more than 255 characters long
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    Thanks, jaded developer
    Taking your comments in reverse order - I know Hookom's function works as I'm using it elsewhere in the mbd with no problems. The SQL string being passed to the function is less than 255, so I don't see that as a problem.
    [On the next item-Sherlock agrees] I've checked syntax and delimiters until I'm cross-eyed (which may be the problem) and can't see anything. I was hoping that someone else had used this function and would spot the problem. I've tried simplifying the SQL statement by creating a new variable in the query to do the "name <emailaddr>" formatting.
    In Hookom's function the SQL string "SELECT DISTINCT...FROM...WHERE...LIKE" is passed as the (pstrSQL) part of the rs = db.openRecordset (pstrSQL) and it is that openRecordset action that seems to be the first problem.
    As for examining the SQL at runtime, I'm not sure how to do that. I've got the function as the source equation for a text box in a form.

    Knowing enough to be dangerous to myself and others, I remain,
    Pete T. (confused)
    PGT

  4. #4
    Join Date
    Aug 2006
    Posts
    126
    Healdem,
    Another thought is that the source is a query, rather than a table. I'm in the process of searching dbforums on the word concatenate and see a comment on Allen Brown's routine (almost identical in concept to Hookom's) that since a query is really an SQL SELECT and the concatenation is also an SQL SELECT referencing results of the first SQL that there is a possibility for recursive endless loops. Every example I've seen of any Concatenation function sources data directly from a table, not a query, especially one like mine with 4 Left inner joins etc. The other idea is also from Allen, that Access doesn't like to return field results >255 characters.

    Help, I'm over my head, said the drowning man
    PT
    PGT

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure that it will solve the problem, but I have some suggestions.

    1. The simplest, the best. Functionally this:
    Code:
    “SELECT DISTINCT FirstName & chr(32) & LastName & chr(32) & chr(60) & PrimaryEMail & chr(62) FROM qPersonThisYrQuery WHERE LastName LIKE ‘A%’ “
    is equivalent to:
    Code:
    "SELECT DISTINCT FirstName & ' ' & LastName & ' <' & PrimaryEMail & '>' FROM qPersonThisYrQuery WHERE LastName LIKE 'A%'"
    Personally I prefer the second form that I find more readable.

    2. If the query is supposed to work with JET, 'A%' should be 'A*'.

    3. I don't know what Concatenate is nor how it works (well, I can guess!), but if it's a VBA function receiving 2 parameters of type String (or type Variant, subtype String), the syntax is:
    Code:
    [Function Name][Left Parentheses][Double Quotes][First String Expression][Double Quotes][Comma][Space][Double Quotes][Second String Expression][Double Quotes][Right Parentheses]
    or:
    Code:
    FunctionName("String1", "String2")
    then in your case:
    Code:
    Concatenate("SELECT DISTINCT FirstName & chr(32) & LastName & chr(32) & chr(60) & PrimaryEMail & chr(62) FROM qPersonThisYrQuery WHERE LastName LIKE 'A%' ", "; ")
    4. I suppose that it's a problem related to character coding, code pages, text processor or something like that, but in the excerp of code you posted the single and double quotes are replaced by their typographic equivalent (, and ). These characters will not work in SQL JET nor in VBA.
    Have a nice day!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I actually wonder if its a problem with the return datatype from the concatenate function blowing some internal limit, changing the return datatype from the concatenate function may make a difference as suggested in #2

    it would be easier to help if you could set a debug watch and examine where the code goes kaboom, examining any variable to make certain they are correct / appropriate
    ms access debugging - Google Search

    this sort of problem could be caused by any number of issues. you could have a string delimiter in your data stream which throws the runtime
    you could have a compilation error (easy enough to prove tools | run | compile code)

    As to whether it should be A% or A* depends on whether ANSI SQL mode is turned on, personally I prefer to use ANSI mode, but thats just me.

    so you need to hone your debugging skills
    prove you've got some records to process
    prove the resultant variable is being built correctly.
    Im assuming your call to concatenate is to handle missing or null data in your tables.

    if your ISP limits you to to the number of emails sent per block then it woudl make more sense to grab that number per block as you don't neccesarily know if you have more than say 50 whose surname starts with A. or you could "just" grab the first 50 for each letter, and if there are more than 50 return to blocks (50 being an arbitary number that should match the limit set by the ISP)
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2006
    Posts
    126
    Healdem,

    Thanks for the hints. My original statement had quoted characters but I changed them to chr(?) as one of the trials. I agree '> ' is more readable and I've already switched back.

    Also thanks for the A% vs A* hint; I'm using basic Access so I imagine that is one of the problems.

    Concatenate function takes 2 strings Concatenate("SQL_Select_Str", optional "delimiter"). I assumed that "???" and '???' would be treated the same. Are you saying that you can only use ' ' inside a " " string, not as the primary string designator?
    [btw, what ended up in the code box was copied out of a MSWord draft of the message so the typographic quotes are not what is in the proc.]

    Most important, thanks for the ref to debugging info. I have not used it a lot and I need to study up on it and make use of some of those tools. One question, the tutorial refers to working in an IDE window. Am I automatically in it when I'm working on the mdb?

    PT
    PGT

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Are you saying that you can only use ' ' inside a " " string, not as the primary string designator?
    Yes precisely.
    Code:
    "This is a string argument with 'something in quotes' in it"
    Or more useful:
    Code:
    strSQL = "SELECT * FROM MyTable WHERE LastName = 'Smith';"
    The VBA interpretor does not treat a single-quotes character as a string deliminer while JET SQL (as well as most SQL engines) do. In:
    Code:
    Debug.Print 'hello'
    everything is green is a comment, not a string expression and the statement prints an empty line.
    Have a nice day!

  9. #9
    Join Date
    Aug 2006
    Posts
    126
    Since there appears to be more than one problem, I'm trying to tackle the db.openrecordset first. I put a debug.print inside the concatenate routine to see what SQL was being passed to the openrecordset. (pstrSQL) is a parameter passed to the routine. Even tho it shouldn't be necessary, I fully qualified the WHERE field name. Both EMailTO and LastName are correct spellings and do exist in the source query.

    Code:
    Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Set db = CurrentDb
        Debug.Print (pstrSQL)
        Set rs = db.OpenRecordset(pstrSQL)
    It showed the

    Code:
    SELECT  [EMailTO]  FROM [qCounselorsbyPersonEMailAddresses] WHERE qCounselorsbyPersonEMailAddress.LastName LIKE 'A*'

    This results in MS Visual Basic - Run-time Error '3061' Too few parameters - Expected 3. Debug highlights as points to the Set rs = .... statement.
    PGT

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Copy the SQL statement (contents of pstrSQL) into the SQL view of a new query. You'll probably receive a more detailed error message when you try to open this query.
    Have a nice day!

Tags for this Thread

Posting Permissions

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