Make all lettesr uppercase!

[Bob]

--

Can I add to this to make all the 3 letters uppercase?...Thanx Bob
=Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3)

 

[Guest]

....all the 3 letters ???
No idea what you mean by this.

Your formula appears to be the control source for a textbox on a form or a
report. It will display the first match found (and no other possible
matches). Because it is a calculated control, you will not be able to change
(edit) a displayed value. You can apply a format to force the display to be
all uppercase, but this will not change the underlying data. It only affects
the way that the data is displayed. The easiest way is to set a format for
the field in table design view. Enter a greater than symbol (ie. ">" without
the quotes) to display the field as all uppercase.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Can I add to this to make all the 3 letters uppercase?...Thanx Bob

=Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3)

 

[John Vinson]

Can I add to this to make all the 3 letters uppercase?...Thanx Bob

=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = "
& [tbOwnerID] & ""),""),3))

Sort of a useless calculated field IMO - JOH, JOH, JOH, JOH and JOH
for Johnson, Johnston, Johannsen, Johanna and Johnson respectively!

John W. Vinson[MVP]

 

[Bob]

Sorry John it has another list box beside it:
=NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" & tbOwnerID.Value),"")
So you do get unique codes JOH23 JOH34.......Thanks Bob
I suppose you couldn't put them all in one code?

Can I add to this to make all the 3 letters uppercase?...Thanx Bob

=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = "
& [tbOwnerID] & ""),""),3))

Sort of a useless calculated field IMO - JOH, JOH, JOH, JOH and JOH
for Johnson, Johnston, Johannsen, Johanna and Johnson respectively!

John W. Vinson[MVP]

 

[John Vinson]

Sorry John it has another list box beside it:
=NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" & tbOwnerID.Value),"")
So you do get unique codes JOH23 JOH34.......Thanks Bob
I suppose you couldn't put them all in one code?

Well... you could; but I wouldn't. Such "Intelligent Keys" turn out to
be pretty near useless in practice. They are NO more unique than the
unique tbOwnerID is in the first place; they are not mnenomic (is
JOH23 Sarah Johnson or Ken Johns? without going to the database you
can't tell); they are vulnerable to name changes (when Sara marries
Steve Zabrowski does JOH23 become ZAB23?)

They maybe made sense when a table could only have one searchable key
(or when data was kept in file folders that needed a short unique
identifier); but IMO they're of very little use in a modern database.
Just store the unique ID, and keep it concealed; use combo boxes with
first name, last name, and enough additional fields to resolve
duplicate names (such as address, department, phone).

John W. Vinson[MVP]

 

[JK]

hi Bob,

The same thing as John said, just apply the Ucase function to the whole
expression
=Ucase(NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" &
tbOwnerID.Value),""))
Regards/JK

Sorry John it has another list box beside it:
=NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" & tbOwnerID.Value),"")
So you do get unique codes JOH23 JOH34.......Thanks Bob
I suppose you couldn't put them all in one code?

Can I add to this to make all the 3 letters uppercase?...Thanx Bob

=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = "
& [tbOwnerID] & ""),""),3))

Sort of a useless calculated field IMO - JOH, JOH, JOH, JOH and JOH
for Johnson, Johnston, Johannsen, Johanna and Johnson respectively!

John W. Vinson[MVP]

 

[Bob]

Thanks Guys the good thing about being a male is that when I got divorced I
could still keep my id VAN8

hi Bob,

The same thing as John said, just apply the Ucase function to the whole
expression
=Ucase(NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" &
tbOwnerID.Value),""))
Regards/JK

Sorry John it has another list box beside it:
=NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" & tbOwnerID.Value),"")
So you do get unique codes JOH23 JOH34.......Thanks Bob
I suppose you couldn't put them all in one code?

Can I add to this to make all the 3 letters uppercase?...Thanx Bob

=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = "
& [tbOwnerID] & ""),""),3))

Sort of a useless calculated field IMO - JOH, JOH, JOH, JOH and JOH
for Johnson, Johnston, Johannsen, Johanna and Johnson respectively!

John W. Vinson[MVP]

 

[JK]

Just as well, I would have been worried if your code was VAC8

Thanks Guys the good thing about being a male is that when I got divorced
I could still keep my id VAN8

hi Bob,

The same thing as John said, just apply the Ucase function to the whole
expression
=Ucase(NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" &
tbOwnerID.Value),""))
Regards/JK

Sorry John it has another list box beside it:
=NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" & tbOwnerID.Value),"")
So you do get unique codes JOH23 JOH34.......Thanks Bob
I suppose you couldn't put them all in one code?


Can I add to this to make all the 3 letters uppercase?...Thanx Bob

=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = "
& [tbOwnerID] & ""),""),3))

Sort of a useless calculated field IMO - JOH, JOH, JOH, JOH and JOH
for Johnson, Johnston, Johannsen, Johanna and Johnson respectively!

John W. Vinson[MVP]

 

[Bob]

Very Good JK ))

Just as well, I would have been worried if your code was VAC8

Thanks Guys the good thing about being a male is that when I got divorced
I could still keep my id VAN8

hi Bob,

The same thing as John said, just apply the Ucase function to the whole
expression
=Ucase(NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" &
tbOwnerID.Value),""))
Regards/JK


Sorry John it has another list box beside it:
=NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" & tbOwnerID.Value),"")
So you do get unique codes JOH23 JOH34.......Thanks Bob
I suppose you couldn't put them all in one code?


Can I add to this to make all the 3 letters uppercase?...Thanx Bob

=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = "
& [tbOwnerID] & ""),""),3))

Sort of a useless calculated field IMO - JOH, JOH, JOH, JOH and JOH
for Johnson, Johnston, Johannsen, Johanna and Johnson respectively!

John W. Vinson[MVP]