At a food bank in Gatineau Quebec, I am putting together a program to track volunteer hours. The hours are in a doubles field, with hours and portions of hours (e.g. 3.5 hours). I take this data, and report it as a crosstab (by month and year), with columns being the month. For each year, I want to total the months.
When totallying, I explicidly turn each field total into a numeric value using "val()". If one does't do this, Access treats each field in the report as a text field, and concatenates the fields in the total (ie. 12 + 2 becomes 122). All good so far; using " = val(t1]) + val([t2]) +....... " works fine with values and fractional values on an Windows American or English formatted system. On a French system, however, val() appears to strip out all fractional values. so that 38.5 becomes 38. The French system replaces a period with a comma (e.g. 38.1 becomes 38,1). This behaviour causes totals to be understated (and wrong)
When I return to the foodbank, I will try to use the CDBL function instead of val() on their French-formatted machines to see if this helps.CDBL works in place of val() on English-formatted systems. Anyone have any experience with this problem?
The cross tab sums hours by year and by month. I cannot actually sum up the months in a cross tab (the report has to do this), but I suppose I could create a second query of the cross tab that sums up the months. Getting messy again (sigh).
FYI, I did try the CDBL function in place of the VAL function, but the problem remained on the French system computers. In the end, I opted to round everything to 0 decimal places, and the problem basically went away. Lets face it, what is the difference between 3,508 hours and 3,508.3 hours?