Results 1 to 2 of 2
  1. #1
    tgall is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Location
    na
    Posts
    38

    Call a Function to build a string

    Short trip: How do I build a dynamic SQL/query code: [1] & ", " & [2] & ", " ~~~~~?

    Scenic View: For one request (RequestID] there can be many test methods (numbered sequentially by the program) and many samples (also numbered sequentially by the program).
    BUT, each test method is assigned a certain set of samples. For example:

    TestMethod# ..... Sample#
    1 ....................... 1
    1 ....................... 3
    1 ....................... 9
    2 ....................... 1
    2 ....................... 6
    2 ....................... 7
    3 ....................... 9

    DESIRED RESULT: via SQL/query I want to present the data as:
    TestMethod# ..... Assigned
    1 ....................... 1, 3, 9
    2 ....................... 1, 6, 7
    3 ....................... 9
    .
    Sooo, I created a crosstab query:
    [TestMethod] ..... [1] .. [2] .. [3]
    1 ....................... 1 .....3 ..... 9
    2 ....................... 1 .... 6 ..... 7
    3 ....................... 9
    .
    .
    .

    I built a couple of aggregate queries to find the [TestMethod] having the most samples and named the field [MaxCode]. For the preceding example, [MaxCode] = 3

    Everything works up to this point. My plan was to build a SELECT query with a field entitled [Assigned].
    In the query wizard, the field would look like: Assigned: [1] & ", " & [2] ", " & ~~~~~~ [9]

    PROBLEM: I built a function in a module I thought would create the preceding string -- it doesn't work:

    Public Function BuildAssignments(MaxCode) As String


    Dim m As Integer
    Dim f As String
    Dim Assigned As String
    Dim i As Integer

    m = MaxCode
    f = "[" & i & "], "
    i = 0

    Do Until i > m
    Assigned = Assigned & f
    i = i + 1
    Loop
    End Function

    Because the result would look like (eg for [TestMethod]= 3): , , , , , , , , 9
    I would build a query to delete ", ,"

    What am I doing wrong?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    run the Crosstab query thru this code which scans the xtab qry and places results in a table. query the table.

    Code:
    Sub BuildXtabResults()
    Dim rst
    Dim fld As Field
      'empty target table
    sSql = "DELETE * FROM tRptTbl"
    DoCmd.RunSQL sSql
      
       'now scan the crosstab query collecting fields
    Set rst = CurrentDb.OpenRecordset("qsCrosstabQry")
    With rst
        While Not .EOF
            For Each fld In .Fields
                If fld.Name = "TestMethod" Then
                   vMeth = fld.value & ""
                Else
                   vResult = fld.Value & ""
                   If vResult <> "" Then vOut = vOut & vResult & ","
                End If
            Next
            'remove last comma
            vOut= Left(vOut, Len(vOut) - 1)
            
                    '-----------------------
                    'post THE total...
                    '-----------------------
               sSql = "insert into tRptTbl ([TestMethod],[Assigned])  values ('" & vMeth & "','" & vOut & "')"
               DoCmd.RunSQL sSql
               vOut= ""
            
           .MoveNext
        Wend
    End With
    Set fld = Nothing
    Set rst = Nothing
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. can't trace function call
    By visions in forum Programming
    Replies: 14
    Last Post: 12-28-2014, 01:24 PM
  2. Replies: 3
    Last Post: 06-08-2014, 05:36 PM
  3. Replies: 7
    Last Post: 05-22-2014, 06:17 AM
  4. Call A function
    By aspen in forum Programming
    Replies: 10
    Last Post: 03-16-2014, 12:57 PM
  5. how to build a function in access
    By AmandaMc in forum Access
    Replies: 6
    Last Post: 02-10-2013, 07:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums