473,320 Members | 1,814 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,320 software developers and data experts.

Calculated Field/ Decimal Precision Help.... MS Access 2k

I am having an issue where I need to multiply a double value by a value with four decimal places within a calc field. However, it will only allow me to multiply by a whole number. "Invalid precision for a decimal" Suggestions?

If I reference a column in a table with the same number (37.8976) this works fine.

Example: Value: Avg(IIf([CAP OR FFS]="CAP" And ([MRTYPE] Like "*a*") And [HOSP_OR_OFFICE]="H",(([SumOfMRTYPE4_FAC]+[SumOfMRTYPE5_FAC])*37.8976),(IIf([CAP OR FFS]="CAP" And ([MRTYPE] Like "*a*") And [HOSP_OR_OFFICE] Not Like "H",(([SumOfMRTYPE4]+[SumOfMRTYPE5])*37.8976),[SumOfAmt_REC]))))

When controlling the properties of the field in the query I can not specify decimal places, only General Number and Fixed data types with no precision control.
Oct 23 '08 #1
4 8106
ADezii
8,834 Expert 8TB
I am having an issue where I need to multiply a double value by a value with four decimal places within a calc field. However, it will only allow me to multiply by a whole number. "Invalid precision for a decimal" Suggestions?

If I reference a column in a table with the same number (37.8976) this works fine.

Example: Value: Avg(IIf([CAP OR FFS]="CAP" And ([MRTYPE] Like "*a*") And [HOSP_OR_OFFICE]="H",(([SumOfMRTYPE4_FAC]+[SumOfMRTYPE5_FAC])*37.8976),(IIf([CAP OR FFS]="CAP" And ([MRTYPE] Like "*a*") And [HOSP_OR_OFFICE] Not Like "H",(([SumOfMRTYPE4]+[SumOfMRTYPE5])*37.8976),[SumOfAmt_REC]))))

When controlling the properties of the field in the query I can not specify decimal places, only General Number and Fixed data types with no precision control.
You can use the Round() or FormatNumber() Functions to produce the desired number of Decimal Places. The following SQL Statements will multiply a Double Field ([MyDouble]) by 37.8976, and Round the results to 3 Decimal Places in a Calculated Field named [RetVal]. The Test Table used for this Demo is named tblTest, pretty ingenious, hey?
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTest.MyDouble, Round([MyDouble]*37.8976,3) AS RetVal
  2. FROM tblTest;
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTest.MyDouble, FormatNumber([MyDouble]*37.8976,3) AS RetVal
  2. FROM tblTest;
Oct 23 '08 #2
mshmyob
904 Expert 512MB
As always.

cheers,
Oct 23 '08 #3
NeoPa
32,556 Expert Mod 16PB
Try :
Expand|Select|Wrap|Line Numbers
  1. Value: Avg(IIf([CAP OR FFS]='CAP' And ([MRTYPE] Like '*a*'),IIf([HOSP_OR_OFFICE]='H',CDbl([SumOfMRTYPE4_FAC])+CDbl([SumOfMRTYPE5_FAC]),CDbl([SumOfMRTYPE4])+CDbl([SumOfMRTYPE5]))*37.8976,[SumOfAmt_REC]))
I added in CDbl() to convert the non double values where necessary. This is because the defaults are to convert to integral types when unspecified.
Oct 23 '08 #4
Thank you! I'll give these a shot.
Oct 23 '08 #5

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

Similar topics

21
by: Batista, Facundo | last post by:
Here I send it. Suggestions and all kinds of recomendations are more than welcomed. If it all goes ok, it'll be a PEP when I finish writing/modifying the code. Thank you. .. Facundo
17
by: John Bentley | last post by:
John Bentley: INTRO The phrase "decimal number" within a programming context is ambiguous. It could refer to the decimal datatype or the related but separate concept of a generic decimal number....
6
by: Peter Blatt | last post by:
Does 5 represent the total numer of digits (including the fractional portion) or only the number of places BEFORE the decimal point? Moreover does the number include the decimal point? Are there...
5
by: John Bahran | last post by:
I am trying to use calculated fields in my query but all the results are zero ven when they're not. Please help. Thanks.
4
by: hi | last post by:
I'm having major problem with multiplications in querys. E.g. Table 1 f1 Single f2 Single f1=1.12 f2=.2345 I create a query with just one field a:f1*f2 and I get
687
by: cody | last post by:
no this is no trollposting and please don't get it wrong but iam very curious why people still use C instead of other languages especially C++. i heard people say C++ is slower than C but i can't...
3
by: Amaryllis | last post by:
Hi again, I'm new to the world of communicating between VB.NET and AS/400, so I've been posting a lot of questions lately since no one else in the company has done anything like this before. ...
2
by: Unforgiven | last post by:
Hello All, I have the following query... SELECT Demographics.Full_Name, Demographics.Year_of_birth, Status.Status_OK, SUM(2004 - Demographics.Year_of_birth) AS Age FROM Demographics INNER...
11
by: Pieter | last post by:
Hi, I'm having some troubles with my numeric-types in my VB.NET 2005 application, together with a SQL Server 2000. - I first used Single in my application, and Decimal in my database. But a...
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
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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.