Label Question

[jad2]

I have one field for Last Name, First Name and MI. I need to print labels First Name, Last Name and MI. Is there any way to switch the text within the field on my label report? I looked at LTRIM and RTRIM but they only work with a specific # of characters. Right now I have to export the report into Monarch and use LSPLIT command and then I put it back into access to run the labels. Any help would be appreciated...Thank you

 

[chrisk]

This assumes that there is a space between the words in your label field [yourfieldname].

Add 3 unbound fields (txtFirst, txtSecond and txtThird) to the report, set Visible = No and their control sources as follows:

txtFirst: = Instr(1,[yourfieldname]," ")
txtSecond: = Instr([txtFirst]+1,[yourfieldname]," ")
txtThird: = Len([yourfieldname])

Then create 3 more unbound fields (txtFirstName, txtLastName and txtMI) to print and set the control sources to:

txtLastName: = Left([yourfieldname],[txtFirst]-1)
txtFirstName: = Mid([yourfieldname],[txtFirst]+1,[txtSecond]-[txtFirst]-1)
txtMI: = Mid([yourfieldname],[txtSecond]+1,[txtThird])

If there is a different character separating the parts of your field, change the bit in quotes in the InStr functions. You can also omit the 3 invisible text boxes and include their expressions in the control sources of the 3 visible ones if you prefer, but I find this to be less complicated.

Chris.

 

[jad2]

Thanks chrisk I appreciate your help..Your suggestion worked.