Conditional Formatting Not Working Properly

[davidmcleod]

I have set up 3 conditions on a specific field and when I run the report the formatting is not correct. I am comparing 3 different budget fields, two of which are calculated using a query.

It seems like my conditions are all pretty basic:
Current Budget > Budget 20% - RED
Current Budget < = Budget 10% - Green
Current Budget between Budget 20% and Budget 10% - Yellow

For example:
1. Current Budget is 414,034 and Budget 10% is 14,545,300 and It was conditioned RED
2. Current Budget is 4,831,659 and Budget 20% is 4,637,590 and it is conditioned GREEN

Happy to attached the file to help troubleshoot. I've been looking everywhere and have tried multiple things with no luck.

[pbaldy]

The file might help. Make sure the conditions have brackets around the field names. Also, make sure the values are numeric, not text.

[davidmcleod]

I have attached the file

[pbaldy]

Also, make sure the values are numeric, not text.

All of your fields are text, so you're getting an alphabetic comparison instead of a numeric comparison. I'd change fields that are designed for numeric values to a numeric data type, like Currency. You can cheat it in Conditional Formatting by converting to numbers:

CCur([current budget])<CCur([original budget])

but frankly that's a workaround for a design mistake IMO.

[pbaldy]

Oh, you could also use CCur() or another conversion function in the query and the Conditional Formatting would probably work, but it's still a workaround. I'd change the data types in the table.

[davidmcleod]

Thanks for your input. How do I change the format for those fields? When I select the dropdown it is blank.

[pbaldy]

Sorry, I'd also change the date fields from text to Date/Time. You'll have the same problem with dates, 6/25/15 will come after any date in October-December, since 6 comes after 1 alphabetically.

[pbaldy]

Thanks for your input. How do I change the format for those fields? When I select the dropdown it is blank.

Not the format, the data type, next to the field name in the top section. With the table in design view, you should be able to change the Data Type from Text to Currency or Date/Time as appropriate.

[davidmcleod]

You were great help! Thank you for saving my sanity!

[pbaldy]

Happy to help, and welcome to the site by the way!

[davidmcleod]

One more question actually... Some items have an A associated with it in the date field. That represents Actual. By updating the data type to Date/Time, those records are deleted when I import. Is there a workaround for that?

[pbaldy]

No, and I wouldn't store it that way. In essence you're trying to store 2 pieces of info in one field.

[June7]

First create 4 new fields. Run UPDATE sql action to populate fields with extracted date. Because not every value has the "A", suggest 4 UPDATE actions.

UPDATE Status SET newStartfield = CDate(Left([Start], InStr([Start]," A")-1)), newStartTypefield ="A" WHERE [Start] LIKE "*A";
UPDATE Status SET newFinishfield = CDate(Left([Finish], InStr([Finish]," A")-1)), newFinishTypefield="A" WHERE [Finish] LIKE "*A";
UPDATE Status SET newStartfield = CDate([Start]) WHERE NOT [Start] LIKE "*A";
UPDATE Status SET newFinishfield = CDate([Finish]) WHERE NOT [Finish] LIKE "*A" AND NOT [Finish] Is Null;

If you don't care about the "A" any more then don't create fields for it and don't include in the SET clause but still need 4 actions.

Delete old fields. Rename new date fields with the old names.

Or write a VBA function to call in the Update To row of a one UPDATE sql.

Wait, one action might work after all.

UPATE Status SET [Start]=CDate(Replace([Start]," A","")), [Finish]=CDate(Replace([Finish]," A",""));

On second thought, that will error because of Null fields in Finish, so two actions.
UPATE Status SET [Start]=CDate(Replace([Start]," A",""));
UPDATE Status SET [Finish]=CDate(Replace([Finish]," A","")) WHERE Not Finish Is Null;

Do you care about the "A"?

[davidmcleod]

Thank you.

[June7]

I did some edits on my previous thread.

If you don't want to modify table and data, the data extracts and date conversions can be done in query when needed but will be tricky because of same issues encountered in the UPDATE actions. And as Paul says, still poor design and annoying 'workaround'.

Advise no spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be Budget20Pct or Budget_20_Pct.