Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2006
    Location
    Chicopee, MA
    Posts
    11

    Unanswered: Concatenate Debug Trouble

    I am having difficulty with the Concatenate Module written by Duane Hookom. I am using one table (WKFLOW_PIOTHERS) and am using the fields FLOWID and MOREMEMBERS. MOREMEMBERS contains names that I would like to display in one field. My query in SQL view reads:

    SELECT WKFLOW_PIOTHERS.FLOWID, Concatenate("SELECT MOREMEMBERS FROM WKFLOW_PIOTHERS WHERE FLOWID =" & [FLOWID]) AS MoreMembers
    FROM WKFLOW_PIOTHERS
    GROUP BY FLOWID, Concatenate("SELECT MOREMEMBERS FROM WKFLOW_PIOTHERS WHERE FLOWID =" & [FLOWID]);

    When I run it, I get a Run-time error '3464' Data Type mismatch in the criteria expression. On debug, the line "Set rs = db.OpenRecordset(pstrSQL)" is highlighted. I am having difficulty understanding this.
    Anyone else have this problem / solution?

    Thanks in advance.
    Mike

  2. #2
    Join Date
    Jul 2006
    Location
    Chicopee, MA
    Posts
    11

    And also...

    The table is a linked table from Oracle.

  3. #3
    Join Date
    Mar 2005
    Posts
    22
    The data type mismatch means that the field used for the criteria doesn't match the datatype used. For example if the data type for the column is Number and you are trying to use Text especially in a joing statement it will not work since the data types do not match.

    Which is kind of odd since you are using the same table to join upon.

    Try this sql statement

    SELECT WKFLOW_PIOTHERS.FLOWID, Concatenate("SELECT MOREMEMBERS FROM WKFLOW_PIOTHERS WHERE FLOWID =" & [FLOWID] & ") AS MoreMembers
    FROM WKFLOW_PIOTHERS
    GROUP BY FLOWID, Concatenate("SELECT MOREMEMBERS FROM WKFLOW_PIOTHERS WHERE FLOWID =" & [FLOWID]);

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Which is kind of odd since you are using the same table to join upon
    hmmmm, seems to me that Oracle likes to occasionally cause hiccups in Access front ends just on principle .....


  5. #5
    Join Date
    Jul 2006
    Location
    Chicopee, MA
    Posts
    11

    Didn't work.

    It didn't work. I get a syntax error. I get the error whether I use the DAO or ADO version.



    Quote Originally Posted by asalamay
    The data type mismatch means that the field used for the criteria doesn't match the datatype used. For example if the data type for the column is Number and you are trying to use Text especially in a joing statement it will not work since the data types do not match.

    Which is kind of odd since you are using the same table to join upon.

    Try this sql statement

    SELECT WKFLOW_PIOTHERS.FLOWID, Concatenate("SELECT MOREMEMBERS FROM WKFLOW_PIOTHERS WHERE FLOWID =" & [FLOWID] & ") AS MoreMembers
    FROM WKFLOW_PIOTHERS
    GROUP BY FLOWID, Concatenate("SELECT MOREMEMBERS FROM WKFLOW_PIOTHERS WHERE FLOWID =" & [FLOWID]);
    Last edited by praypad; 07-05-06 at 09:34.

  6. #6
    Join Date
    Jul 2006
    Location
    Chicopee, MA
    Posts
    11
    When I mouse over the pstrSQL variable in Debug, it displays:
    pstrSQL="SELECT MOREMEMBERS FROM WKFLOW_PIOTHERS WHERE FLOWID =-2124720162"
    -2124720162 being the first value in the FLOWID list.

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I believe it is your rs variable. If you have rs dimmed as a recordset in Access 2000 or later then it assumes ADO. And the assignment is a DAO assignment:

    set rs = ....

    Make sure if you use:

    Set rs = db.OpenRecordset(pstrSQL

    that rs is defined like:

    dim rs as DAO.Recordset

    Also make sure you have a reference to the DAO library.

  8. #8
    Join Date
    Jul 2006
    Location
    Chicopee, MA
    Posts
    11
    I still get the error. Is there anymore info I can provide to make this easier for some one?

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    If [FlowID] is a Null value at any point, it will cause the error if the Concatenate function is setup to receive a string or some other variable type that does not accept Null values.

    Since I don't have the source for the Concatenate function, I can't be certain.

    Also, with respect to the DAO, did you go into references and check "Microsoft Data Access Objects (DAO) 3.6" (or what ever version)?

    tc

  10. #10
    Join Date
    Jul 2006
    Location
    Chicopee, MA
    Posts
    11

    Source.

    I don't know if I am supposed to post this, but this is the source with the acknowledgement to the creator. Found here:
    http://www.rogersaccesslibrary.com/O...p#Hookom,Duane
    I left it intact:

    Function Concatenate(pstrSQL As String, _
    Optional pstrDelim As String = ", ") _
    As String
    'Created by Duane Hookom, 2003
    'this code may be included in any application/mdb providing
    ' this statement is left intact
    'example
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID, FirstName, DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    ' John, Mary, Susan
    'in a Query
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    ' WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '

    '======For DAO uncomment next 4 lines=======
    '====== comment out ADO below =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

    '======For ADO uncomment next two lines=====
    '====== comment out DAO above ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
    If Not .EOF Then
    .MoveFirst
    Do While Not .EOF
    strConcat = strConcat & _
    .Fields(0) & pstrDelim
    .MoveNext
    Loop
    End If
    .Close
    End With
    Set rs = Nothing
    '====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
    strConcat = Left(strConcat, _
    Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
    End Function
    Last edited by praypad; 07-11-06 at 07:39.

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Yep, that may be the problem:

    Function Concatenate(pstrSQL As String, _
    Optional pstrDelim As String = ", ") _
    As String
    The value fed to pstrSQL from the function call is:
    "SELECT MOREMEMBERS FROM WKFLOW_PIOTHERS WHERE FLOWID =" & [FLOWID]
    if [FLOWID] is a number, then Access will convert it to a string using the default conversion, which is ok for what you are doing. But, if the value in [FLOWID] is null, then the entire value becomes null. The declaration of pstrSQL in the function is a STRING which does not accept null values.

    Try this:
    Code:
    "SELECT MOREMEMBERS FROM WKFLOW_PIOTHERS WHERE FLOWID =" & Nz([FLOWID],"-999999")
    (make sure -999999 is not one of you values. If so, choose a number that is not a value)

    good luck,
    tc

  12. #12
    Join Date
    Jul 2006
    Location
    Chicopee, MA
    Posts
    11
    Thanks for the response, tc. I altered the statement as you suggested but I still get the data type mismatch error. The only differance is that I don't have to click "End" 50 times to halt debugging.
    I am not experienced in Module development or VB, so if there is any other information / description I can provide please let me know.
    Thanks for taking the time to help.

  13. #13
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    After reviewing the rest of the code more closely, you still may be looking at assigning null values to string variables.

    Update the following portion of code as shown in red:
    Code:
    Do While Not .EOF
    strConcat = strConcat & _
    Nz(.Fields(0),"XXX") & pstrDelim
    .MoveNext
    Loop
    There is probably some null values in your table.

    tc

  14. #14
    Join Date
    Jul 2006
    Location
    Chicopee, MA
    Posts
    11
    This still didn't do the trick. Does anyone know of another good concatenate module?

  15. #15
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Describe what you want to do and the circumstances you are doing it in and we can probably help you write your own.

    tc

Posting Permissions

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