Access97 Reports

[Guest]

I need someones help in being able to finish a database I am working on and
have run into trouble. In my FORM I have a MultiSelect Text Box, that lists
the selections in a single row. It does the same thing in the Report;
however, in the report I need them in a string within the field w/ a "," and
" " between them. I have a few more question, but will start w/ this on first.

 

[Duane Hookom]

There is a generic concatenate function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

Duane:
Thanks for the responce. I downloaded the info that you suggested, but I was
unabe to open it, even after unzipping.HELP again please! Ihave a few more
bugs that I need Help with if you don't mind helping me one on on.

 

[Duane Hookom]

I haven't heard of any other problems with the file. Here is some
information and the function:

To use any function like this, open a new module. Copy the code from
"Function Con..." to "End Function" into the new module. Select
Debug|Compile to make sure there are no compile errors. Then save the module
as "modConcatenate". You can then use the Concatenate() function as an
expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'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

 

[Guest]

Duane:
I tried what you said, and I am sure if I could do it correctly it would
work, but other than basic design I'm an idiot, and get confused very
easily.Is there any way that I can send you a copy of what I have, and you
can Debug it for me? I would really appreciatr it if you could and could give
you some compensation if needed.

I haven't heard of any other problems with the file. Here is some
information and the function:

To use any function like this, open a new module. Copy the code from
"Function Con..." to "End Function" into the new module. Select
Debug|Compile to make sure there are no compile errors. Then save the module
as "modConcatenate". You can then use the Concatenate() function as an
expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'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


--
Duane Hookom
MS Access MVP
--

Duane:
Thanks for the responce. I downloaded the info that you suggested, but I was
unabe to open it, even after unzipping.HELP again please! Ihave a few more
bugs that I need Help with if you don't mind helping me one on on.

 

[Duane Hookom]

I wouldn't accept compensation for something like this. You can email at
AccessMVP at GMAIL dot COM. If you attach an mdb, please compact the
database and then zip it.

--
Duane Hookom
MS Access MVP
--

Duane:
I tried what you said, and I am sure if I could do it correctly it would
work, but other than basic design I'm an idiot, and get confused very
easily.Is there any way that I can send you a copy of what I have, and you
can Debug it for me? I would really appreciatr it if you could and could give
you some compensation if needed.

I haven't heard of any other problems with the file. Here is some
information and the function:

To use any function like this, open a new module. Copy the code from
"Function Con..." to "End Function" into the new module. Select
Debug|Compile to make sure there are no compile errors. Then save the module
as "modConcatenate". You can then use the Concatenate() function as an
expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'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


--
Duane Hookom
MS Access MVP
--

Duane:
Thanks for the responce. I downloaded the info that you suggested, but

I
was

unabe to open it, even after unzipping.HELP again please! Ihave a

few
more

bugs that I need Help with if you don't mind helping me one on on.

:

There is a generic concatenate function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I need someones help in being able to finish a database I am

working
on

and
have run into trouble. In my FORM I have a MultiSelect Text Box, that
lists
the selections in a single row. It does the same thing in the Report;
however, in the report I need them in a string within the field w/

a
","

and
" " between them. I have a few more question, but will start w/

this
on

first.

 

[Guest]

Duane:
Now you are going to think I am a total Idiot! But, I know I did this
right,however, I tried to send you the file 4 times and all 4 times received
an error message UNABLE TO SEND" I sent it to (e-mail address removed) w/ the
zipped file attached and to your ATTN. if I didn't really need this Database
I would just give up!!

I wouldn't accept compensation for something like this. You can email at
AccessMVP at GMAIL dot COM. If you attach an mdb, please compact the
database and then zip it.

--
Duane Hookom
MS Access MVP
--

Duane:
I tried what you said, and I am sure if I could do it correctly it would
work, but other than basic design I'm an idiot, and get confused very
easily.Is there any way that I can send you a copy of what I have, and you
can Debug it for me? I would really appreciatr it if you could and could give
you some compensation if needed.

I haven't heard of any other problems with the file. Here is some
information and the function:

To use any function like this, open a new module. Copy the code from
"Function Con..." to "End Function" into the new module. Select
Debug|Compile to make sure there are no compile errors. Then save the module
as "modConcatenate". You can then use the Concatenate() function as an
expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'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


--
Duane Hookom
MS Access MVP
--

Duane:
Thanks for the responce. I downloaded the info that you suggested, but I
was
unabe to open it, even after unzipping.HELP again please! Ihave a few
more
bugs that I need Help with if you don't mind helping me one on on.

:

There is a generic concatenate function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I need someones help in being able to finish a database I am working
on
and
have run into trouble. In my FORM I have a MultiSelect Text Box, that
lists
the selections in a single row. It does the same thing in the Report;
however, in the report I need them in a string within the field w/ a
","
and
" " between them. I have a few more question, but will start w/ this
on
first.

 

