CrossTab Query/Report/Conditional Formatting Question

[EllieFant]

I have finally tacked the Crosstab Query monster (well, made a crosstab query work anyway) and have created a report (from a number of crosstab queries combined into a single query - then generate report from that query). In this report I have a field called Auth and a field called Hired. We have so many job positions Authorized and I needed to know how many were filled. I did that fine.

I now have a report that shows how many of each job position are authorized and how many are filled. I have code set up in the On Format event of my report detail section to color the text red in the Auth field, if the Hired field is of less value. Works GREAT except when we have Authorized positions with no one in them.

By setting the format of the hired field to #;#;0;0 I have gotten zeros to show up...I just can't figure out how to turn the Auth text red since there doesn't appear to be anything in the hired field. I have tried

If me.hired = "" then
me.auth.forecolor = vbRed
else
me.auth.forecolor = vbBlack
End If

Doesn't work. Any suggestions?
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[hymn]

The code I use is the IsNull Sample below

If IsNull(BRCCode) Then
BRCCode.BackColor = 16777164
Else
BRCCode.BackColor = 13434828
End If

Hope this helps

 

[EllieFant]

Thanks, I will try this when I get back to work.

I really appreciate the help!
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[MichaelJ]

Try this in the format property of the field.

#;#;0;0[Red]

Mike

 

[Lightning]

To follow on from MichaelJ's post, read this Access On-Line Help Topic:

Format Property

This will allow what you want in Access 97. In Access 2000, check out the Conditional Formatting. This is available on the toolbar: Format|Conditional Format

HTH
Lightning

 

[EllieFant]

The following code doesn't make Auth be red if Hired is null.

If IsNull(Hired) Then
Auth.ForeColor = vbRed
Else
Auth.ForeColor = vbBlack
End If

This makes Hired red if it is Zero

#;#;0;0[Red]

BUT I need Auth to show the color change.

Any other suggestions?

Thanks in advance for the help.




Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[EllieFant]

I got it to work.

I used this code instead.

If Me.Auth > Me.Hired Or IsNull(Hired) Then
Me.Auth.ForeColor = vbRed
Me.Auth.FontBold = True
Else
Me.Auth.ForeColor = vbBlack
Me.Auth.FontBold = False
End If

Even leaving the #;#;0;0 (without the [red]) makes it all appear uniform.

Thanks for pointing me in the right direction.
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net