Strange behavior when doing math before copying information into Excel file

[mcomp72]

I have run across some strange behavior that I cannot explain, and am hoping someone here could shed some light on it for me.



I have created a database for outputting information onto some Excel worksheets. Before outputting some of the information, I have Access do some simple math. The result I'm getting on one of my calculations is a number that has been rounded when it shouldn't be. I can't figure out why it's happening.

Here's a brief explanation. First off, I have a field called "Rate" in my query, and it is set to Currency, with 4 decimal places.

I output "Rate" on my Excel sheet into a specific cell with this line of code. The value of "Rate" in the current record is $39.1482.

.Range("G39").Value = rstQ_NoticeP1.Fields("Rate")

It works perfectly fine. It shows 39.1482 in the Excel cell.

Then my next line of code multiplies the rate by 1.5 and outputs that to a different cell.

.Range("N39").Value = rstQ_NoticeP1.Fields("Rate") * 1.5

This also works perfectly fine. It outputs 58.7223 in the Excel cell.

The next line is where the problem is.

.Range("R39").Value = rstQ_NoticeP1.Fields("Rate") * 2

As you can see, in this case I want the Rate amount to be doubled, and that number to show up in the cell "R39". What should be showing is 78.2964. However, what shows up instead is 78.3000. (The actual value in the cell is 78.3 but it is adding the zeros because the cell formatting is set to 4 decimal places.)

I wondered if Access was somehow making a math error, so in my code right after these lines, I added a new line.

MsgBox (rstQ_NoticeP1.Fields("Rate") * 2)

The message box that pops up when I run the code says 78.2964.

I have confirmed that all three cells in Excel are formatted the same way: as Currency, decimal place set to 4, and the Symbol set to $. The only difference I can see is that the first two cells are Merged Cells, and the last one (the one that is not working properly) is not a Merged Cell. Could that somehow be causing the issue?

Otherwise, I cannot for the life of me understand why this is happening, especially when the numbers being output to the other two cells are not being rounded. Anyone have any ideas?

[Micron]

I don't know of any reason why a non-merged cell would do that if it's formatted the same way as the ones that are merged but work. Provided that there's no code in the workbook that could be causing this, I'd try writing the value to a cell in some other column that is formatted the same way. If it's OK, chances are the worksheet or book is corrupted. If it works, I'd try moving all data right of this point one column over and deleting the faulty one. You would need to keep any formulae in this column safe from deletion.

[mcomp72]

Thanks for that suggestion. I was about to try it, but decided to try something else first, on the off-chance it would work. I created a variable of type Double called DoubleTime. Then, in regards to the problem area, I changed my code to this.

DoubleTime = rstQ_NoticeP1.Fields("Rate") * 2
.Range("R39").Value = DoubleTime

It fixed the problem! I have no idea why this worked but the original way didn't -- it makes no sense to me. But, it did work, so I'll go with this and hope that I don't run into anything like this again. Very strange!

[Micron]

Thanks for posting your solution. Yes, very bizarre that you needed to pass the calculation to a variable of Double data type when other calculations stored in other cells don't require it. If more than one field was involved, I would have suggested you go back to the table, possibly finding one field was Currency and another was some other type. You said the query field was "set to Currency". Did you mean that you were coercing the field to Currency, as in Cur([Rate])?
Also wonder what would have happened had you multiplied by 2.0...

BTW, Rate is a reserved word
allenbrowne.com/AppIssueBadWord.html