473,324 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Unwanted Rounding in unbound control on Report

110 100+
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
Nov 8 '13 #1

✓ answered by NeoPa

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
jimatqsi
1,271 Expert 1GB
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
Nov 8 '13 #2
dgunner71
110 100+
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
Nov 8 '13 #3
dgunner71
110 100+
Jim -

Also - if I hard code these numbers to the field, it displays properly. So, if I change the control source from:

Expand|Select|Wrap|Line Numbers
  1. =IIf([fxSumofPurchases]>0,[fxCostofPurchases]\[fxSumofPurchases],"n/a")
to

Expand|Select|Wrap|Line Numbers
  1. =62500/5500
the field displays 11.36 correctly.

Not sure if that helps to eliminate a bunch of potential causes.

Thanks again for any assistance!

Gunner
Nov 8 '13 #4
dgunner71
110 100+
Jim -

I've solved the problem although not with the best of options.

I have created 2 new controls [text182] and [text183].

Expand|Select|Wrap|Line Numbers
  1. [text182].ControlSource = [numTotalItemsPurchased]
  2. [text183].ControlSource = [curTotalCost]
  3. [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
Nov 8 '13 #5
jimatqsi
1,271 Expert 1GB
I was going to suggest exactly what you did. And also I thought you could try explicitly rounding your original formula, that is
Expand|Select|Wrap|Line Numbers
  1. 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
Nov 9 '13 #6
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.
Nov 9 '13 #7
dgunner71
110 100+
NeoPa,

That was the cause - wow. Thanks for that great tip!

Thanks to all for the assistance!
Nov 9 '13 #8
jimatqsi
1,271 Expert 1GB
Old Eagle Eye Neopa :)

I didn't even know that operator was available. Always good to learn something.

Jim
Nov 9 '13 #9
NeoPa
32,556 Expert Mod 16PB
I wish the old bit were as inaccurate as the eagle eye bit :-D
Nov 9 '13 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
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). ...
0
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...
3
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...
1
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...
0
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
3
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 +...
29
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
0
JAMBAI
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...
5
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...
4
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
1
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)...
1
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...
1
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....
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.