• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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

0
skillilea
Asked:
skillilea
  • 8
  • 4
  • 2
  • +2
3 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 8
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now