Results 1 to 6 of 6
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Custom function in queries (access 2003)

    I have a custom function that works out the average Time Fraction of a staff member for a given year, by averaging their time fraction for semester 1 and for semester 2. These are themselves calculated using another function.

    If I call in it the immediate window it behaves itself:

    ? fnGetAverageTimeFraction(89,2005)
    0.5
    ? fnGetAverageTimeFraction(23,2005)
    0.83

    However if I put the same function in a query it returns values like : 0.829999983310699


    If I add the round function to the function it does not make any difference, if I add the round function to the query it also makes no difference.

    I can fix it in the query using any of these methods:
    timefraction3: Format(fnGetAverageTimeFraction([staffmemberID],2005),"0.00") which gets changed to:
    timefraction3: Format(fnGetAverageTimeFraction([staffmemberID],2005),"Fixed")

    or
    timefraction4: Val(Format(fnGetAverageTimeFraction([staffmemberID],2005),"Fixed"))

    or timefraction5: fnGetAverageTimeFraction([staffmemberID],2005) with field format set to fixed, 2 dec places.

    The problem is that I want to export the results to Excel, using DoCmd.Transfer Spreadsheet.

    The format function yields nicely formatted two dec place time fractions , but then the cells in Excel (using Excel 2003) all carry the warning that they are numbers stored as text.

    If I use a combination of Val, Format, and format the column as fixed with 2 dec places it seems to work. - No it works if I use office links but not using docmd.TransferSpreadsheet - just tested again.

    ( I have also found that exporting the query with Tools...Office Links, gives a slight different result from using docmd.TransferSpreadsheet.)

    Is there a nice simple solution to this.

    I attach a cut down demo.
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom function in queries (access 2003)

    If you change the return type of both functions from Single to Double, the rounding errors will be gone. Don't forget to change the declaration of snTimeFraction:

    Dim snTimeFraction As Double

    (You may want to change the prefix sn too, but that's not essential)

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Custom function in queries (access 2003)

    Thanks Hans

    I have been testing this to see what difference it made. It seems that it allows me to use the Round function, in either the function or the query.

    Without using Round, the errors persist, but now I can use Round to fix them.

    Is there any way to have the numbers appear in Excel with 2 dec places?

    Formatting the column in the query as Fixed, 2 dec places improves the apperance in Access, but does not affect the export to Excel.
    Regards
    John



  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom function in queries (access 2003)

    You can use DoCmd.OutputTo, this will preserve the formatting from the query. See the attached zip file, it contains the database and the Excel output.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Custom function in queries (access 2003)

    Thanks Hans

    That seems to work. Using Output to, it does not seem t matter whether my functions use singles or doubles, because the formatting of the query solves the problem.
    Regards
    John



  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Custom function in queries (access 2003)

    Hans

    I have now put this into my application and it works well.

    OutputTo has another advantage, I find, it uses the captions from the query as the column headings.
    Regards
    John



Posting Permissions

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