Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Unanswered: Regionalization -- Round II

    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?

    Regards

    John S

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    In the Crosstab, are you using SUM in the query?
    This would sum the values, not concatenate.

  3. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    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?

    John S.

    Aylmer, Quebec

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •