I have an Access database for a work project, unfortunately I can't share too many details due to the nature of the work. But basically I have a tables of actions which need to be carried out, each action has a due date and I have a table which counts how many actions are due and have been completed in each month, using the below logic:
Group by Month([Due Date])
Count [Due Date]
For one query, and the same for the completed dates in another query. I then have a final query which uses the due dates query, a completed dates query, and a months of the year table (otherwise empty months don't show). So I have the following:
Nz(Due Dates Totals.Count,0)
Nz(Completed Dates Totals.Count,0)
Calendar.Month Number
Calendar.Month Name
(The Nz
's above is so that I don't have empty spaces in my report)
My report then uses the month number to sort and group, the month name for displaying the month to users, and a small table with the due actions and the total actions. I then have conditional formatting on the table so that if the completed actions are less than the due actions it's highlighted red, if they equal it's green, and more completed actions than due means that the 'completed' field is blue.
This works fine unless one of the numbers is a double digit, in which case it only compares the first digit of each value, e.g. 19 < 2
I tried comparing the value in the field to the due count from the query itself, and comparing it to the value in the field on the report and they both have the same issue. My guess is that it's comparing them as strings and not numbers but I don't know how to force the query to store the count as a number.
Thank you for any help. (And by the way, there are more tables and the like in the database, Excel isn't the right tool for this work).