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.
4 8106
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? - SELECT tblTest.MyDouble, Round([MyDouble]*37.8976,3) AS RetVal
-
FROM tblTest;
- SELECT tblTest.MyDouble, FormatNumber([MyDouble]*37.8976,3) AS RetVal
-
FROM tblTest;
NeoPa 32,556
Expert Mod 16PB
Try : - 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.
Thank you! I'll give these a shot.
Sign in to post your reply or Sign up for a free account.
Similar topics
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
|
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....
|
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...
|
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.
|
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
|
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...
|
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. ...
|
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...
|
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...
|
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: 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: 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...
|
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: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |