wrong data type?

  • Thread starter Thread starter martin
  • Start date Start date
M

martin

Hi Alex!
Thank you for mail.
I tried to use your idea, but i still have problems.
I have no knowledge in VBA, but this is a good point to start.
I copied the function to a new module in the VBA Editor and
saved it as module columntorow.

When I run the query in Access

SELECT id, ListRecords("select data from table where id ="
& [id],"data")
FROM
;


on the table
id data
digit Text fielddatatype
Long Integer





Visual Basic pops up a message

Error while compiling
user defined Typ not definde


In the visual basic Editor the line

rst As DAO.Recordset

is highlighted

Where do I have to define the type
in Access table or in the VBA module?


---------------------------




-----Original Message-----
you can use this function:

Public Function ListRecords(strSQL As String, ParamArray aFields() As
Variant) As String
Dim rst As DAO.Recordset, strResult As String, intI As Integer
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
With rst
Do Until .EOF
For intI = 0 To UBound(aFields)
Select Case rst(aFields(intI)).Type
Case dbText, dbMemo
strResult = strResult & (rst(aFields(intI)).Value +
", ")
Case Else
strResult = strResult & (rst(aFields(intI)).Value &
", ")
End Select
Next intI
If Len(strResult) > 2 Then
strResult = Left(strResult, Len(strResult) - 2) & vbCrLf
End If
.MoveNext
Loop
.Close
End With
If Len(strResult) > 2 Then
strResult = Left(strResult, Len(strResult) - 2)
End If
ListRecords = strResult

End Function

in a query as

select id, ListRecords("select data from table where id=" & [id], "data")
from table

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com



Martin said:
Hello!
I have a table with data organized in columns, but I need
them for one ID in one row.

for example
ID Data
1 a
1 b
1 c
1 d
2 a
2 a
2 b
3 c
3 d

it should looke like that

1 a,b,c,d,e,
2 a,a,b
3 c,d

How can I manage that?
Click to expand...


.
Click to expand...
..
 
This error probably means that you need to add the DAO library to your references.

Open the module.
Select Tools: References
In the dialog window, scroll down and find Microsoft DAO 3.6 Object Library
Select it (the version number "3.6" will vary depending on your version of Access.
Close the dialog window.

Try the code again.

THIS MAY BREAK OTHER CODE - since DAO and ADO libraries have some objects that
are named the same.
Hi Alex!
Thank you for mail.
I tried to use your idea, but i still have problems.
I have no knowledge in VBA, but this is a good point to start.
I copied the function to a new module in the VBA Editor and
saved it as module columntorow.

When I run the query in Access

SELECT id, ListRecords("select data from table where id ="
& [id],"data")
FROM
;

on the table
id data
digit Text fielddatatype
Long Integer



Visual Basic pops up a message

Error while compiling
user defined Typ not definde

In the visual basic Editor the line

rst As DAO.Recordset

is highlighted

Where do I have to define the type
in Access table or in the VBA module?

---------------------------
-----Original Message-----
you can use this function:

Public Function ListRecords(strSQL As String, ParamArray aFields() As
Variant) As String
Dim rst As DAO.Recordset, strResult As String, intI As Integer
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
With rst
Do Until .EOF
For intI = 0 To UBound(aFields)
Select Case rst(aFields(intI)).Type
Case dbText, dbMemo
strResult = strResult & (rst(aFields(intI)).Value +
", ")
Case Else
strResult = strResult & (rst(aFields(intI)).Value &
", ")
End Select
Next intI
If Len(strResult) > 2 Then
strResult = Left(strResult, Len(strResult) - 2) & vbCrLf
End If
.MoveNext
Loop
.Close
End With
If Len(strResult) > 2 Then
strResult = Left(strResult, Len(strResult) - 2)
End If
ListRecords = strResult

End Function

in a query as

select id, ListRecords("select data from table where id=" & [id], "data")
from table

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com



Martin said:
Hello!
I have a table with data organized in columns, but I need
them for one ID in one row.

for example
ID Data
1 a
1 b
1 c
1 d
2 a
2 a
2 b
3 c
3 d

it should looke like that

1 a,b,c,d,e,
2 a,a,b
3 c,d

How can I manage that?
Click to expand...


.
Click to expand...
.
Click to expand...
 
Back
Top