Summing memo field

[Summer123]

Hello,
i have a form with subform where user can see the hours allocated, if they want to add more hours they can then open up another form with different options that they can choose to add additional hours, in this form there is also one memo field (basically description of the hours). What the user can then do is check off the options they want and return to the original subform where it adds up the hours and shows the different descriptions but one descriptions overrides the others. What i want to do is also add the memo fields. Is there a way to do this?

i have Nz Sum funcions on my subform to add the hours, but how can i add the memo fields?
thanks!

 

[rodmc]

I dont get what you mean, how can you add memo fields, the contentof memo fields are not numbers so you cant perform any mathematical functions on them, well not as far as I know anyway

 

[Summer123]

right not adding per say but putting them together??? so here is an example
the sub form has the following info

chkbox, hour1, hour2, comment

chkbox1 12 12 work on foood
chkbox2 10 10 planting
chkbox3 8 10 paper work

now if i check off chkbox1 and chkbox2 then what i want is

total: 22 22 work on food, planting

what i am doing on the form now is summing up the hours in another text field
=Nz(Sum([hour1]),0)
=Nz(Sum([hour2]),0)

but i dont know how to get the 2 comments together, summing is prob not the right word but do you see what i am trying to do?

 

[rodmc]

concatenate them!

 

[Summer123]

and how do i do that if the information is in the same column? so for example, i want to conct the assumptions when 2 different projects are picked (true in chkbox):

witht he query below i get 2 rows, but i want one row with just the conct'ed assumptions, even if i get rid of the Proj Name i still get 2 different rows... how do i concatenate them?

SELECT [Proj].Add, [Proj].[Proj Name], [Proj].[Proj Assumptions]
FROM [Proj]
WHERE ((([Proj].Add)=True));

 

[Summer123]