[Duane Hookom]

Try duane AT hookom DOT net

--
Duane Hookom
MS Access MVP
--

Duane:
Now you are going to think I am a total Idiot! But, I know I did this
right,however, I tried to send you the file 4 times and all 4 times received
an error message UNABLE TO SEND" I sent it to (e-mail address removed) w/ the
zipped file attached and to your ATTN. if I didn't really need this Database
I would just give up!!

I wouldn't accept compensation for something like this. You can email at
AccessMVP at GMAIL dot COM. If you attach an mdb, please compact the
database and then zip it.

--
Duane Hookom
MS Access MVP
--

Duane:
I tried what you said, and I am sure if I could do it correctly it would
work, but other than basic design I'm an idiot, and get confused very
easily.Is there any way that I can send you a copy of what I have, and you
can Debug it for me? I would really appreciatr it if you could and

could
give

you some compensation if needed.

:

I haven't heard of any other problems with the file. Here is some
information and the function:

To use any function like this, open a new module. Copy the code from
"Function Con..." to "End Function" into the new module. Select
Debug|Compile to make sure there are no compile errors. Then save

the
module

as "modConcatenate". You can then use the Concatenate() function as an
expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'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

but
I

was
unabe to open it, even after unzipping.HELP again please! Ihave

a
few

more
bugs that I need Help with if you don't mind helping me one on on.

:

There is a generic concatenate function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I need someones help in being able to finish a database I am working
on
and
have run into trouble. In my FORM I have a MultiSelect Text

Box,
that

lists
the selections in a single row. It does the same thing in the Report;
however, in the report I need them in a string within the

field w/
a

","
and
" " between them. I have a few more question, but will start

w/
this

on
first.

 

[Guest]

I think it went thru this time. I didn't get qn error message this
time!!Once again, I really appreciate the help!

Duane:
Now you are going to think I am a total Idiot! But, I know I did this
right,however, I tried to send you the file 4 times and all 4 times received
an error message UNABLE TO SEND" I sent it to (e-mail address removed) w/ the
zipped file attached and to your ATTN. if I didn't really need this Database
I would just give up!!

I wouldn't accept compensation for something like this. You can email at
AccessMVP at GMAIL dot COM. If you attach an mdb, please compact the
database and then zip it.

--
Duane Hookom
MS Access MVP
--

Duane:
I tried what you said, and I am sure if I could do it correctly it would
work, but other than basic design I'm an idiot, and get confused very
easily.Is there any way that I can send you a copy of what I have, and you
can Debug it for me? I would really appreciatr it if you could and could give
you some compensation if needed.

:

I haven't heard of any other problems with the file. Here is some
information and the function:

To use any function like this, open a new module. Copy the code from
"Function Con..." to "End Function" into the new module. Select
Debug|Compile to make sure there are no compile errors. Then save the module
as "modConcatenate". You can then use the Concatenate() function as an
expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'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


--
Duane Hookom
MS Access MVP
--

Duane:
Thanks for the responce. I downloaded the info that you suggested, but I
was
unabe to open it, even after unzipping.HELP again please! Ihave a few
more
bugs that I need Help with if you don't mind helping me one on on.

:

There is a generic concatenate function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I need someones help in being able to finish a database I am working
on
and
have run into trouble. In my FORM I have a MultiSelect Text Box, that
lists
the selections in a single row. It does the same thing in the Report;
however, in the report I need them in a string within the field w/ a
","
and
" " between them. I have a few more question, but will start w/ this
on
first.

 

[Guest]

Duane:
Did you receive the copy of the DataBase I sent?
I hope so! Please let me know, so if you didn't I can try to send it again.
Thanks!! -JIML

I think it went thru this time. I didn't get qn error message this
time!!Once again, I really appreciate the help!

Duane:
Now you are going to think I am a total Idiot! But, I know I did this
right,however, I tried to send you the file 4 times and all 4 times received
an error message UNABLE TO SEND" I sent it to (e-mail address removed) w/ the
zipped file attached and to your ATTN. if I didn't really need this Database
I would just give up!!

I wouldn't accept compensation for something like this. You can email at
AccessMVP at GMAIL dot COM. If you attach an mdb, please compact the
database and then zip it.

--
Duane Hookom
MS Access MVP
--

Duane:
I tried what you said, and I am sure if I could do it correctly it would
work, but other than basic design I'm an idiot, and get confused very
easily.Is there any way that I can send you a copy of what I have, and you
can Debug it for me? I would really appreciatr it if you could and could
give
you some compensation if needed.

:

I haven't heard of any other problems with the file. Here is some
information and the function:

To use any function like this, open a new module. Copy the code from
"Function Con..." to "End Function" into the new module. Select
Debug|Compile to make sure there are no compile errors. Then save the
module
as "modConcatenate". You can then use the Concatenate() function as an
expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'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


--
Duane Hookom
MS Access MVP
--

