First 3 Letters in a text Box

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Is there some way I can alter this code or use properties so as only the
first 3 letters of the last name will show from the last name in the text
box!

=IIf(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID])="" Or
IsNull(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID]))




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

Take a look in Access HELP for the syntax on the Left() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
But Jeff I don't see where I can use it for a text box only a query and I
cant use the query on my Report..thanks Bob

Jeff Boyce said:
Bob

Take a look in Access HELP for the syntax on the Left() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Bob said:
Is there some way I can alter this code or use properties so as only the
first 3 letters of the last name will show from the last name in the text
box!

=IIf(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID])=""
Or
IsNull(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID]))




Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
 
Am I Close Jeff?
=IIf(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID])="" Or
IsNull(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])=Left([OwnerLastName],3))

Thanks Bob



Bob said:
But Jeff I don't see where I can use it for a text box only a query and I
cant use the query on my Report..thanks Bob

Jeff Boyce said:
Bob

Take a look in Access HELP for the syntax on the Left() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Bob said:
Is there some way I can alter this code or use properties so as only the
first 3 letters of the last name will show from the last name in the
text box!

=IIf(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID])=""
Or
IsNull(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID]))




Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
Click to expand...
 
Bob,
I don't quite understand where you want to use this, but there is a big
problem lurking here. The way you have the code construted, it required 4
DLookups. This will have a serious negative impact on performance. Here is
a version that does exactly the same thing, but required only one DLookup.
The only concern I have is what [tbOwnerID] is. Given the nameing of it, I
have doubts. It should be a control on your form.


=Left(Nz(DLookup("[OwnerLastName]", "tblOwnerInfo", "[OwnerID = '" & _
[tbOwnerID] & "'"),""), 3)

Bob said:
Am I Close Jeff?
=IIf(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID])="" Or
IsNull(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])=Left([OwnerLastName],3))

Thanks Bob



Bob said:
But Jeff I don't see where I can use it for a text box only a query and I
cant use the query on my Report..thanks Bob

Jeff Boyce said:
Bob

Take a look in Access HELP for the syntax on the Left() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP




Is there some way I can alter this code or use properties so as only the
first 3 letters of the last name will show from the last name in the
text box!

=IIf(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID])=""
Or
IsNull(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID]))




Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
Click to expand...
 
I cant use the query on my Report.
Click to expand...

Why ever not?

If you're assuming that your Report must be based on a Table without
using a query, revise that assumption. ALL reports are based on
Queries - usually queries created by the developer, but even if you
base a report on a Table, Access will create a query in the
background.

John W. Vinson[MVP]
 
Brilliant Klatuu, had to alter your code a little but got it Thanks for your
help much appreciated.....Bob
=Left(Nz(DLookup("[OwnerLastName]", "tblOwnerInfo", "[OwnerID] = " &
[tbOwnerID] & ""),""), 3)

Klatuu said:
Bob,
I don't quite understand where you want to use this, but there is a big
problem lurking here. The way you have the code construted, it required 4
DLookups. This will have a serious negative impact on performance. Here
is
a version that does exactly the same thing, but required only one DLookup.
The only concern I have is what [tbOwnerID] is. Given the nameing of it,
I
have doubts. It should be a control on your form.


=Left(Nz(DLookup("[OwnerLastName]", "tblOwnerInfo", "[OwnerID = '" & _
[tbOwnerID] & "'"),""), 3)

Bob said:
Am I Close Jeff?
=IIf(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID])=""
Or
IsNull(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])=Left([OwnerLastName],3))

Thanks Bob



Bob said:
But Jeff I don't see where I can use it for a text box only a query and
I
cant use the query on my Report..thanks Bob

Bob

Take a look in Access HELP for the syntax on the Left() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP




Is there some way I can alter this code or use properties so as only
the
first 3 letters of the last name will show from the last name in the
text box!

=IIf(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])=""
Or
IsNull(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID]))




Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top