conditional formats using values from another control/form

[uscitizen]

i know you can easily condition the format of a field based on its values but what i'd like to do seems to extend outside the power of the condional format menu item. basically, i'd like to display in red the values of a laboratory test on a form when they fall outside the normal ranges for a) males and when they fall outside the normal ranges for b) females. now since you're either a male or a female, and the patient's gender is entered ('M' or 'F') on a different form the questions becomes how do you ask Access to read the gender and tell the lab test field display itself the color red when the value therein is not 14 to 18 for males or to go to red when the value therein is not 12 to 16 for females?

 

[Zathras]

Conditional formatting allows up to three conditions that you can specify. This application requires two. In the example, the gender code is in column A and the weights are in column B. Select column B (the entire column) and enter in the following conditional format formulas:

Condition 1:[blue]

  =AND(A1=&quot;M&quot;,OR(B1<150,B1>250))

[/color]
Condition 2:[blue]

  =AND(A1=&quot;F&quot;,OR(B1<100,B1>200))

[/color]
Select a different color for each condition and you should see what you want. Adjust the limit ranges to the real numbers you need.

 

[uscitizen]

'but' the field you're calling 'A1' (gender) is not to be found on the same form as the field you're calling 'B1'? doesn't that fact prevent you from using conditional formatting?

 

[Bowers74]

I'm confused here . . .

uscitizen (Me too!):

Are you using Access or Excel?



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[uscitizen]

I'm using MS Access. Did I post this to the Excel group?

 

[Zathras]

Can you use a hidden column on the sheet for which you want conditional formatting, and have formulas to reference the gender cells from the other sheet? [blue]

  A1: =Sheet2!A1
  A2: =Sheet2!A2
    etc.

[/color]
If not, then no you can't use conditional formatting and you may have to resort to VBA.

 

[uscitizen]

I'm curious as to why you generous folks are reading the VBA forum and sending me useful suggestions (as if I were Excel)?

Zathras: Nope, I'm not using Exel so your useful replies don't seem to be applicable.

Thanks a bunch (anyway).

And the beat goes on.......

 

[Zathras]

Well, for one, there are several Access-specific fora (to which you subscribe) in which you might have asked this question. Instead you chose to ask it here where the application used is commonly one of the other Office products, of which Excel is arguably the most frequent.

For another, the concept of "conditional formatting" has meaning within the context of Excel, whereas I cannot find anything in Access 97 that calls itself "Conditional Format Menu"

You will get better answers in the future if you specify the environment you are working in and the application you are using, instead of making us guess.

 

[uscitizen]

Point taken (from Zarthras) -- and would add that I'm not using Access '97: 2000 has this capability -- that should straighten things out a little more.

Thanks again.

(Still wish this had a straightforward solution, though)