Multiple Iif statements?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I am struggling to generate a code from two other fields based on a
selection from a drop down box...

qrySiteServices
ContractNo (Eg. 000321)
SiteNo (Eg. 01)

A combo box enables me to choose one of the following services: -
Alarm, Mobile, Keys or Static

So, if I choose 'Alarm' in the drop down box I would like to generate a
ServiceCode of "A.321-01" by adding the 'A' from Alarm, extracting the last
three digits from the ContractNo and then adding the two digits from the
SiteNo.
Is this possible?

Thanks.
 
yes, but not by using the IIf() function. if you're trying to generate the
"code" in a calculated field in the query, use

Code: Left([Forms]![FormName]![ComboBoxName], 1) & "." & Right([ContractNo],
3) & "-" & [SiteNo]

take a look at the Left() and Right() functions in Access Help, and search
the Help for topics about "concatenation".

hth
 
Hi Tina, thanks for replying...
Good news = I managed to return the code by using: -
ServiceCode: Left([ServiceID],1) & Right([ContractNo],3) & [SiteNo]

Bad news = It displays the code in the initial query window where I put the
expression but it doesn't appear on the table.

It must have something to do with the drop down box!
Any suggestions?

Thanks.

tina said:
yes, but not by using the IIf() function. if you're trying to generate the
"code" in a calculated field in the query, use

Code: Left([Forms]![FormName]![ComboBoxName], 1) & "." & Right([ContractNo],
3) & "-" & [SiteNo]

take a look at the Left() and Right() functions in Access Help, and search
the Help for topics about "concatenation".

hth


BetaMike said:
Hi all,
I am struggling to generate a code from two other fields based on a
selection from a drop down box...

qrySiteServices
ContractNo (Eg. 000321)
SiteNo (Eg. 01)

A combo box enables me to choose one of the following services: -
Alarm, Mobile, Keys or Static

So, if I choose 'Alarm' in the drop down box I would like to generate a
ServiceCode of "A.321-01" by adding the 'A' from Alarm, extracting the last
three digits from the ContractNo and then adding the two digits from the
SiteNo.
Is this possible?

Thanks.
Click to expand...
Click to expand...
 
Hmm, getting closer but I'm not quite there yet

SQL
SELECT tbl4SitesServices.ContractNo, tbl4SitesServices.SiteNo,
tbl4SitesServices.ServiceID, Left(tbl3Services.ServiceCode,1) & "." &
Right(tbl4SitesServices.ContractNo,3) & "-" & tbl4SitesServices.SiteNo AS
AIsCode FROM tbl3Services INNER JOIN tbl4SitesServices ON
tbl3Services.ServiceID=tbl4SitesServices.ServiceID;

So, I choose 'Alarm' in the 'ServiceID' combo box but I am then prompted
with another combo box where I have to choose the following four options: -
A.321-01
M.321-01
K.321-01
S.321-01

How do I automatically display the correct value based on the first option I
choose in the 'ServiceID' combo box instead of being presented with another
combo???

Thanks.

tina said:
yes, but not by using the IIf() function. if you're trying to generate the
"code" in a calculated field in the query, use

Code: Left([Forms]![FormName]![ComboBoxName], 1) & "." & Right([ContractNo],
3) & "-" & [SiteNo]

take a look at the Left() and Right() functions in Access Help, and search
the Help for topics about "concatenation".

hth


BetaMike said:
Hi all,
I am struggling to generate a code from two other fields based on a
selection from a drop down box...

qrySiteServices
ContractNo (Eg. 000321)
SiteNo (Eg. 01)

A combo box enables me to choose one of the following services: -
Alarm, Mobile, Keys or Static

So, if I choose 'Alarm' in the drop down box I would like to generate a
ServiceCode of "A.321-01" by adding the 'A' from Alarm, extracting the last
three digits from the ContractNo and then adding the two digits from the
SiteNo.
Is this possible?

Thanks.
Click to expand...
Click to expand...
 
Hi all,
I am struggling to generate a code from two other fields based on a
selection from a drop down box...

qrySiteServices
ContractNo (Eg. 000321)
SiteNo (Eg. 01)

A combo box enables me to choose one of the following services: -
Alarm, Mobile, Keys or Static

So, if I choose 'Alarm' in the drop down box I would like to generate a
ServiceCode of "A.321-01" by adding the 'A' from Alarm, extracting the last
three digits from the ContractNo and then adding the two digits from the
SiteNo.
Is this possible?
Click to expand...

Yes... but IMHO it's not desirable. This is called a "composite key"
or "intelligent key", and it's redundant. For one thing you have no
guarantee that it's unique (consider the Alarm for contracts 1321 and
2321 at Site 01); for another, it's pointless, since you already HAVE
that information available in the other three fields.

Just calculate this field as needed by concatenating the components.
There's no point in storing it in any table.

John W. Vinson[MVP]
 
Hi John,
I understand what you are saying but I can't get my head around it properly,
even though I think you are correct ;)

Store the following fields in the Table?
000321, 01, Alarm,
000321, 01, Mobiles,
000321, 02, Static,

Using the above fields, generate the following codes in the Query?
A.000321-01
M.000321-01
S.000321-02

Then use these concatenated codes produced in the Query for Reporting
purposes?

Thanks.
 
correct. anytime you're storing the "raw" data you need to generate
concatenated values, you can generate the values at will, in a query bound
to a form or report, or directly in the form or report (though creating a
calculated field at the query level is usually more desirable).

hth
 
Back
Top