Adding space to text, VBA, Access 07

[pteare]

Hello,

I am using access 07 and have some VBA code which puts some text into a memo field for me. It gets the text from various columns of a table.

Currently it produces text that looks like the below:

phil - hire - advanced
jonathan - lessons - beginner
claire - lift pass - local area

etc

Does anyone know of a good resource for info on code for formatting the text? So far I've discovered vbCrLf, that's it! What I'd ideally want is just to be able to line up the text into columns and also if there are any features I could use here (e.g. the option to line up to the right hand side of the words or to the left hand side?).

This will be a template email for billing guests so I'm just trying to work out how to make it look good. It needs to stay just as text for the way I'll be emailing it.

Any ideas on where to look to find out what's possible and what the code is?

many thanks,

Phil.

 

[georgedwilkinson]

I saw something pretty slick in one of my Access books the other day involving the use of String() and Len(). It output a nicely formatted report in the immediate window.

Speaking of reports, why not just make a report? It takes seconds to go through the report wizard and Microsoft has spent gazillions of dollars on it. Surely, they can do a much better job with it than we can with VB?

 

[nIGHTmAYOR]

your question is similler to :
I wanna shave , does anyone know of any good movie i can shave with ?

first of all memo fields are hidious fields that have loads of problems in dealing with.
(You cant use a where condition on a memo field)

second if you have the same data stored in other fields why not concatinate them in a select statment than rather creating a new field storing duplicate data ?
(Expr1: [user_name] & " - " & "phone_number" & " - " & "blasblas" in any column of query)

third did you know you can format a report , insert all data everywhere you like within which then useing a wizardy buttons create a script that would mail your report through outlook ?

Please take no offence from me

 

[pteare]

hi george,

thanks for the reply. I store the emails I sent out to guests in a memo field. This function is to template an email to my guests letting them know what ski lessons they have booked. It needs to go into the memo field as this jsut templates the email then I tweak it manually, add on any personal stuff as needed before hitting send.

Thanks for the thoughts, so from that it seems I would right a function to look at the length of words then add the right number of spaces in. Thanks for the imput, I shall go and try that and let you know how I get on!

Many thanks,

Phil.

 

[pteare]

thanks, of course no offence taken at all! I'm very grateful for the ideas.
Right, I'll get stuck into that too! thanks, phil.

 

[georgedwilkinson]

In the meantime, I looked up the code I saw the other day. It is not format centric so it won't help to post it here.

In essence:

Debug.Print MyVariable & String(x - Len(MyVariable)," ")

 

[pteare]

Thanks, that worked a treat. Here's my working code, just for anyone looknig through who had the same problem.

I created 2 public functions:

Public Function Column15(strText As String)

Column15 = strText & Space(15 - Len(strText))

End Function

Public Function ColumnRight15(strText As String)

ColumnRight15 = Space(15 - Len(strText)) & strText

End Function

These add spaces to the text to make it a total of 15 charaters wide.

Then I just called it from my main code:

rs1.MoveFirst
Do Until rs1.EOF
    strCol1 = Column15(rs1!guestname)
    email.Value = email.Value & vbCrLf & _
                Column15(rs1!guestname) & ColumnRight15(rs1!extratype) & Column15(rs1!extraname)
    rs1.MoveNext
Loop
rs1.Close

Thanks everyone for the help. Right, off to research reports next....

Phil.