concatenate string with "and" before last entry

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mariah - 05 Nov 2007 15:56 GMT

Concatenate has opened a whole new world, but I have one question:

I am using Concantenate to create a string of roles together for a letter.
For example "Thanks for serving as Role1, Role2".

Is it possible to tell the report to insert an "and" before the last role so
it would read: "Thanks for serving as Role1, and Role2". Some of our members
have served in over 15 roles, but either way the letter may not pass the
board if it doesn't have the "and".

I am not sure what information would be useful, so I will offer this at this
time:
My concatenate query:

SELECT tblCustomerAddresses.[ID Number], tblCustomerAddresses.Title,
tblCustomerAddresses.MailAddress1, tblCustomerAddresses.MailAddress2,
tblCustomerAddresses.MailCity, tblCustomerAddresses.MailState,
tblCustomerAddresses.MailZip, tblCustomerAddresses.Country,
tblCustomerAddresses.FirstName, tblCustomerAddresses.LastName,
Concatenate("SELECT SpecialRole1 FROM tblJunctionNamesRoles WHERE NameLookup
= " & [ID Number]) AS Roles
FROM tblCustomerAddresses;

Thanks ahead of time for your help, I am really amazed by the talent on this
board!

Reply to this Message

Klatuu - 05 Nov 2007 18:44 GMT

In the Print event of the section of the report your list or roles will be
in, modify it using this expression:
Me.txtRoles = left(Me.txtRoles,instrrev(Me.txtRoles,",")-1) &
replace(Me.txtRoles,",", " And",instrrev(Me.txtRoles,","))

Where txtRoles is the text box bound to the Roles field in the query.

Signature

Dave Hargis, Microsoft Access MVP

> Concatenate has opened a whole new world, but I have one question:
>
[quoted text clipped - 21 lines]
> Thanks ahead of time for your help, I am really amazed by the talent on this
> board!

Reply to this Message

Mariah - 06 Nov 2007 14:50 GMT

Thank you so much for your reply! I really appreciate the help.
I must warn you I am fairly new to this world called "Database".

I opened the report and then opened the properties to the text box where I
have the string of roles, clicked on "print event" and added the string to
the code. There wasn't much there to start:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

End Sub.

When I hit the "view" button to see the report, it gave me this error:
You can't assign a value to this object.

I wonder if this is because I have a string of text (the start of the
letter) the added roles, and then the rest of the letter in the single box?
Or perhaps I misunderstood where it should go?

Please let me know if/what type of information you need from me.

I really appreciate your spending time assisting me with this challenge.

> In the Print event of the section of the report your list or roles will be
> in, modify it using this expression:
[quoted text clipped - 28 lines]
> > Thanks ahead of time for your help, I am really amazed by the talent on this
> > board!

Reply to this Message

Klatuu - 06 Nov 2007 15:06 GMT

The code I posted earlier should go in the event sub:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.txtRoles = left(Me.txtRoles,instrrev(Me.txtRoles,",")-1) &
replace(Me.txtRoles,",", " And",instrrev(Me.txtRoles,","))
End Sub.

As to your error, what line of code does the error occur on?

Signature

Dave Hargis, Microsoft Access MVP

> Thank you so much for your reply! I really appreciate the help.
> I must warn you I am fairly new to this world called "Database".
[quoted text clipped - 49 lines]
> > > Thanks ahead of time for your help, I am really amazed by the talent on this
> > > board!

Reply to this Message

Mariah - 06 Nov 2007 15:45 GMT

Ok, so it looks like I put it in the right place. The error is on the line
you sent, with my changes, so I suspect I interpreted it incorrectly.

right now I have:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.txtLetterBody = Left(Me.txtLetterBody, InStrRev(Me.txtLetterBody, ",") -
1) & Replace(Me.txtLetterBody, ",", " And", InStrRev(Me.txtLetterBody, ","))
End Sub

The name of my textbox is txtLetterBody (because it contains the string of
text and the roles all together).

I tried replacing "Me" with the name of the report and that didn't work. I
ended up with a run-time error 424 Object required.

I am doing this in the "details" section of the report as that is the area
that my text box is in.

The text box has in it: =Trim("As we celebrate... . Thank you for serving
as " & [Roles] & ". " & "
May the...")

I know I must not be translating correctly to my own situation, so I thank
you for your patience and your time with this as I continue to learn. If you
need any other information please let me know.

Thanks again!

> The code I posted earlier should go in the event sub:
>
[quoted text clipped - 58 lines]
> > > > Thanks ahead of time for your help, I am really amazed by the talent on this
> > > > board!

Reply to this Message

Klatuu - 06 Nov 2007 16:46 GMT

I don't understand why it is not working. Try moving it from the print event
to the format event.

Signature

Dave Hargis, Microsoft Access MVP

> Ok, so it looks like I put it in the right place. The error is on the line
> you sent, with my changes, so I suspect I interpreted it incorrectly.
[quoted text clipped - 86 lines]
> > > > > Thanks ahead of time for your help, I am really amazed by the talent on this
> > > > > board!

Reply to this Message

Mariah - 06 Nov 2007 17:25 GMT

Hmm... that didn't do it either. I ended up with the same error.
-2147352567 (80020009) "You can't assign a value to this object"

I do appreciate your help. I will keep sleuthing around, and keep checking
back in case there are any other ideas. It would be awesome to get this
working.

Thanks again

> I don't understand why it is not working. Try moving it from the print event
> to the format event.
[quoted text clipped - 89 lines]
> > > > > > Thanks ahead of time for your help, I am really amazed by the talent on this
> > > > > > board!

Reply to this Message