?
Solved

Form number field decimals based on a second field

Posted on 2014-07-29
7
Medium Priority
?
253 Views
Last Modified: 2014-07-30
An expert helped me a while back with this topic but for some reason it is not working as I thought it was.

I have two fields on a form.  The first one is named "txtC_SIG_FIG" and is a number field and the data entered into this field is always a whole number with no decimals.  The 2nd one is named "txtC_MIN".  When a number is entered into the 2nd field I want it to carry out to the number of decimals entered in the 1st field.

So, for example, if txtC_SIG_FIG is 3, and if I enter 1.23412 in the 2nd field, I want the result in the 2nd field to display as 1.234

But if the value in txtC_SIG_FIG is 4, and if I enter 1.23412 in the 2nd field, I want the result in the 2nd field to display as 1.2341

Note:  The properties of the 2nd field in the table and on the form is Standard, auto decimals.

The current code which is not working is:

Me.txtC_MIN = Format([txtC_MIN], "0." & Right(10 ^ [txtC_SIG_FIG], Len(10 ^ [txtC_SIG_FIG]) - 1))
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 40227424
Here's a function I found some place.  I didn't write it and I'm sorry I don't remember who did.

Function RoundToNearest(dblNumber As Double, varRoundAmount As Double, _
   Optional varUp As Variant) As Double
   
'''The following example creates a user-defined function called RoundToNearest, which accepts three parameters:
'''
'''dblNumber        The numeric value you want to round
'''varRoundAmount   The increment to which dblNumber will be rounded
'''varUp            An optional argument: include to round up; omit to round down
''' For example:
''''•RoundToNearest(3.33, 0.1, up) returns the value 3.4.'
''''•RoundToNearest(3.33, 0.1) returns the value 3.3.
''''.RoundToNearest(3.45678, 0.001) returns the value 3.456.
''''•RoundToNearest(3.33, 1, up) returns the value 4.'
''''•RoundToNearest(3.33, 1) returns the value 3.
         
   Dim dblTemp As Double
   Dim intTemp As Integer
   
   dblTemp = dblNumber / varRoundAmount
   intTemp = Int(dblTemp)
   
   If intTemp = dblTemp Then
      RoundToNearest = dblNumber
   Else
      If IsMissing(varUp) Then
         ' round down
         dblTemp = intTemp
      Else
         ' round up
         dblTemp = intTemp + 1
      End If
      RoundToNearest = dblTemp * varRoundAmount
   End If
End Function

Open in new window


print roundtonearest(3.45678, .001)
 3.456
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40227476
This treats it like text, searching for the "." and moving the appropriate number of places past that:
=LEFT(TEXT(txtC_MIN,"#.############"), SEARCH(".",txtC_MIN)+txtC_SIG_FIG)

Open in new window

0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40228642
This truncates the decimals while performing a 4/5 rounding:

Me.txtC_MIN  = Format([txtC_MIN], "0." & String([txtC_SIG_FIG], "0"))

/gustav
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:SteveL13
ID: 40229112
I think we're getting close.  If I enter 1.12345 and the decimal qualifier is 3 I get 1.123  -  Perfect.

But if I enter 1.1 I get 1.1 and want to see 1.100

In other words, the number of decimals shown should always be the decimal qualifier (txtC_SIG_FIG)

????
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40229127
Then you'll have to adjust the Format property of the textbox as well:

Me.txtC_MIN.Format  = "0." & String([txtC_SIG_FIG], "0")

/gustav
0
 

Author Comment

by:SteveL13
ID: 40229186
Once more..  this is working great...

Me.txtC_MIN.Format  = "0." & String([txtC_SIG_FIG], "0")

But if the decimal qualifier is "0" and I enter 1 I am getting "1." (with the decimal.

Is there a way to get rid of the decimal if I enter a whole number and the qualifier is "0"?
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 40229208
Yes, though not very smart:

Me.txtC_MIN  = Format([txtC_MIN], "0" & Left(".", [txtC_SIG_FIG]) & String([txtC_SIG_FIG], "0"))
Me.txtC_MIN.Format  = "0" & Left(".", [txtC_SIG_FIG]) & String([txtC_SIG_FIG], "0")

/gustav
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question