Create a report with concatenating value

[namktkt]

Hi all,

I'm a newbie to MS Access & trying to make a report with from a table as below :

    ItemID    CreDate/DeliDate/CompleDate
    A    01-Jan        
    B    02-Jan        
    C    02-Jan    04-Jan    
    D    03-Jan    05-Jan    07-Jan
    E    04-Jan    06-Jan    08-Jan
 

My expected outcome for the report is like

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

I tried to refer to some other similar cases in this site but they are quite different. If you have any suggestion, pls help.
Thanks.

[twinnyfo]

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
• The Function would cycle through the records, adding the Item ID to the String (plus a ", " between records)
• The Value returned by that Function would go in your report.

I hope this description makes sense. For those of us who have been doing this a long time, what you are asking for is (should be) relatively simple. However, we don't even know your Table names or what your data looks like.

On this site, we prefer to see the work our posters have already tried and troubleshoot problem areas to correct/prevent errors.

We are standing by to assist further.

[namktkt]

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,

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]

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

 
    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]

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", "strDeliDate", "strCompleDate" to the ControlSource of 3 different textboxes in the report. But it doesn't show the values !!!

[jforbes]

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]

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]

namktkt,

To slightly modify the code you provided:

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:

BuildString(1)

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

BuildString(2)
 
and
 
BuildString(3)

Will produce results for your other two columns.

[namktkt]

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]

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]

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

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]

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

[twinnyfo]

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

[namktkt]

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]

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) !!!

[twinnyfo]

From Post #14, this is the ONLY recordsource for your Report? THere are not other fields or controls, just the three bits of information?

[namktkt]

Yes, that's correct. I checked around & tried to insert Option Explicit as guided in http://allenbrowne.com/ser-30.html
But it's still the same :(

[twinnyfo]

Remove the "From Table1"

SELECT BuildString(1) AS CreateDate, BuildString(2) AS DeliveryDate, BuildString(3) AS CompleteDate;

[namktkt]

I tried this but one msgbox saying that "Query input must contain at least one table or query" :(
I'm using MS Access 2007 SP2.

[twinnyfo]

Then ignore the Record Source. have an unbound Report (no record source). Then have three text box controls (also unbound). For their Control Source, make them:

= BuildString(1)
=BuildString(2)
=BuildString(3)

This should work. Still don't know why this did not work from Post #10.

[namktkt]

Yes, i created a new report & follow your way. It's still nothing there.
Let me try on another PC by tomorrow. Thanks

[namktkt]

I found the error, twinnyfo.
U missed one line in your codes:

Loop
 BuildString = strReturn
Set rst = Nothing
 

So, now I can run the function directly in Report (no need to set up a Query then).

It's done.
Thanks guy

[twinnyfo]

Ummmmmmm, yeah! Call me dumb!

:-)