Urgent: Combining a field from 2 different records(continuous form)??

[Mtlca401]

Lets say I have a form some thing like this.

Item Number Rev Hull
Line #1 = 6116 1 A
Line #2 = 6116 1 B
Line #3 = 6116 2 A

I need it to look like this, but can't even seem to figure out where to get started. Line #1 and #2 are the same thing, they just apply to different Hulls. And also I just need it to look like this on the Continuous Form, Not the table.

Item Number Rev Hull
Line #1 = 6116 1 A,B
Line #3 = 6116 2 A


I need this by today, and really need help.
thank you

 

[boriska40]

What I did (some test using Acc97):
1. Created Table1 :

SomeNumber Rev Hull
6116 1 A
6116 1 B
6116 2 A

2. Created Public Function in Module Level:
Public Function GetHullString(MyNumber, MyRev) As String

Dim db As Database
Dim rs As Recordset
Dim sql As String
Dim strResult As String

Set db = CurrentDb

sql = "select Hull from Table1"
sql = sql & " where SomeNumber = " & MyNumber
sql = sql & " and Rev = " & MyRev
sql = sql & " order by Hull"

Set rs = db.OpenRecordset(sql, dbOpenDynaset)

If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
strResult = strResult & rs("Hull&quot
rs.MoveNext
If Not rs.EOF Then
strResult = strResult & ","
End If
Loop
End If

rs.Close
Set rs = Nothing

db.Close
Set db = Nothing

GetHullString = strResult

End Function

3. Created next select query:
SELECT DISTINCT Table1.SomeNumber, Table1.Rev, GetHullString([SomeNumber],[Rev]) AS HullString
FROM Table1
ORDER BY Table1.SomeNumber, Table1.Rev;

and I've got:
SomeNumber Rev HullString
6116 1 A,B
6116 2 A

Try it, just put some error handling.