asked on

Error: Scaling of decimal value resulted in data truncation

I'm taking a Excel cell value and trying to save it to an Access field. I keep getting the error: Scaling of decimal value resulted in data truncation.

The Access field (PF) data type is Numeric, Field size = "decimal", Percision = 28, Scale 6

The value in the Excel cell ("pf") is: 0.748284042319986

I've been attempting to follow existing VBA that I got from another related application doing the same thing:
!pf = Right(Range("pf"), Len(Range("pf")) - 1)

Any suggestions? Thanks as always!!!

Rey Obrero (Capricorn1)

set the Decimal property to maximum (15)
and Scale to 15

Thirt

ASKER

I can't update the Access field. It's actually a odbc link to a Postgres SQL server.

Rey Obrero (Capricorn1)

wish you mentioned that in your original q

Thirt

ASKER

My bad. I should have clarified that I need to convert a decimal value to fit in a data field that I cannot change.

Rey Obrero (Capricorn1)

how are you getting the data from excel?

Thirt

ASKER

sure, see the snip below.

Made the assumption that my example would be enough info:

!pf = Right(Range("pf"), Len(Range("pf")) - 1)

Guess my question should have been, how do I strip off the extra decimal values that are not supported? Sorry for the confusion.

Set xl = CreateObject("excel.application")
        DoCmd.Hourglass True
        
        strFilename = _
          Dir("\\Server1\_projectcostreport\projectcostreport.xls")
        
        Set wkb = _
                  xl.Workbooks.Open("\\Server1\_projectcostreport\" & strFilename)
                
        '* replace this later with loop statement of all active project ids
        lngProjID = 2415
                
        '************************************************      
                'xl.Run "call costreport(" & lngProjID & ", " & frollup & ")"
                xl.Run "getprojectpf"     '<Need to pass this function lngProjID
        '************************************************
                strSQL = _
                  "SELECT * FROM historic_project_pf " & _
                  "WHERE week_ending=#" & xl.range("WeekEnding").Value & "# " & _
                  "AND project_id = " & lngProjID & ";"
                  
                Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
                With rs
                    If rs.EOF Then
                        .AddNew
                    Else
                        .Edit
                    End If
                    !week_ending = xl.range("WeekEnding").Value
                    !project_id = lngProjID
                    Dim lngPF As Long
                    
                    !pf = xl.range("pf").Value
                
                    .Update
                    .Close
                End With
                
                wkb.Close savechanges:=False
            
            strFilename = Dir()

Open in new window

Thirt

ASKER

Well, in helping me fine the better way to ask the question, I was able to search better and found that the round function is what I'm looking for.

!pf = Round(Range("pf"), 6)

That does the trick!

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Thirt

ASKER

sry for the late grade