Text Box Horizontal Sizing 2

[binglelmh]

I created a report in the format of a personal letter to employees of my company. I based it on a query which pulls employee names and addresses (and some other unrelated stuff). The first name, middle initial and last name are in separate fields (as are address, city , state and zip)

I need the text boxes to expand or shrink left or right so they don't leave big spaces between first name and middle initial, middle initial and last name etc.(caused by some names being shorter than others).

Can Grow and Can Shrink don't work horizontally. The text boxes are side by side to make the name and address print like they would on a personal letter.

I'm missing something really simple, right?

 

[Zonie32]

Maybe you could try using Trim...??? Put something like this in one text box.

=Trim([FName] & ", " & [MidName] & " " & [LName])

 

[Zonie32]

Sorry,,there should not be a comma in there. Remove it.

 

[binglelmh]

Thank you so much!!! When I tried it, I got an Error# message. Is it because I put CanShrink and CanGrow back to "No" for those name and address fields?

My field names in the text boxes (on the first line of my name/ address section) are: Member Fname, Member MI, and Member LName. In the first text box I typed Trim=([Member Fname]&" "&[Member MI]&" "&[Member LName])

Got the Error# message in the Member Fname text box on the report.

I made sure my text boxes did not overlap...don't know if this is important.

What am I doing wrong? Again...thanks for your time and consideration.

 

[ZmrAbdulla]

In the first text box I typed Trim=([Member Fname]&" "&[Member MI]&" "&[Member LName])

I think you have done a typing mistake there
[highlight]Trim=[/highlight]([Member Fname]&" "&[Member MI]&" "&[Member LName]) should be
=Trim([Member Fname]&" "&[Member MI]&" "&[Member LName])
you can use a single textbox instead of 3 textboxes
regards


Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[binglelmh]

I corrected it but I still get an #Error message. I tried it by leaving the two extra text boxes off to the side and also tried it by deleting the two redundent text boxes

The text boxes are in a group header section rather than the detail section of the report. Could that be the problem?

 

[dhookom]

Make sure the name of the text box is not the name of a field. If this doesn't correct your issue, come back with your current control source property.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[binglelmh]

Bingo !!!! Thanks to dhookom!!!

It worked perfectly when I changed the name of the field in properties.

I'm embarrassed to ask one more question, but here goes...
currently the names and addresses are all in caps because I imported the data in its original form from an Excel worksheet and could not figure out how to do Proper case in Excel (I do know how in Word though)

I know there is a thread that gives instruction for a Proper case function used in Access 2000, but now I'm afraid to use it because it might mess up the Trim statement.

Should I just start from scratch, go back to Excel, dump the worksheet into a Word table, change case on all the names and addresses, then put it back into Excel, then import to my tables?

I know that sounds pathetic but I don't know how to combine the Trim and the Proper case formulas.

 

[dhookom]

You can change the case of stored values use the StrConv() function in an update query. This may not return the desired results for names like "McKinley".

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[binglelmh]

I wrote the update query but I don't know the expression used to change names and addresses to what I call "Title Case" - Ex. I need to change "MARY K. MARTIN" to "Mary K. Martin".

StrConv(fldName,1) will give me all caps which is what I already have...won't it?

 

[dhookom]

Very close. Try update to:
StrConv(fldName,3)

I generally try all mass update queries on a backup copy prior to doing this with production data.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[binglelmh]

Thanks, I am definitely working on a backup copy.

Well, now you will know just what a newbie I am -

Here's what I typed- In Update to: StrConv(Member Fname,3)

Remember that "Member Fname" is the name of the field.

I ran it and in datasheet view it returned one column, many records long, and the value in each field was literally: Member Fname.

So now you know how humble my Access skills are. I must be missing some syntax in the expression, right?

Can't thank you enough....this must be like an act of love and devotion to the Access novitiates.

 

[binglelmh]

To: dhookom from: binglelmh - I figured it out!!!!!!

I left out my brackets around the fieldname.

Thank you again and again!!!!!

It works!!!!!!!!!!!!!!!!!!!!!!!!

Also thanks again to Zonie32 and ZmrAbdullah!!!!!

 

[dhookom]

It's probably time to step up your application development a level or two with a good, solid naming convention that doesn't allow spaces in field or object names.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]