0

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).

0

If MS Access is interpreting a numerical value as a string, you can coerce/cast the string value to a numerical data type using one of the following type conversion functions, the choice of which will obviously depend on the nature of your data:

  • CCur (to Currency)
  • CDbl (to Double)
  • CDec (to Decimal)
  • CInt (to Integer)
  • CLng (to Long)
  • CSng (to Single)

Since you look to be working with integers, I would suggest using either CInt or, if your values are likely to exceed 32767 (215-1), CLng.

So your code might become:

CInt(Nz([Due Dates Totals].Count,0))
CInt(Nz([Completed Dates Totals].Count,0))
Calendar.[Month Number]
Calendar.[Month Name]
  • 1
    That's perfect, thank you! – Itxi May 28 at 18:18
0

Found the solution, I multiplied the Nz() functions by 1 which forced them to be numbers and not strings.

e.g., instead of

Nz(Due Dates Totals.Count,0)

I used

1 * Nz(Due Dates Totals.Count,0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.