Calculating the value of a control onthe last 2 digits of a combo

T

Tony Williams

I have a table (tbladdresses) of 4 digit branch codes (txtcode) and addresses
(txtunitname). The last 2 digits of the txtcode is the code for the
individual addresses (the first two are the area). I have a combobox
(cmbbranch)control on a form which shows the 4 digit code (txtcode) and I
want to populate another control on the form with the txtunitname relevant to
the 4 digit code the user chooses. I've tried using something like
DLookup("[txtunitname]","[tbladdresses]","Right([cmbbranch],2) but then got
lost!
Can someone help me please?
Thanks
Tony
 
R

RonaldoOneNil

Set the control source property to

=DLookup("[txtunitname]","[tbladdresses]","txtcode = '" & cmbbranch & "'")
 
T

Tony Williams

Will this account for the need to match only the last 2 digits? There will
be a number of areas with more than one branch eg the codes for say one area
could be AB01,AB02,AB56, AB47
Thanks Tony
RonaldoOneNil said:
Set the control source property to

=DLookup("[txtunitname]","[tbladdresses]","txtcode = '" & cmbbranch & "'")

Tony Williams said:
I have a table (tbladdresses) of 4 digit branch codes (txtcode) and
addresses
(txtunitname). The last 2 digits of the txtcode is the code for the
individual addresses (the first two are the area). I have a combobox
(cmbbranch)control on a form which shows the 4 digit code (txtcode) and I
want to populate another control on the form with the txtunitname
relevant to
the 4 digit code the user chooses. I've tried using something like
DLookup("[txtunitname]","[tbladdresses]","Right([cmbbranch],2) but then
got
lost!
Can someone help me please?
Thanks
Tony
Click to expand...
Click to expand...
 
R

RonaldoOneNil

=DLookup("[txtunitname]","[tbladdresses]","Right(txtcode,2) = '" &
Right(cmbbranch,2) & "'")

Tony Williams said:
Will this account for the need to match only the last 2 digits? There will
be a number of areas with more than one branch eg the codes for say one area
could be AB01,AB02,AB56, AB47
Thanks Tony
RonaldoOneNil said:
Set the control source property to

=DLookup("[txtunitname]","[tbladdresses]","txtcode = '" & cmbbranch & "'")

Tony Williams said:
I have a table (tbladdresses) of 4 digit branch codes (txtcode) and
addresses
(txtunitname). The last 2 digits of the txtcode is the code for the
individual addresses (the first two are the area). I have a combobox
(cmbbranch)control on a form which shows the 4 digit code (txtcode) and I
want to populate another control on the form with the txtunitname
relevant to
the 4 digit code the user chooses. I've tried using something like
DLookup("[txtunitname]","[tbladdresses]","Right([cmbbranch],2) but then
got
lost!
Can someone help me please?
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

Sorry Ronaldo, forgot to mention that the combobox has two columns and the
code is in the first column. How does that affect the Dlookup?
Sorry
Thanks for your help
Tony

RonaldoOneNil said:
=DLookup("[txtunitname]","[tbladdresses]","Right(txtcode,2) = '" &
Right(cmbbranch,2) & "'")

Tony Williams said:
Will this account for the need to match only the last 2 digits? There will
be a number of areas with more than one branch eg the codes for say one area
could be AB01,AB02,AB56, AB47
Thanks Tony
RonaldoOneNil said:
Set the control source property to

=DLookup("[txtunitname]","[tbladdresses]","txtcode = '" & cmbbranch & "'")

:

I have a table (tbladdresses) of 4 digit branch codes (txtcode) and
addresses
(txtunitname). The last 2 digits of the txtcode is the code for the
individual addresses (the first two are the area). I have a combobox
(cmbbranch)control on a form which shows the 4 digit code (txtcode) and I
want to populate another control on the form with the txtunitname
relevant to
the 4 digit code the user chooses. I've tried using something like
DLookup("[txtunitname]","[tbladdresses]","Right([cmbbranch],2) but then
got
lost!
Can someone help me please?
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
R

RonaldoOneNil

If the first column is the bound column is the bound column then the
statement is OK
You can change your DLookup to pick any column similar to below.

=DLookup("[txtunitname]","[tbladdresses]","Right(txtcode,2) = '" &
Right(cmbbranch.Column(1),2) & "'")

Tony Williams said:
Sorry Ronaldo, forgot to mention that the combobox has two columns and the
code is in the first column. How does that affect the Dlookup?
Sorry
Thanks for your help
Tony

RonaldoOneNil said:
=DLookup("[txtunitname]","[tbladdresses]","Right(txtcode,2) = '" &
Right(cmbbranch,2) & "'")

Tony Williams said:
Will this account for the need to match only the last 2 digits? There will
be a number of areas with more than one branch eg the codes for say one area
could be AB01,AB02,AB56, AB47
Thanks Tony
Set the control source property to

=DLookup("[txtunitname]","[tbladdresses]","txtcode = '" & cmbbranch & "'")

:

I have a table (tbladdresses) of 4 digit branch codes (txtcode) and
addresses
(txtunitname). The last 2 digits of the txtcode is the code for the
individual addresses (the first two are the area). I have a combobox
(cmbbranch)control on a form which shows the 4 digit code (txtcode) and I
want to populate another control on the form with the txtunitname
relevant to
the 4 digit code the user chooses. I've tried using something like
DLookup("[txtunitname]","[tbladdresses]","Right([cmbbranch],2) but then
got
lost!
Can someone help me please?
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

Great thanks worked !

RonaldoOneNil said:
If the first column is the bound column is the bound column then the
statement is OK
You can change your DLookup to pick any column similar to below.

=DLookup("[txtunitname]","[tbladdresses]","Right(txtcode,2) = '" &
Right(cmbbranch.Column(1),2) & "'")

Tony Williams said:
Sorry Ronaldo, forgot to mention that the combobox has two columns and the
code is in the first column. How does that affect the Dlookup?
Sorry
Thanks for your help
Tony

RonaldoOneNil said:
=DLookup("[txtunitname]","[tbladdresses]","Right(txtcode,2) = '" &
Right(cmbbranch,2) & "'")

:

Will this account for the need to match only the last 2 digits? There will
be a number of areas with more than one branch eg the codes for say one area
could be AB01,AB02,AB56, AB47
Thanks Tony
Set the control source property to

=DLookup("[txtunitname]","[tbladdresses]","txtcode = '" & cmbbranch & "'")

:

I have a table (tbladdresses) of 4 digit branch codes (txtcode) and
addresses
(txtunitname). The last 2 digits of the txtcode is the code for the
individual addresses (the first two are the area). I have a combobox
(cmbbranch)control on a form which shows the 4 digit code (txtcode) and I
want to populate another control on the form with the txtunitname
relevant to
the 4 digit code the user chooses. I've tried using something like
DLookup("[txtunitname]","[tbladdresses]","Right([cmbbranch],2) but then
got
lost!
Can someone help me please?
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need VBA Codes for a Huge Problem in MS Access 1
VBA Code for converting Type-E UPC to Type-A (barcodes) 1
FormCode - Converting Type-E UPC to Type-A (barcodes) 2
Passing value to a bound control 5
help with Control source 2
How do I sort several 4 digit numbers by the last 2 digits in exc. 4
Removing the last two digits of a cell 3
Referencing a control on a sub form (Post 2) help, please? 13

Top