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

  • Thread starter Thread starter Tony Williams
  • Start date Start date
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
 
Set the control source property to

=DLookup("[txtunitname]","[tbladdresses]","txtcode = '" & cmbbranch & "'")
 
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...
 
=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...
 
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...
 
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...
 
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...
 
Back
Top