Forms/Subforms/Reports

Can you upload sample DB and show expected result?

Here's some code that shows how to concatenate many-side records.  However, your requirement is more complicated.  You have an alpha string that you want to summarize by showing only a starting and ending value.  How do you want to handle gaps?  What if AD is missing?   Should the string be AA-AC, AE-AX?
Concatenate-AllenBrowne.docx

Pat this is exactly what Im talking about.  I didnt know if there was a way to handle the gaps.  For example, I might have 12 parts AA-AL and AD might be rejected.  Would there be a way to take AA-AC, AE-AL?  that would be exactly what im looking for.  please advise

I didnt know if there was a control field that I can put in to grab the suffix for the last record on the subform but that still wouldnt answer how to handle gaps if required

Forms don't have anything to do with this process.  You are processing a recordset made from the table.

I'm sure there is a way to handle the gaps but the code I posted won't do it.  You are going to have to write your own custom code.  Create a query that sorts the data into sequence by the unique identifier.  The code will split the common part from the suffix.  It will need to save the common part to a hold area that is used to match subsequent records.  You will need to have an array to capture the suffixes.  When there is a break in the common part, your code must loop through the array and build the grouping string.  It will need to compare each code to the previous 1 to ensure there is no gap.  This is more difficult than you might imagine since you can't subtract AC from AD to determine the difference is 1.  I would probably create  a table to translate the suffixes from numeric values and use the numeric value in the subtract.

Well, that's it in a nutshell, you should be able to finish the code by 2:00 :)

Take a stab at it and we'll help.

Is there any way to just grab the last suffix on that particular sub form (example AL)?  I figured the database can figure out that AL is after AK but really just to take the suffix from the first record and the last record.  I think if I want to get rid of the gap I can add a field on my form where the user can input to exclude these suffixes.  The cert would then say AA-AL except AD.  (AA being a field, - being a label, AL being a field, except AD being a field).  More input by the user but simpler to create if there was a way to grab the first and last record.  thanks for the help

thanks

Again, this is not something you do with a form.  It is something you do with a recordset.  You could create a query that selects the min and max suffixes but it won't handle gaps.

Select Left(TraceCode, Instr(TraceCode, "-") -1) as TraceCodeGroup, Min(Right(TraceCode, 2)) as MinSuffix, Max(Right(TraceCode, 2)) as MaxSuffix
From yourtable
Where Left(TraceCode, Instr(TraceCode, "-") -1)  = Left(Forms!yourform!TraceCode, Instr(TraceCode, "-") -1)
Group By Left(TraceCode, Instr(TraceCode, "-") -1) ;

Of course, this would be simpler if the suffix was separate.  It is always better to store something like the suffix in a separate field.  You can then concatenate the two fields when you display them.
Select TraceCode, Min(Suffix) As MinSuffix, Max(Suffix) as MaxSuffix
From yourtable
Where TraceCode = Forms!yourform!TraceCode
Group By TraceCode;

The thing is that the trace code is in the main form and the suffix is a separate field in the sub form so it is already been concatenated.  it wont do a min of the suffix (it gives an error)

concatenated means stuck together.  It sounds like they are separate.  The group part in the main form and the suffix in the subform.

Please show us the query you wrote and the exact error message.

The query I suggested needs to get the common part of the TraceCode from the main form since you want it without the suffix.  If necessary, it can reference the subform but that will only work if the subform actually has records.

It would also help to see what the data looks like in the parent table and in the child table.

Yeah I know how to concatenate.  They are separated right now.  I open up the cert by a command button which has this code
Private Sub Command28_Click()
DoCmd.OpenReport "New COC", acViewPreview, , "[furnace run#]=Forms![Final Input new]![furnace run#]"
End Sub

My tables look like the excel file I attached
New-RVC-dimensions.xlsx

Sorry for the delay I have been busy

This is what my SQL looks like before adding your query

SELECT [New TBLRVC].[PO#], [New TBLRVC].SO, [New TBLRVC].[Trace Code], [New TBLRVC].[Part Name], [New TBLRVC].[Inspection Qty], [New TBLRVC].[Furnace Run#], [New RVC many to many Table].Suffix, [New TBLRVC].[Shipping Date], [New TBLRVC].[Packing Slip#], [New TBLRVC].[Lot Qty]
FROM [New TBLRVC] INNER JOIN ([New RVC many to many Table] INNER JOIN [New RVC dimensions] ON [New RVC many to many Table].[Suffix] = [New RVC dimensions].[Suffix (ID)]) ON [New TBLRVC].[Furnace Run#] = [New RVC dimensions].[Furnace Run#];