Setting properties for fields in queries (Access 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote