Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting properties for fields in queries (Access 2000)

    Does anyone know why when setting the properties of calculated fields within queries, I get the option to set the field type, e.g. to currency, but only sometimes (and seemingly at random) do I get the opportunity of setting the number of decimal points?

    As an example, take a query with a field that is of type currency with 2 decimal places, say WeeklyAmount. Converting it to say Monthly amount by *52/12, sometimes I can set the decimal places to 2, which I need, and sometimes I can't, and I get floating.

    I could add the Round function to the calculation, but I am intrigued as to why I sometimes get the decimal places option and sometimes not.

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

    Re: Setting properties for fields in queries (Access 2000)

    Is there a special reason you want to set the number of decimals in the query? You can always set it for the text box used to display the field on a form or report, and if you use the query for a mailmerge in Word (for example), you can set the number format in Word.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting properties for fields in queries (Access 2000)

    I just had this issue in my current project. I usually don't even bother with setting these things, but it helped when I was putting the reports together because I was doing a lot of averages. It had to do with how I was displaying the resultant data. Make sure you're not displaying it as text or that it's not coming from a text field (even if it's an integer value).

    I also had an issue with a crosstab that refused to let me display an average as a two digit decimal and kept doing 28 point precision (even if I was only displaying standard with 2 decimals in the report). I believe the reason was that when I did my make table query, it was creating a decimal field with 28 point precision. The answer was to change it to a long integer and do an append query instead of make table.

    Hope this helps,

  4. #4
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting properties for fields in queries (Access 2000)

    Yes Hans, there is. I run a database of residents at our nursing homes and maintain records of the various room charges,grants and payments. Within the database everything is in currency format from the underlying fields through to forms and reports and I have no problem with rounding errors (or at least Access takes care of rounding according to its internal formulae).

    I am now experimenting with the transfer of sales ledger data through to our accounts package which is a commercial package written in FoxPro. The transfer is effected by exporting the data to a dBase III file which can then be imported by the accounts package.

    So, the query creates a temporary Access table which is then passed to DoCmd.TransferDatabase. In the Access table the information appears to be rounded but in the dbf file I see 6 decimal places.

    My worry is that if I pass unrounded data into dBase III the accounts package has to convert it into currency format (real pounds and pence) and might use a slightly different internal rounding, leading to errors.

    If I use Round() can I be absolutely sure that the result is exactly the same as Access's internal rounding?

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

    Re: Setting properties for fields in queries (Access 2000)

    When you export a table or query, in most cases the underlying data are exported, not the way you display them, so you would need to either use the Round function, or the Format function. The latter will change the numbers to text. You'd have to experiment to see if you can change it back to numeric in DBase or FoxPro. You can certainly do that if you export to a text file.

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

    Re: Setting properties for fields in queries (Access 2000)

    One thing: the Round function in Access uses "banker's rounding". This rounds numbers ending in 5 to the nearest even digit: when you round 2.35 and 2.45 to 1 digit, both become 2.4. When you set the number format to Fixed, 1 digit, numbers ending in 5 are rounded up to the next digit: 2.35 becomes 2.4 and 2.45 becomes 2.5. The Excel worksheet function ROUND works this way too.
    Attached Images Attached Images
    • File Type: png x.png (1.8 KB, 0 views)

Posting Permissions

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