Coding a complex field value

[Guest]

I’m a newby attempting to design a query to produce an invoice, and could
desperately use some expert coding advice. The calculation: IF
[PopulationDependent] is “YES†then [BracketBillingMultiple] needs to return
a value by determining if there is a (number) value in either
[CountyPopulation] OR [CityPopulation] (one or the other will always be
null). If that value in either population field is >1 and <5000, then the
value returned should equal “1â€; if it’s >5001 and <25000 then the value
should be “2â€. The entire calculation overwhelms my feeble brain. Can someone
give me the correct code snippet? Many thanks.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What is "[BracketBillingMultiple]"; a column, table, query...??

I'll assume it is the name of the query you want:

SELECT IIf(Nz(CountyPopulation, CityPopulation)
Between 1 and 5000, 1,
IIf(Nz(CountyPopulation, CityPopulation)
Between 5001 and 25000, 2)) As Result
FROM some_table_name
WHERE PopulationDependent = "Yes"

The Nz() function is used 'cuz your description implied that only one of
the columns CountyPopulation, CityPopulation would be NULL. Nz() will
return the value of the non-NULL value.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmVQBoechKqOuFEgEQLkUQCdF7hW88lEOAmHuRxSiSZHoufRV6AAoJ/P
e8/GioCiLbZupRzfsv3AeTCo
=YUz7
-----END PGP SIGNATURE-----

 

[Guest]

Thanks MG, can't tell you how much I appreciate the lesson. Actually,
[BracketBillingMultiple] is the field name where the result will go. Sorry.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What is "[BracketBillingMultiple]"; a column, table, query...??

I'll assume it is the name of the query you want:

SELECT IIf(Nz(CountyPopulation, CityPopulation)
Between 1 and 5000, 1,
IIf(Nz(CountyPopulation, CityPopulation)
Between 5001 and 25000, 2)) As Result
FROM some_table_name
WHERE PopulationDependent = "Yes"

The Nz() function is used 'cuz your description implied that only one of
the columns CountyPopulation, CityPopulation would be NULL. Nz() will
return the value of the non-NULL value.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmVQBoechKqOuFEgEQLkUQCdF7hW88lEOAmHuRxSiSZHoufRV6AAoJ/P
e8/GioCiLbZupRzfsv3AeTCo
=YUz7
-----END PGP SIGNATURE-----

I’m a newby attempting to design a query to produce an invoice, and could
desperately use some expert coding advice. The calculation: IF
[PopulationDependent] is “YES†then [BracketBillingMultiple] needs to return
a value by determining if there is a (number) value in either
[CountyPopulation] OR [CityPopulation] (one or the other will always be
null). If that value in either population field is >1 and <5000, then the
value returned should equal “1â€; if it’s >5001 and <25000 then the value
should be “2â€. The entire calculation overwhelms my feeble brain. Can someone
give me the correct code snippet? Many thanks.