Date stored as string

[warpped]

I have a date field in an Access database that is stored as text. I am using this field as a row in a crosstab report.

Because it is text, it does not sort properly. I tried extracting just the day from the date, and converting it to a number so it will sort properly.

The new number field appears as 1.00, 2.00, etc. The crosstab report still sees the new field as text instead of a number, so I cannot format it to drop the decimals. I can go into options and set the number field globally to drop the decimals, and this works.

The problem is when this is distributed as runtime, it still displays as 1.00, 2.00, etc. Is there a way to format this field in the crosstab report, or at runtime.

 

[MalcolmW]

You can set the number of decimal places with an optional argument for the ToText() function.
Instead of ToText(x), use ToText(x, 0)
See help for more info on other optional arguments for the ToText() function.