Left Margin Problem

[Bob]

I have 3 windows in Owner Information(1)Title (2) First Name (3) Last Name
If I use all 3 I get correct left alignment in my Invoice statement, but if
I leave Title empty I get a 2mm gap and if I leave Title and First Name
blank I get a 4mm gap to the left, is there something I can add to the code
so First and last Names left align
Else
.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")
.Fields("OwnerName") = Nz(recOwnersInfo.Fields("OwnerTitle"),
"") _
& " " & Nz(recOwnersInfo.Fields("OwnerFirstName"), "") & " " _
& Nz(recOwnersInfo.Fields("OwnerLastName"), "")
'***
.Fields("OwnerAddress") = recOwnersInfo.Fields("OwnerAddress")
End If


Thanks in advance.........Bob Vance

 

[Wayne Morgan]

I sounds as if you may have leading spaces. Try the Trim() function.

Example:
..Fields("OwnerName") = Trim(Nz(recOwnersInfo.Fields("OwnerTitle"), "") _
& " " & Nz(recOwnersInfo.Fields("OwnerFirstName"), "") & " " _
& Nz(recOwnersInfo.Fields("OwnerLastName"), ""))

The problem is coming from the space you're putting in between the names. If
the names aren't there, the spaces still are. While the Trim() will remove
the leading and trailing spaces (although Access will remove the trailing
spaces automatically), it won't get rid of the double space in the middle if
you have an OwnerTitle but no OwnerFirstName. You could use an IIf()
function to only put the space in when there is a name, create a user
defined function to change double spaces to single spaces, or use a small
trick that takes advantage of Null values in an equation.

Example of the Null value trick. This will only work if there are NO numbers
in the string, including anything that Access may think it can convert to a
number.

..Fields("OwnerName") = recOwnersInfo.Fields("OwnerTitle") + " " _
& recOwnersInfo.Fields("OwnerFirstName") + " " _
& Nz(recOwnersInfo.Fields("OwnerLastName"), "")

IIf() Example:
..Fields("OwnerName") = IIf(IsNull(recOwnersInfo.Fields("OwnerTitle")), "",
recOwnersInfo.Fields("OwnerTitle") & " ") _
& IIf(IsNull(recOwnersInfo.Fields("OwnerFirstName")), "",
recOwnersInfo.Fields("OwnerFirstName") & " ") _
& Nz(recOwnersInfo.Fields("OwnerLastName"), "")

User defined function example:
Public Function RemoveDoubleSpaces(strInput As String) As String
Do Until InStr(strInput, " ") = 0 '2 spaces between the quotes
strInput = Replace(strInput, " ", " ") '2 spaces, 1 space
Loop
RemoveDoubleSpaces = strInput
End Function

Then to call it:
..Fields("OwnerName") =
RemoveDoubleSpaces(Trim(Nz(recOwnersInfo.Fields("OwnerTitle"), "") _
& " " & Nz(recOwnersInfo.Fields("OwnerFirstName"), "") & " " _
& Nz(recOwnersInfo.Fields("OwnerLastName"), "")))

 

[Bob]

Thanks Wayne will relay to my coder friend....Thanks Bob