Creating a string from a query in vba

[StuJol]

using access 2003 i have a qry that returns top 5 values

1 Item1 123
2 Item2 101
3 Item3 32
4 Item4 2
5 Item5 76

im trying to create a function that retuens a string like
"Item1 123, Item2 101, Item3 32" etc etc

I can create a function that returns the first row but not all 5 rows. can
anyone help please??

 

[BruceM via AccessMonster.com]

One option is here:
http://allenbrowne.com/func-concat.html

Another here:
http://www.rogersaccesslibrary.com/forum/topic16.html

They are similar, but one may work better than the other for your specific
situation.

 

[Daniel Pineault]

You could use a function such as:

Function GenStr()
On Error GoTo Error_Handler
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("YourQueryName")

If rs.RecordCount <> 0 Then
With rs
.MoveFirst
Do While Not .EOF
GenStr = GenStr & ![QueryFieldName1] & " " &
![QueryFieldName2] & ", "
.MoveNext
Loop
End With

'Remove trailing , if applicable
If Right(GenStr, 2) = ", " Then
GenStr = Left(GenStr, Len(GenStr) - 2)
End If
Else
'No records returned by your table or query
GenStr = ""
End If

Error_Handler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: GenStr" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[StuJol]

Daniel, Just looking at your response and are getting error messages, my code
is as below and im getting error 3061, too few parameters when the code
executes line Set rs = db.OpenRecordset("Alarms - Module Totals")

any ideas what im doing wrong??


Function GenStr() As String
On Error GoTo Error_Handler

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Alarms - Module Totals")
MsgBox "1"
If rs.RecordCount <> 0 Then
With rs
.MoveFirst
Do While Not .EOF
GenStr = GenStr & ![Module] & " " & ![CountOfModule] & ", "
.MoveNext
Loop
End With

'Remove trailing , if applicable
If Right(GenStr, 2) = ", " Then
GenStr = Left(GenStr, Len(GenStr) - 2)
End If
Else
'No records returned by your table or query
GenStr = ""
End If

Error_Handler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & Err.Number & vbCrLf & "Error Source: GenStr" & vbCrLf &
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function

You could use a function such as:

Function GenStr()
On Error GoTo Error_Handler
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("YourQueryName")

If rs.RecordCount <> 0 Then
With rs
.MoveFirst
Do While Not .EOF
GenStr = GenStr & ![QueryFieldName1] & " " &
![QueryFieldName2] & ", "
.MoveNext
Loop
End With

'Remove trailing , if applicable
If Right(GenStr, 2) = ", " Then
GenStr = Left(GenStr, Len(GenStr) - 2)
End If
Else
'No records returned by your table or query
GenStr = ""
End If

Error_Handler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: GenStr" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

using access 2003 i have a qry that returns top 5 values

1 Item1 123
2 Item2 101
3 Item3 32
4 Item4 2
5 Item5 76

im trying to create a function that retuens a string like
"Item1 123, Item2 101, Item3 32" etc etc

I can create a function that returns the first row but not all 5 rows. can
anyone help please??

 

[StuJol]

it appears to be giving errors as a result of opening a qry that is based on
another qry with criteria. is there any way round this??

Daniel, Just looking at your response and are getting error messages, my code
is as below and im getting error 3061, too few parameters when the code
executes line Set rs = db.OpenRecordset("Alarms - Module Totals")

any ideas what im doing wrong??


Function GenStr() As String
On Error GoTo Error_Handler

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Alarms - Module Totals")
MsgBox "1"
If rs.RecordCount <> 0 Then
With rs
.MoveFirst
Do While Not .EOF
GenStr = GenStr & ![Module] & " " & ![CountOfModule] & ", "
.MoveNext
Loop
End With

'Remove trailing , if applicable
If Right(GenStr, 2) = ", " Then
GenStr = Left(GenStr, Len(GenStr) - 2)
End If
Else
'No records returned by your table or query
GenStr = ""
End If

Error_Handler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & Err.Number & vbCrLf & "Error Source: GenStr" & vbCrLf &
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function

You could use a function such as:

Function GenStr()
On Error GoTo Error_Handler
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("YourQueryName")

If rs.RecordCount <> 0 Then
With rs
.MoveFirst
Do While Not .EOF
GenStr = GenStr & ![QueryFieldName1] & " " &
![QueryFieldName2] & ", "
.MoveNext
Loop
End With

'Remove trailing , if applicable
If Right(GenStr, 2) = ", " Then
GenStr = Left(GenStr, Len(GenStr) - 2)
End If
Else
'No records returned by your table or query
GenStr = ""
End If

Error_Handler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: GenStr" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

using access 2003 i have a qry that returns top 5 values

1 Item1 123
2 Item2 101
3 Item3 32
4 Item4 2
5 Item5 76

im trying to create a function that retuens a string like
"Item1 123, Item2 101, Item3 32" etc etc

I can create a function that returns the first row but not all 5 rows. can
anyone help please??

 

[BruceM via AccessMonster.com]

Either of the ready-made functions for which I provided links should do what
you need.

One possibility with Daniel's code (and this is just a guess) is that there
are spaces in the query name, so you may need to enclose it in square
brackets. I don't know if that applies in the context of a query name in
quotes, but it's easy enough to conduct the experiment. Or maybe you can
rename the query so that the name includes only letters, numbers, and
underscores.

it appears to be giving errors as a result of opening a qry that is based on
another qry with criteria. is there any way round this??

Daniel, Just looking at your response and are getting error messages, my code
is as below and im getting error 3061, too few parameters when the code

[quoted text clipped - 101 lines]