Access: Double, Need to hold the decimal

Access 2010

I have a textbox entry and I am losing the decimal.  Not sure why?


The textbox has properties of:  NO Format, 0 decimal

tnx experts

Code below...
Private theValue As String

Private Sub Form_Load()
    For Each ctl In Me
        If ctl.Tag = "duration" Then
            Me(ctl.Name).AfterUpdate = "=handle_AfterUpdate('" & ctl.Name & "')"
            Me(ctl.Name).BeforeUpdate = "=handle_BeforeUpdate('" & ctl.Name & "')"
        End If
    Next
End Sub



Private Function handle_BeforeUpdate(ctlName As String)
    theDay = Right(ctlName, 1)
    theValue = Me(ctlName).Value
End Function

Open in new window

skillileaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

skillileaAuthor Commented:
Ooops...tried making the variable a double as well..
0
mbizupCommented:
For your textbox format, use "Fixed", and 2 (or whatever precision you need)
0
skillileaAuthor Commented:
I changed the format to Fixed and 2  and it still rounds it.
Here is what I have:


Private theValue As Double

Private Function handle_BeforeUpdate(ctlName As String)
    theValue = CDec(Round(Me(ctlName).Value + 0.000001, 2))
End Function

Open in new window

0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

skillileaAuthor Commented:
The data is linked to SQL as a float...is that the problem?

CREATE TABLE [dbo].[tblData](
      [dataID] [int] IDENTITY(1,1) NOT NULL,
      [metricID] [int] NULL,
      [metricDay] [int] NULL,
      [metricValue] [float] NULL,....this field
0
Rey Obrero (Capricorn1)Commented:
change your function to pass back the result


Private theValue As Double

Private Function handle_BeforeUpdate(ctlName As String) as double
    theValue = CDec(Round(Me(ctlName).Value + 0.000001, 2))
    handle_BeforeUpdate=theValue
End Function
0
peter57rCommented:
I don't really understand how you could have typed the question without realising what the answer is.


"The textbox has properties of:  NO Format, 0 decimal"

0
skillileaAuthor Commented:
Sorry if my post is confusing.

I have attached the rest of the code.

Here is what I am doing.

Data comes back from SQL server as this:

MetricID      MetricName      Day1      Day2      Day3      Day4      Day5
1      Metric1      55.00      6.00      53.00      NULL      NULL
2      Metric2      354.00      NULL      NULL      NULL      NULL
3      Metric3      NULL      NULL      3535.00      NULL      NULL
4      Metric4      NULL      NULL      NULL      NULL      NULL
5      Metric5      NULL      78.00      NULL      NULL      NULL

The below code inserts a record without errors if it needs to but...I can't seem to get the decimal to hold.
Private sSQL As String
Private ctl As Control
Private theDay As Integer
Private theValue As Double


'-----------------------------------------------FORM LOAD
Private Sub Form_Load()
    For Each ctl In Me
        If ctl.Tag = "duration" Then
            Me(ctl.Name).AfterUpdate = "=handle_AfterUpdate('" & ctl.Name & "')"
            Me(ctl.Name).BeforeUpdate = "=handle_BeforeUpdate('" & ctl.Name & "')"
        End If
    Next
End Sub

Private Function handle_AfterUpdate(ctlName As String)
    On Error GoTo ERR_INSERT
INSERT:
    DoCmd.RefreshRecord
    DoCmd.SetWarnings True
    Exit Function

ERR_INSERT:
    DoCmd.SetWarnings False
    Call rec_Insert(theDay, theValue)
    Resume INSERT
End Function

Private Function handle_BeforeUpdate(ctlName As String) As Double
    theDay = Right(ctlName, 1)
    Debug.Print Me(ctlName).Value
    theValue = CDec(Round(Me(ctlName).Value + 0.000001, 2))
    handle_BeforeUpdate = theValue
End Function

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
        Case 7787, 7878
            Response = acDataErrContinue
        Case Else
            Response = acDataErrDisplay
    End Select
End Sub

Private Sub rec_Insert(theControl As Integer, theValue As Double)
    sSQL = _
        "INSERT INTO  dbo_tbldata  " & _
        " (metricID, metricDay, metricValue) VALUES( " & _
        [MetricID] & "," & _
        theControl & "," & _
        theValue & _
        ")"
        CurrentDb.Execute sSQL, dbSeeChanges
End Sub

Open in new window

0
mbizupCommented:
Try this - it looks like you were rounding AFTER adding in the decimal:


Private Function handle_BeforeUpdate(ctlName As String)
    theValue = CDec(Round(Me(ctlName).Value) + 0.000001, 2)
End Function 

Open in new window


Also, what precision do you need?

Try setting it to 6 (or higher) instead of 2
0
mbizupCommented:
Correction:

Private Function handle_BeforeUpdate(ctlName As String)
    theValue = CDec(Round(Me(ctlName).Value, 2) + 0.000001)
End Function
0
skillileaAuthor Commented:
I enter:  44.12 in the textbox

It somehow gets rounded prior even the CDEC(....).

Here is are the results from this

Private Function handle_BeforeUpdate(ctlName As String)
    theDay = Right(ctlName, 1)
    Debug.Print Me(ctlName).Value
    theValue = CDec(Round(Me(ctlName).Value, 2) + 0.000001)
    Debug.Print theValue
End Function

Intermediate:
 44
 44.000001

0
skillileaAuthor Commented:
the textbox is fixed(2)
0
mbizupCommented:
Okay - what are you expecting to see?

Did you try setting the precision to a higher precision like:

Fixed (6)   '<--- or higher than 6 if needed?
0
skillileaAuthor Commented:
I think I just figured it out.

The form is linked to a view in SQL and somehow Access is not allowing the format to be a decimal unless I explicitly CAST the SQL return.

SELECT
      m.metricID as MetricID
      ,m.metricName as MetricName
      ,cast(d1.metricValue as decimal(8,2)) as Day1
      ,cast(d2.metricValue as decimal(8,2)) as Day2
      ,cast(d3.metricValue as decimal(8,2)) as Day3
      ,cast(d4.metricValue as decimal(8,2)) as Day4
      ,cast(d5.metricValue as decimal(8,2)) as Day5

This now gives me a decimal to work with.

Private Function handle_BeforeUpdate(ctlName As String) As Double
    'theDay = Right(ctlName, 1)
    Debug.Print Me(ctlName).Value
    Exit Function
End Function

 34.44

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
This:

 theValue = CDec(Round(Me(ctlName).Value, 2) + 0.000001)

makes no sense. What are you trying to do?

/gustav
0
skillileaAuthor Commented:
Thanks Experts...looks like the data needs to be formatted right first so Access can process it.

thanks for all the insight!
0
Gustav BrockCIOCommented:
> ...looks like the data needs to be formatted right first so Access can process it.

Well, perhaps, but that is not the case; something else is going on. Attaching SQL Server tables is a non-issue and works perfectly for everyone else, so somehow you are off track.

Further, adding 0.000001 to a value you have rounded to two decimals and then store that value for later to read it back, again rounded to two decimals, makes no sense.

You need to reconsider what you doing.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.