Create a report with concatenating value

**twinnyfo** · Sep 19 '14, 10:45 AM

namktkt,

You mention you are a newbie to MS Access, so I will tell you that the solution might be more involved than what you might be looking for.

To describe how I would approach this problem, I would:

Call a Function that uses as its parameter a numerical flag (1, 2 or 3), representing the three different return values you desire. This function would return a string value that you use in your report.
The Function would create a recordset, based on your table, searching for records that meet the criteria you describe:
Create Date, but no Delivery Date/Complete Date
Delivery Date but no Complete Date
Complete Date

**namktkt** · Sep 21 '14, 04:36 PM

Hi twinnyfo,

When u say about writing a Function, I'm a bit worried as I'm not so confident in writing a Function in Access. But anyway, I tried & did it (as below).
As you can see in my file, we can get the outcome of the Function but it's quite stupid to me that I can't assign these output values to the Report !!! Can u help me this ?
And another question is that can we arrange output values in better order (it is currently "F1,F,E1,E, D,", can we change to D,E,E1,F,F1) ?
Thks,

Code:

Dim db As DAO.Database, rst As DAO.Recordset
Dim strCreDate As String, strDeliDate As String, strCompleDate As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("Table1", dbOpenSnapshot)
rst.MoveFirst

  Do Until rst.EOF
    If Not IsNull(rst.Fields("CreDate")) = True And IsNull(rst.Fields("DeliDate")) = True Then
        strCreDate = rst.Fields("ItemID") & ", " & strCreDate
    End If
    If Not IsNull(rst.Fields("DeliDate")) = True And IsNull(rst.Fields("CompleDate")) = True Then
        strDeliDate = rst.Fields("ItemID") & ", " & strDeliDate
    End If
    If Not IsNull(rst.Fields("CompleDate")) = True Then
        strCompleDate = rst.Fields("ItemID") & "," & strCompleDate
    End If
    rst.MoveNext
  Loop

Set rst = Nothing
Set db = Nothing

DoCmd.OpenReport "Table1", acViewPreview

**zmbd** · Sep 22 '14, 05:44 AM

is this the actual text you want from the data given?

Code:

    1. CreateDate : A,B
    2. DeliveryDate : C
    3. CompleteDate : D, E

so for line item one:
A, B and not 01-Jan , 02-Jan

Do you need the item list numbers (1 thru 3...)

**namktkt** · Sep 22 '14, 07:31 AM

Yes, I need the data is A,B...(not the date).
The number 1,2,3 is not necessary, so don't pay attention to it.

Anyway, with my codes, I can see that I was able to list the data to A,B,C...but now I don't know how to assign them to the Report (sorry I can't upload my file here). I tried to put "strCreDate ", "strDeliDat e", "strCompleD ate" to the ControlSource of 3 different textboxes in the report. But it doesn't show the values !!!

**jforbes** · Sep 22 '14, 01:16 PM

Namktkt,

I think you might want to take a slightly different approach on this. What you are trying to accomplish is very common in Access Reporting except for putting all the values on one line.

Typically, by creating a Report with Grouping, you will get an output very close to what you want except the values (A, B, C, D, E...) will be on separate lines. A big reason for this is that when more and more data is added to the table, the output lines would get longer and longer and at some point would need to wrap around and start a new line. When this happens, the output starts to get confusing.

I think you will also find that since Access was tailored this way, it will be much less frustrating to accomplish.

**twinnyfo** · Sep 22 '14, 01:42 PM

namktkt,

What I meant by using a function is that you call the function from within the Query. The results of the function then serve as one of your fields, which you can place anywhere on th Report.

However, please consider carefully the advice from jforbes (Post #6). Although we can certainly guide you to a solution that will "look and feel" exactly like you want it, it may be "easier and wiser" to use some of teh built-in functionality of the MS Access reporting capabilities.

It is your choice, and we can work through either solution.

**twinnyfo** · Sep 22 '14, 01:58 PM

namktkt,

To slightly modify the code you provided:

Code:

Public Function BuildString(ColumnNum As Integer)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fAddText As Boolean
    Dim strReturn As String

    strReturn = ""
    fAddText = False
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Table1", dbOpenSnapshot)
    If Not rst.RecordCount = 0 Then
        rst.MoveFirst
        Do Until rst.EOF
            Select Case ColumnNum
                Case 1
                    If (Not IsNull(rst!CreDate)) _
                        And IsNull(rst!DeliDate) Then
                        fAddText = True
                    End If
                Case 2
                    If (Not IsNull(rst!DeliDate)) _
                        And IsNull(rst!CompleDate) Then
                        fAddText = True
                    End If
                Case 3
                    If Not IsNull(rst!CompleDate) Then
                        fAddText = True
                    End If
            End Select
            If fAddText Then
                strReturn = strReturn & _
                    IIf(strReturn = "", rst!ItemID, _
                        ", " & rst!ItemID)
            End If
            fAddText = False
            rst.MoveNext
        Loop
        Set rst = Nothing
        Set db = Nothing
    End If
End Function

Then, just call the Code from your Query:

Code:

BuildString(1)

Will give you A,B (based on your sample data)

Code:

BuildString(2)

and

BuildString(3)

Will produce results for your other two columns.

**namktkt** · Sep 23 '14, 01:41 AM

Thanks jforbes, I understand that u're guiding me another approach to my issue. But it seems to tough for me to get your points (I'm quite new to Access !!!).

Thanks twinnyfo, your modified codes look much better than mine. Please let me check at my end & get back to u soon.

**namktkt** · Sep 23 '14, 08:26 AM

Hi twinnyfo,

I copy your codes in the Modules under the name of Module1. I tested it by using AddWatch with the strReturn & I can see the output of the Function.

However, when I set up a textbox on Report & set =BuildString(1) in the ControlSource, the Report only displays blank for this Textbox at the preview mode. Am I wrong somewhere else ?

Thks

**twinnyfo** · Sep 23 '14, 11:59 AM

BuildString(1) (and the other two), should be Fields in your Query.

Code:

SELECT BuildString(1) AS CreateDate ... (etc.)

Then you use the Field the Query Returns (CreateDate) in your Report. I try to avoid Calculating in my Reports unless I have to.

**namktkt** · Sep 23 '14, 04:05 PM

I've got it & try your suggestion but it fails !!!
Anyway, let me check this error around & get back to u. Thks.

**twinnyfo** · Sep 23 '14, 07:02 PM

Please provide the Query you use as the REcord Source for your Report.

**namktkt** · Sep 24 '14, 01:17 AM

I set up 1 Query & use it as Record Source for the Report.
It is "SELECT BuildString(1) AS CreateDate, BuildString(2) AS DeliveryDate, BuildString(3) AS CompleteDate
FROM Table1;"

**namktkt** · Sep 24 '14, 06:56 AM

Hi twinnyfo,
Table1 is requested by Access to execute the query. When I try to run the query alone, it also doesn't have any value inside (except for some blank cells) !!!

Create a report with concatenating value

Create a report with concatenating value

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment