Incorporate a space into a string

[Bob]

I want to incorporate (" ") this into this part of the code because it is
giving me a gap at the start when there is no First Name to display and Only
Last Name!

Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") & .................OwnerLastName......Code

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

 

[Douglas J. Steele]

Take advantage of the fact that "A" + Null yields Null, while "A" & Null
yields "A":

(Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) + " ") & .................OwnerLastName......Code

 

[Bob]

Thanks Douglas, but I am still getting a blank space before last name if
there is no first name e.g.:
|Douglas Steele
| Steele

Thanks for your help.......Bob

Take advantage of the fact that "A" + Null yields Null, while "A" & Null
yields "A":

(Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) + " ") & .................OwnerLastName......Code


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I want to incorporate (" ") this into this part of the code because it is
giving me a gap at the start when there is no First Name to display and
Only Last Name!

Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") &
.................OwnerLastName......Code

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

 

[JK]

Bob,

Just trim the whole string asf:

Trim(Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") & .................OwnerLastName......Code
.......)

This will eliminate leading spaces if any

Regards/JK

 

[Bob]

JK your a Genius thanks for your help, Trim Ah!!!
Thanks Bob

Bob,

Just trim the whole string asf:

Trim(Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") &
.................OwnerLastName......Code
......)

This will eliminate leading spaces if any

Regards/JK

I want to incorporate (" ") this into this part of the code because it is
giving me a gap at the start when there is no First Name to display and
Only Last Name!

Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") &
.................OwnerLastName......Code

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

 

[Douglas J. Steele]

That implies that you're storing a zero-length string (""), rather than a
Null when you don't have a first name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas, but I am still getting a blank space before last name if
there is no first name e.g.:
|Douglas Steele
| Steele

Thanks for your help.......Bob

Take advantage of the fact that "A" + Null yields Null, while "A" & Null
yields "A":

(Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) + " ") &
.................OwnerLastName......Code


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I want to incorporate (" ") this into this part of the code because it
is giving me a gap at the start when there is no First Name to display
and Only Last Name!

Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") &
.................OwnerLastName......Code

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

 

[Guest]

Bob:

I know you've solved this with another approach already, but the reason its
not working the way Douglas suggested is that you are calling the Nz function
in the expression to return a zero-length string, so even if the
OwnerFirstName column is NULL the space won't be suppressed because there is
never a NULL to propagate. If the Nz function call is removed it should work:

(Left(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID]),1) + " ") & .................OwnerLastName......Code

Left(NULL,1) returns NULL, so Left(NULL,1) + " " will evaluate to NULL
because of the propagation of NULL, thus suppressing the redundant space
character. The advantage of this approach over calling the Trim function is
that it will suppress other characters than spaces. Say you are returning
two values in the format 'Lastname, FirstName', if you use:

LastName & (", " + FirstName)

both the comma and the space will be suppressed if FirstName is NULL.

Ken Sheridan
Stafford, England

Thanks Douglas, but I am still getting a blank space before last name if
there is no first name e.g.:
|Douglas Steele
| Steele

Thanks for your help.......Bob

Take advantage of the fact that "A" + Null yields Null, while "A" & Null
yields "A":

(Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) + " ") & .................OwnerLastName......Code


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I want to incorporate (" ") this into this part of the code because it is
giving me a gap at the start when there is no First Name to display and
Only Last Name!

Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") &
.................OwnerLastName......Code

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

 

[Douglas J. Steele]

Oops.

That was my fault, Ken: I didn't pay that much attention when recopying what
Bob had.

Thanks for the save.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Bob:

I know you've solved this with another approach already, but the reason
its
not working the way Douglas suggested is that you are calling the Nz
function
in the expression to return a zero-length string, so even if the
OwnerFirstName column is NULL the space won't be suppressed because there
is
never a NULL to propagate. If the Nz function call is removed it should
work:

(Left(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID]),1) + " ") & .................OwnerLastName......Code

Left(NULL,1) returns NULL, so Left(NULL,1) + " " will evaluate to NULL
because of the propagation of NULL, thus suppressing the redundant space
character. The advantage of this approach over calling the Trim function
is
that it will suppress other characters than spaces. Say you are returning
two values in the format 'Lastname, FirstName', if you use:

LastName & (", " + FirstName)

both the comma and the space will be suppressed if FirstName is NULL.

Ken Sheridan
Stafford, England

Thanks Douglas, but I am still getting a blank space before last name if
there is no first name e.g.:
|Douglas Steele
| Steele

Thanks for your help.......Bob

Take advantage of the fact that "A" + Null yields Null, while "A" &
Null
yields "A":

(Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) + " ") &
.................OwnerLastName......Code


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)




I want to incorporate (" ") this into this part of the code because it
is
giving me a gap at the start when there is no First Name to display
and
Only Last Name!

Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") &
.................OwnerLastName......Code

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

 

[Bob]

this is working? can you see anything wrong?....thanks bob

=IIf(DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" & [tbOwnerID])="" Or
IsNull(DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" &
[tbOwnerID]) & " ") &
Trim(Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") &
IIf(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID])="" Or
IsNull(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])))

 

[Bob]

I decide to add an additional field on my OwnerInfo called OwnerFirstNameInt
so I could change OwnerFirstName to my new field and change the name on the
string, works good because you might have to have Mr T.G. Williams and the
new code wont allow that, But I have struck a snag I have 2 other reports
showing the same name and I cant alter them to my new code. They both have a
source control [OwnerName] is there any way I can change this to my new code
with OwnerFirstNameInt showing instead of
OwnerFirstName...............Thanks Bob for your help

 

[Guest]

I'd see this as a 'road to Dublin' question, and would backtrack a bit.
Rather than called the DLookup function in the report to get data from the
tblOwnerInfo table I'd simply join that table to whatever table(s) your
report is currently based on and use the query as the report's RecordSource.
You can then return the various columns from the tblOwnerInfo table in the
query, which is far more efficient than calling the DLookup function
repeatedly in the report.

To compute the concatenated full name you can either do this with an
expression as the ControlSource of a text box in the reports, or you can
compute it in the query, which would be the approach I'd adopt. To return
all the columns from a Table MyTable say joined to the tblOwnerInfo table,
and return the full name from that in a computed column the query would go
something like that below, for which I've assumed the table contains a column
OwnerMiddleName (if it just contains the middle intitial it’s a trivial task
to amend the query)

SELECT MyTable.*,
((OwnerTitle + " ") &
((Left(OwnerFirstName,1) + ".") &
(Left(OwnerMiddleName,1) + ".")) + " ") &
OwnerlastName AS OwnerName
FROM MyTable INNER JOIN tblOwnerInfo
ON MtTable.OwnerID = tblOwnerInfo.OwnerID;

If all 4 columns contain values this would return an OwnerName column such
as Mr K.W. Sheridan from values Mr | Kenneth | William | Sheridan. If any
column is Null the result would take account of this, e.g. Mr K. Sheridan, K.
Sheridan, K.W. Sheridan, Mr Sheridan or simply Sheridan

Ken Sheridan
Stafford, England

 

[JK]

My pleasure Bob

JK your a Genius thanks for your help, Trim Ah!!!
Thanks Bob

Bob,

Just trim the whole string asf:

Trim(Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") &
.................OwnerLastName......Code
......)

This will eliminate leading spaces if any

Regards/JK

I want to incorporate (" ") this into this part of the code because it
is giving me a gap at the start when there is no First Name to display
and Only Last Name!

Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") &
.................OwnerLastName......Code

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