Ok I found this helpful website (http://allenbrowne.com/func-concat.html) that had a concatenate function written by Allen Browne that i could use in a module.. and it works.. but the issue is i am totaling everything on a form in the footer field so i have 4 text boxes and each box here is what i have written,
=Nz(Sum([hour1]),0)
=Nz(Sum([hour2]),0)
=Nz(Sum([hour3]),0)
=ConcatRelated("comment","mytbl") *** this does not work in the footer, but it will work if i put it in the body??

the totals for the hours do come up but the text concatenation is not working... can someone please help???

 

[PNGBill]

Try

=[fieldOne]+[FieldTwo] for the footer

Here you use the text box control name, not the Control Source name.

 

[Summer123]

not sure what you mean? the field i am trying to concatenate are in the same field but different rows
so in the form i have a table that is shown in datasheet view and has the following information
chkbox, hour1, hour2, comment

chkbox1 12 12 work on foood
chkbox2 10 10 planting

my expectation in the footer is to have the following result in the text box
total: 22 22 work on food, planting

the way i have coded the textboxes in the footer is as follows
=Nz(Sum([hour1]),0)
=Nz(Sum([hour2]),0)
=Nz(Sum([hour3]),0)
=ConcatRelated("comment","mytbl")

the hrs sum up and is fine but the comments dont concatenate...and thats where i need help.. please advise.
thank you,
summer

 

[PNGBill]

Sorry, I am not sure how you would join text for records in a continuous form.
You could create a UDF (user defined function) to do this and put this as the record source in the footer/header control.

 

[Summer123]

i'm not good at VBA so wouldnt have clue on User defined fields.. can anyone help with PNGBill's suggestion?

 

[PNGBill]

Check your Modules group. Do you have any functions there ?

Help is never far away and the vba section of the forum ussually has the most visitors.

With vba code it is a little easier to join string as you can get one value, hold it in a variable and then get another value and join the two together.

Just a matter of taking it one step at a time.

The crucial part here is to know what records to work with ie you need some clear definition on what records would be in the Form.

You don't want the Form to display one group of records and the Function to return a string that is using a different set.

You can also have additional fields in your sql/query that are not used in the form body but just in the header and footer but as you want to join text from different records, this will not be easy.

I would have approached this, before I became familiar with vba, by using a Temp Table.
One query will put Record A in a new table - make table query.
A second query will update a field in the table with the text from record B
You then have one record in the new table table with Primary key field, Record A text and Record B text - 3 fields for one record.
You can then bring this table into your query for the Form and have the two text fields on each record of the form - don't show them on your form body though.
The two records can be concatenated in the query and use that calculated field in your form footer/header.
Sounds messy but does work.

Now though, just create a function and the job is done.

 

[Summer123]

thank you PNGBill. but i am still not getting what you are stating below. I have attached a DB that Access offers...If you open the DB and look at Client 1 that I have entered, in there you can add "common tasks" I have 2 common tasks and i want to add both to Client 1, which it does perfectly, I have added under the common tasks a comment field, which comes up when you add the 2 tasks. What I want to do is, see where it adds the hours ( 2nd tab on the Projects detail form, you'll see the hours total is 30), I also want to concatenate the memo fields and pull it to the first tab, which I am not sure how to do.. can you please assist or point me in the right direction of how to do this? thank you, Summer

 

[PNGBill]

I open Form Project Details and see Client1, Project Details tab with Notes blank.
Click on Projects Tasks tab and see two tasks. Task1 and Task2, each with a comments control.

You want the data in the comments control to be also displayed in tab Project Details ? correct ?

This would read "Exporting to CH1, Exporting to IN1" if my understanding is correct.

 

[PNGBill]

You are not making life easy when repeating field names in different tables. eg Primary Key is ID in all tables - Try TaskID, EmployeeID, ProjectsID. some will repeat where they are the foreign field but when you are working in code/queries/ forms etc, it can get confusing as to what table you are dealing with.

It is not recognised as good practice to have spaces in names.

 

[Summer123]

yes your understanding is correct on both of these

You want the data in the comments control to be also displayed in tab Project Details ? correct ?

This would read "Exporting to CH1, Exporting to IN1" if my understanding is correct

and this DB is not what i am working on, this is just somthing i pulled off of Access that they offer so that i can show you what i am trying to do... hope that helps and THANK YOU!

 

[PNGBill]

Is this what you want ? - see attached zip

 

[PNGBill]

This version has last comma removed from the string.

Here is the code.

Public Function fncTaskComments(ProjectID As Long) As String
   On Error GoTo fncTaskComments_Error
    Dim rst As DAO.Recordset
    Dim strOutput As String
    
    Set rst = CurrentDb.OpenRecordset("SELECT Projects.ID, Tasks.comments AS ProjectComments " & _
        "FROM Projects LEFT JOIN Tasks ON Projects.ID = Tasks.Project " & _
        "WHERE (((Projects.ID)=" & ProjectID & "));", dbOpenDynaset)
    
    If Not (rst.BOF And rst.EOF) Then   'test for records
        Do While Not rst.EOF
            strOutput = strOutput & rst("ProjectComments") & ", "
        rst.MoveNext
        Loop
        strOutput = Left(strOutput, Len(strOutput) - 2) 'remove last comma
        fncTaskComments = strOutput
    Else
        fncTaskComments = "No Comments"
    End If
   On Error GoTo 0
   Exit Function

 

[Summer123]

first of all THANK YOU!!! that is exactly what i was trying to do! Also the Author should be you as i am not the one who wrote this so i will change that, i really appreciate your help. I am not going to try and see if it works for me on my DB and will let you know if i have any questions. But besides the module and calling the function on the textbox was there anything else i need to do? thank you once again.

 

[PNGBill]

Glad it resolved your issue.

You will find that having your own Public Functions will allow of tasks to be done that prev may have taken a bucket full of queries.
Plus, they can be reused from anywhere in your database.

Get your query/sql to return the data you require (comments)
Then edit this sql to be used in your vba code.

Have the code loop through the recordset (query data) and in this case, we join the string.

Other bits of code check if no records which while not essential, can cause sometimes.

As you attempt any task, post what you have done and advice is then quick to come.

 

[Summer123]

hi PNGBill,
Thanks for your help earlier.. in this same thread i have another question... your code worked fine but instead of the comma can it be so that each line is its own and its separated out instead of by comma's by a new line? i tried to remove the comma and it didnt work then i tried putting & vbCrLf or vbLf... but that didnt work either.. anything i am missing here? Suggestions please?