can this be grouped?

E

Emilio

Hi all,

I need to have this result:

OrderID Customer Items
3456 Jones Windows, Doors
(where the OrderID appears only once)
-------------------------------------------
Right now I have:

OrderID Customer Items
3456 Jones Windows
3456 Jones Doors

I am a newbie and can't figure this out.
Any help greatly appreciated.

Thanks,
Emilio





???????

Gracias
 
E

Emilio

Hi Duane, thanks for youe help.

I am using the aFunction with DAO and followed the lines
instructions:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
-----------------------------------------
When I run my query I get:
Run-time Error '3061':
Too few parameters. Expected 1.

When I debug it highlights the following:

"Set rs = db.OpenRecordset(pstrSQL)"

I am using Access 2003

Any ideas.

Thanks again,
Emilio
 
D

Duane Hookom

You must provide your expression that you are sending to the function as
well as some data types of significant fields.
 
E

Emilio

Is it because the table name is the same as field name?

AllItems: Concatenate("SELECT Items FROM Items WHERE
OrderID =" & [Items.OrderID] & " ORDER BY
[Items.OrderID]")
 
D

Duane Hookom

You didn't provide the field types so I can assume OrderID is numeric:
AllItems: Concatenate("SELECT Items FROM Items WHERE OrderID =" &
[Items].[OrderID] & " ORDER BY [OrderID]")
Your []s were off. Also, your "ORDER BY [OrderID] shouldn't do anything
since the returned results contains Items from only one OrderID.

I have fairly strict naming conventions which would never allow any two
objects in a database to have the same name. Check
http://www.granite.ab.ca/access/tablefieldnaming.htm.

--
Duane Hookom
MS Access MVP


Emilio said:
Is it because the table name is the same as field name?

AllItems: Concatenate("SELECT Items FROM Items WHERE
OrderID =" & [Items.OrderID] & " ORDER BY
[Items.OrderID]")



-----Original Message-----
You must provide your expression that you are sending to the function as
well as some data types of significant fields.

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
E

Emilio

Thanks again Duane, but it won't work for me.
I am going to start a new post to see if there is a
simpler solution, yours is great but for a larger scope.
Mine is a one time need.

You have lots of patience,
Thanks again,
Emilio


-----Original Message-----
You didn't provide the field types so I can assume OrderID is numeric:
AllItems: Concatenate("SELECT Items FROM Items WHERE OrderID =" &
[Items].[OrderID] & " ORDER BY [OrderID]")
Your []s were off. Also, your "ORDER BY [OrderID] shouldn't do anything
since the returned results contains Items from only one OrderID.

I have fairly strict naming conventions which would never allow any two
objects in a database to have the same name. Check
http://www.granite.ab.ca/access/tablefieldnaming.htm.

--
Duane Hookom
MS Access MVP


Is it because the table name is the same as field name?

AllItems: Concatenate("SELECT Items FROM Items WHERE
OrderID =" & [Items.OrderID] & " ORDER BY
[Items.OrderID]")



-----Original Message-----
You must provide your expression that you are sending
Click to expand...
to
the function as
well as some data types of significant fields.

--
Duane Hookom
MS Access MVP


"Emilio" <[email protected]> wrote
Click to expand...
in
message
Hi Duane, thanks for youe help.

I am using the aFunction with DAO and followed the lines
instructions:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
-----------------------------------------
When I run my query I get:
Run-time Error '3061':
Too few parameters. Expected 1.

When I debug it highlights the following:

"Set rs = db.OpenRecordset(pstrSQL)"

I am using Access 2003

Any ideas.

Thanks again,
Emilio



-----Original Message-----
There is code and samples of this in the generic
concatenate function at
Click to expand...
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hoo
kom,Duane

--
Duane Hookom
MS Access MVP


"Emilio" <[email protected]>
Click to expand...
wrote
in
message
Hi all,

I need to have this result:

OrderID Customer Items
3456 Jones Windows, Doors
(where the OrderID appears only once)
-------------------------------------------
Right now I have:

OrderID Customer Items
3456 Jones Windows
3456 Jones Doors

I am a newbie and can't figure this out.
Any help greatly appreciated.

Thanks,
Emilio





???????

Gracias




.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Top