I have a report with 2 fields - [numTotalItemsPurchased] and [curTotalCost]. When I try to calculate the average cost as [curTotalCost]/[numTotalItemsPurchased], Access keeps rounding the value.
I am not doing this in VBA, I'm setting this as the control source for this calculated control. The control is set to Standard Format with 2 Decimals.
So to illustrate my dilemma, I purchased 5,500 widgets for $62,500.00. The value Access returns is $11.00. I cannot get it to correctly display $11.36.
Any help is appreciated.
Kind Regards.
Gunner
If you look at the first line of code in post #4 you'll see that the division symbol used is "\" rather than "/".
\ ==> Integer divide. The result is always integral (whole numbers).
/ ==> Divide. Results vary, but this is what people generally understand by division.
9 1551
Gunner,
Are you saying the math is done in a query? Or is some textbox bound to a formula like =a/b where a and b come from the recordsource? Are these two field names, [curTotalCost] and [numTotalItemsPurchased],the names of textboxes?
What is the definition of the relevant fields in the table where this data comes from? I mean if [curTotalCost] and [numTotalItemsPurchased] are text boxes bound to some fields from a table, how are those fields defined in the table?
Jim
Jim -
The report is fairly complex (this is the coup de grace of their system).
The report itself is bound to a query which links several tables.
The field [curTotalCost] is a calculated Currency value from a separate query (i.e. in the Query, this field = [curCosts]+[curFees]. I am using a DSum to pull this value in the report. The DSum function is included as the direct Control Source for this control. This value shows decimals just fine.
The field [numTotalItemsPurchased] is similar to the previous value except it is not calculated in the query. The Control source of the control is a DSum from the same query as previously noted (separate from the report's control source). Because this only uses whole numbers, there are no decimals.
Finally, the field I'm having trouble with ([curAvgPurPrice]) is an unbound control (no underlying table) with the math included directly in the control source.
The math works perfectly except that it keeps rounding when I would I like to include decimals. On the format tab (for that control on the report), the Format is set to Currency with 2 decimals. I also tried to add a brand new control (thought maybe I was missing something) - the new control acts exactly the same.
Thanks again for your help, Jim.
Gunner
Jim -
Also - if I hard code these numbers to the field, it displays properly. So, if I change the control source from: - =IIf([fxSumofPurchases]>0,[fxCostofPurchases]\[fxSumofPurchases],"n/a")
to
the field displays 11.36 correctly.
Not sure if that helps to eliminate a bunch of potential causes.
Thanks again for any assistance!
Gunner
Jim -
I've solved the problem although not with the best of options.
I have created 2 new controls [text182] and [text183]. - [text182].ControlSource = [numTotalItemsPurchased]
-
[text183].ControlSource = [curTotalCost]
-
[curAvgPurPrice].ControlSource = [text183]/[text182]
As baffled as I am, it works perfectly!
I would still appreciate any insight into the problem if this makes any sense to anyone.
Thanks again for your assistance!
Gunner
I was going to suggest exactly what you did. And also I thought you could try explicitly rounding your original formula, that is - IIf([fxSumofPurchases]>0,Round([fxCostofPurchases]\[fxSumofPurchases],2),"n/a")
or maybe use CDbl instead of Round.
Not that I can say why any of those should work and the original does not. I think the answer lies somewhere in the queries behind all this. Sometimes you just go with what works :) Congratulations on solving it.
Jim
NeoPa 32,556
Expert Mod 16PB
If you look at the first line of code in post #4 you'll see that the division symbol used is "\" rather than "/".
\ ==> Integer divide. The result is always integral (whole numbers).
/ ==> Divide. Results vary, but this is what people generally understand by division.
NeoPa,
That was the cause - wow. Thanks for that great tip!
Thanks to all for the assistance!
Old Eagle Eye Neopa :)
I didn't even know that operator was available. Always good to learn something.
Jim
NeoPa 32,556
Expert Mod 16PB
I wish the old bit were as inaccurate as the eagle eye bit :-D
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Georges Heinesch |
last post by:
Hi.
I cannot change the value of an unbound control (txtSR and txtSS) within
a sub call. I explain ... my sub (SRSS_set) takes the date (type date)
as input, and returns 2 values (type time).
...
|
by: Bob Bykerk |
last post by:
Hyperlinks work fine when using bound controls but when I try to get one to
work on an unbound control (with hyperlink? = true) I can't insert a
hyperlink or use the data as a hyperlink. I have...
|
by: Trevor Hughes |
last post by:
I am trying to resolve a problem I'm experiencing in Access 2000.
I have an unbound control which is set be code on the open event of a
form. However when I try to subsequently run some code...
|
by: Jim M |
last post by:
To prevent data corruption I have replaced a memo field on my form with
an unbound control on my form that I populate with a function that
fills it from a memo field from my table. When the form is...
|
by: Barbara Schmidt |
last post by:
Hello,
if I get the data from db with sqldatasource-control, how to show this data
to an unbound control, so for instance:
label1.text=sqldatasourceWHAT-TO-DO-NOW
Thanks
Barbara
|
by: scott_baird |
last post by:
Probably something simple, but it's driving me up the wall...
In a report, I have an unbound control that is the total of 2 other
bound controls (currency) as follows:
unbound control =cash1 +...
|
by: Marco |
last post by:
Hello,
I have :
float f = 36.09999999;
When I do :
char cf;
sprintf(cf,"%0.03lf", f);
I get : 36.100
|
by: JAMBAI |
last post by:
I am using a OLE unbound Control to Embed the word document in the form load, event like this
http://support.microsoft.com/kb/209990
and When i double clicks (In Place activation), the documents...
|
by: John McAtee |
last post by:
I am using Access 2007. Created a contribution database for a non-profit. Created parameter query that displays contributions for a specified time period using the "Between And " parameter. The...
|
by: beemomo |
last post by:
This is my second post for the same question, hope this time I did explain more clearly about my problem.
I need a report that generate the total value of projects from a few foreign currency to...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |