conditional field

[David Gerstman]

I have a sheet with the following information

Team W L PCT Pyth %
Baltimore 38 49 0.437 0.48588214748205
Boston 53 34 0.609 0.612495589772804
Chicago Sox 39 47 0.453 0.415344230998688
Cleveland 52 36 0.591 0.564140709038061
Detroit 52 34 0.605 0.612782350342339
Kansas City 38 50 0.432 0.458356141848638

I would like to add a column that will compare PCT and Pyth %. If the former
is greater than the latter that field will have the term "Underrated" else it
will have the term "overrated." How do add a field conditionally? Is that
something that's done in design view?

 

[Arvin Meyer [MVP]]

You do this in a query. As a matter of fact, the percentages can and should
also be calculated in a query. Just add a query column:

Result: IIf([PCT] > [Pyth %], "Underrated", "overrated")

 

[David Gerstman]

Thank you very much.
And I assume I could nest the IIF statements if I had two criteria I need to
fulfill?

David

 

[Arvin Meyer [MVP]]

Yes you may. You might also look at the Choose() and Switch() functions in
VBA help, and a Select Case statement if you'd like to write your own custom
function to run in an Access query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thank you very much.
And I assume I could nest the IIF statements if I had two criteria I need
to
fulfill?

David

You do this in a query. As a matter of fact, the percentages can and
should
also be calculated in a query. Just add a query column:

Result: IIf([PCT] > [Pyth %], "Underrated", "overrated")

 

[David Gerstman]

Again thanks.

One last thing: How can I format the newly generated fields? (I followed
your advice and did the caluclations in the query ( e.g. pct: [W]/([W]+[L])
). Say I only want 3 decimal places or I want to round to the nearest
integer? Can I do that in the query or do I need VBA for that. (There's
nothing in the format menu for adjusting number formats.)

David

 

[Arvin Meyer [MVP]]

You can use both Standard and your own custom VBA functions in your Access
queries. Other than some limited ability in SQL-Server, Access is the only
DBMS that allows that. Check into the Round () and Format() functions in VBA
help. They will probably fill your requirements.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Again thanks.

One last thing: How can I format the newly generated fields? (I followed
your advice and did the caluclations in the query ( e.g. pct:
[W]/([W]+[L])
). Say I only want 3 decimal places or I want to round to the nearest
integer? Can I do that in the query or do I need VBA for that. (There's
nothing in the format menu for adjusting number formats.)

David

Yes you may. You might also look at the Choose() and Switch() functions
in
VBA help, and a Select Case statement if you'd like to write your own
custom
function to run in an Access query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

news:[email protected]...