Conditional format

[chef_tim]

Conditional format NEW QUESTION ADDED.

I have just done a search, and read through 6 pages of info on this but didn't get just what I was looking for. Sorry to have to add to the list.

I would like to format a text control that shows a date 9years and 6monthes from the last time a check was completed. I have =([SECURITY INITIAL]+3467). What I would like is to have any dates over that to be in red. I have tried field value......Is greater then...............([SECURITY INITIAL]+3467) and also expression is............>([SECURITY INITIAL]+3467) and sever other variations of these. Thanks for any and all help, Tim

 

[chef_tim]

Hi Pat, by "SomeDate" do you mean like =Date() for example??? I think I see where it is going. I'll give it a try at work tomorrow and post back then. Thanks, Tim

 

[chef_tim]

Thanks Pat, that did it. The only weird thing was it wouldn't use "=" in the expression. Tim

 

[chef_tim]

Here's another question on the same subject..........

I would like to turn the date yellow the three months prior to the expiration date of a training task. some of the senarios I have tried are.........[M16DUE]>DATE()+270, [M16DUE]<DATE()+364 OR FIELD VALUE IS: BETWEEN: [M16DUE]-270 AND [M16DUE]-364 I must have lost my mind, I need to be working in 90 day increments. Oh well, I'm still having trouble. I did get over due training to show up as red text using Date()>([m16due])

Thanks in advance, Tim

 

[chef_tim]

Thanks Pat, but that turns them all yellow (except the ones that are already red). I think I have alot to learn, Tim

 

[chef_tim]

WooooHoooo I switched around MyDate and Date() and it works!!!!!!! Thank you very very much, Tim

 

[chef_tim]

This stuff is driving me nuts!!! More on conditional formating. Now I want "literal text to show up in a null field. What I have tried is, iif(Is Null, "OVERDUE") and several veriations. and I've tried Is Null ("OVERDUE") and several veriations of this one too. Any Ideas???? Thanks again, Tim

 

[chef_tim]

Okay, I'm working this one, but I still seem to be having trouble.Select IIf(IsNull(yourfieldname), "somevalue", yourfieldname) as NewField, First, is "Select" part of the expresion? "as NewField", what does that mean/do? Should "yourfieldname" be in brackets[]??? That's it for now, I'm sure I'll think of more later but I'll keep plugging away at it. Thanks, Tim

 

[chef_tim]

Hi Pat, Thanks for spelling it out, I'm afraid your getting too deep for me to follow. Your explanation did show me to use my queiry, But after that I tried it and I'm getting Syntax errors. So I've played with it to no avail.

I just went to the last colunm in my queiry, I think I'll try it in the actual colunm for the field that it is taking the data from. Thanks again, Tim

 

[john471]

Tim,

To translate what Pat is saying into the GUI design grid (formerly known as the QBE grid); you want something like

YourAliasedFieldName: IIf(IsNull([YourFieldName]),"somevalue",[YourFieldName])

in the "Field" row of the column you are working on. The part to the left of the colon relates to what comes after the "AS" statement in SQL :- it gives that column another name (i.e. an alias).

If you change the view of the query you are building to SQL mode, you will see it represented as Pat has said.

Hope this helps.

Regards.

John.

 

[chef_tim]

All I can say is WOW!! I had to play around with it but it worked like a charm. I even went back and put a conditional format on it to turn "OVERDUE" red. Thanks to both of you for the help I really appriciate it. Tim