Duane:
Thanks for the responce. I downloaded the info that you suggested, but
I
was
unabe to open it, even after unzipping.HELP again please! Ihave a
few
more
bugs that I need Help with if you don't mind helping me one on on.

:

There is a generic concatenate function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I need someones help in being able to finish a database I am
working
on
and
have run into trouble. In my FORM I have a MultiSelect Text Box,
that
lists
the selections in a single row. It does the same thing in the
Report;
however, in the report I need them in a string within the field w/
a
","
and
" " between them. I have a few more question, but will start w/
this
on
first.

 

[Duane Hookom]

Can you change your table structure? You should never store multiple values
in a single field.I wouldn't spend additional time on the application until
the tables are normalized.

--
Duane Hookom
MS Access MVP

I think it went thru this time. I didn't get qn error message this
time!!Once again, I really appreciate the help!

Duane:
Now you are going to think I am a total Idiot! But, I know I did this
right,however, I tried to send you the file 4 times and all 4 times received
an error message UNABLE TO SEND" I sent it to (e-mail address removed) w/ the
zipped file attached and to your ATTN. if I didn't really need this Database
I would just give up!!

I wouldn't accept compensation for something like this. You can email at
AccessMVP at GMAIL dot COM. If you attach an mdb, please compact the
database and then zip it.

--
Duane Hookom
MS Access MVP
--

Duane:
I tried what you said, and I am sure if I could do it correctly it would
work, but other than basic design I'm an idiot, and get confused very
easily.Is there any way that I can send you a copy of what I have, and you
can Debug it for me? I would really appreciatr it if you could and could
give
you some compensation if needed.

:

I haven't heard of any other problems with the file. Here is some
information and the function:

To use any function like this, open a new module. Copy the code from
"Function Con..." to "End Function" into the new module. Select
Debug|Compile to make sure there are no compile errors. Then save the
module
as "modConcatenate". You can then use the Concatenate() function as an
expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'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


--
Duane Hookom
MS Access MVP
--

Duane:
Thanks for the responce. I downloaded the info that you suggested, but
I
was
unabe to open it, even after unzipping.HELP again please! Ihave a
few
more
bugs that I need Help with if you don't mind helping me one on on.

:

There is a generic concatenate function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I need someones help in being able to finish a database I am
working
on
and
have run into trouble. In my FORM I have a MultiSelect Text Box,
that
lists
the selections in a single row. It does the same thing in the
Report;
however, in the report I need them in a string within the field w/
a
","
and
" " between them. I have a few more question, but will start w/
this
on
first.

 

[Guest]

Duane:
You can change whatever you need to. Like I said I only know basic lay out
and design. I thought I would be able to do this one myself NOT!, and as you
can see I am very novice at this and created more problems than fixing it.
This is why I a asking for an ACE to help. Are you sure I can't compensate
you for your help. No, I do not want to insult you by asking this, but I know
your time must be wort something? Do you wear contacts or glasses?

Can you change your table structure? You should never store multiple values
in a single field.I wouldn't spend additional time on the application until
the tables are normalized.

--
Duane Hookom
MS Access MVP

I think it went thru this time. I didn't get qn error message this
time!!Once again, I really appreciate the help!

Duane:
Now you are going to think I am a total Idiot! But, I know I did this
right,however, I tried to send you the file 4 times and all 4 times received
an error message UNABLE TO SEND" I sent it to (e-mail address removed) w/ the
zipped file attached and to your ATTN. if I didn't really need this Database
I would just give up!!

:

I wouldn't accept compensation for something like this. You can email at
AccessMVP at GMAIL dot COM. If you attach an mdb, please compact the
database and then zip it.

--
Duane Hookom
MS Access MVP
--

Duane:
I tried what you said, and I am sure if I could do it correctly it would
work, but other than basic design I'm an idiot, and get confused very
easily.Is there any way that I can send you a copy of what I have, and you
can Debug it for me? I would really appreciatr it if you could and could
give
you some compensation if needed.

:

I haven't heard of any other problems with the file. Here is some
information and the function:

To use any function like this, open a new module. Copy the code from
"Function Con..." to "End Function" into the new module. Select
Debug|Compile to make sure there are no compile errors. Then save the
module
as "modConcatenate". You can then use the Concatenate() function as an
expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'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


--
Duane Hookom
MS Access MVP
--

Duane:
Thanks for the responce. I downloaded the info that you suggested, but
I
was
unabe to open it, even after unzipping.HELP again please! Ihave a
few
more
bugs that I need Help with if you don't mind helping me one on on.

:

There is a generic concatenate function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I need someones help in being able to finish a database I am
working
on
and
have run into trouble. In my FORM I have a MultiSelect Text Box,
that
lists
the selections in a single row. It does the same thing in the
Report;
however, in the report I need them in a string within the field w/
a
","
and
" " between them. I have a few more question, but will start w/
this
on
first.