Solved

Access front-end truncates instead of rounds my decimal input

Posted on 2011-09-21
21
407 Views
Last Modified: 2012-06-22
Hello,

I'm building an Access 2007 front-end to my SQL Server 2008 database. I have a form to input data with a field for "HeightInInches". In the database, this field is restricted to 1 decimal point, but of course people like to enter whatever they have. If more than 1 decimal point is entered, Access truncates the extra numbers. I would rather it round them, like what happens if I enter it directly into the database. I've tried using the round function on Before Update, but it doesn't help. (BTW, I first put the round function with After Update, which I then realized was silly. It obviously didn't work.)

Any thoughts?

Thanks in advance,
oskar
0
Comment
Question by:oskar509
  • 9
  • 8
  • 4
21 Comments
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Throw a format on the control so that no more than 1 decimal place can be entered in the first place!
0
 

Author Comment

by:oskar509
Comment Utility
I've set the format for the control to one decimal, and it seems I can still type as many decimals as I want, then on losing focus it displays just the one decimal which represents the truncated value.

And I don't mean to disparage my data entry people, but even if I can get the format to work, I'm not confident they can take any number of decimals on paper and properly round it to 1 decimal for entry. I'm just trying to make it idiot-proof, not that I have any idiots working here. It's just about solid design.

Thanks,
oskar
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
If the textbox is bound to the field in the table, you can use the AfterUpdate event to adjust the entered value:

Me!txtInput.Value = CDbl(Format(Nz(Me!txtInput.Value, 0), "#.0"))

/gustav
0
 

Author Comment

by:oskar509
Comment Utility
The control is bound to a decimal field with a precision and scale of 3,1 (or ##.#)
I've thought about having the data entry go into an unbound textbox and formatting it as you suggest before feeding it to the decimal field, but it seems like a lot of work for something that should have a more straightforward solution. (I have multiple decimal fields with varying formats.)

Also, I can't wrap my head around why the Round function in the Before Update isn't working.

oskar
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
There's input masks.
They are evil and I hate them but an input mask of ##.# gets you there
0
 

Author Comment

by:oskar509
Comment Utility
Yeah, I've tried input masks, but it still makes the user do the rounding. I may just have to go with that for now.

Do you think having the Round function on Before Update is right, or is there a better place?

oskar
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
<Yeah, I've tried input masks, but it still makes the user do the rounding>
If you mean that in terms of the user has to do the rounding in their head, then yes.

I'd maybe cheat!
Bump your field to take 2 decimal places
Throw in a two digit input mask ##.##
and THEN do the Round in the AfterUpdate event.

They enter the minimum for you to get a good rounding, nothing hassles you and you don't run into any of the bad things that always assail me when I use BeforeUpdate.
0
 

Author Comment

by:oskar509
Comment Utility
The AfterUpdate event is too late. It's already truncated to one decimal by then.
What I'm not understanding is why the same thing happens with the BeforeUpdate event. I've done some more troubleshooting and it seems like the Round Function on the BeforeUpdate event is receiving the already-truncated value as input.

Does anyone know how to round the user input data BEFORE it gets saved as the value of the field - in an elegant way. I know I can capture the user input in an unbound text box, round it, and then set the field value to the output, but that seems ugly, ...and not at all the way Access was intended to work.

Thanks so much for your ideas!
oskar :-)
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
<The AfterUpdate event is too late. It's already truncated to one decimal by then.>
Did you follow my 'cheating' logic?
<Bump your field to take 2 decimal places>
So no, it wouldn't be truncated to one decimal yet
<Throw in a two digit input mask ##.##>
So your people enter two decimals
<and THEN do the Round in the AfterUpdate event.>
To get yourself the one decimal you want
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
Comment Utility
You can make the textbox unbound. Also rename the textbox.
Bind the data field to a hidden textbox.

Then use the OnCurrent event to retrieve (from the bound textbox) and display the value, and the AfterUpdate event to round and set the value of both the visible and the hidden textbox.

/gustav
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:oskar509
Comment Utility
Nick,

Thanks for your continued attention to my problem.

I did try cheating: I changed the field to accept 2 decimals. I used an input mask to restrict user input to 2 decimals. But it still doesn't round. A 65.29 stays a 65.29 no matter what I do.

I've used the built-in Round() function, and also written my own, which doesn't do the rounding any differently, but lets me print out messages about what's going on. I've tried both of these in BeforeUpdate and AfterUpdate. It never works.

It seems like it just doesn't like me messing with bound data. I can assign the value of the control to a variable and it rounds just fine. But it won't let me assign that value back to the control. I was going to go so far as to use an update query to set the value to the rounded number, but it was messy and there were a lot of error messages to trap.

I'm going to give up and use an unbound txtBox for data entry, round it, and assign it to the control.

Still perplexed and confused, but that's not new.

Thanks,
oskar
0
 

Author Comment

by:oskar509
Comment Utility
Gustav,

Thanks for your suggestion. This is what I was trying to avoid because I thought it would force me to have to do a lot of verification of the data that happens automatically when entering directly into the control - like that it's numeric and not bigger than the field will allow, etc. But it seems I don't have a choice. Nothing else I've tried will let me Round the number entered into the control.

I've set it up, and it wasn't that difficult to make sure people enter the kind of data that they should. It works well, actually. I just can't believe this is the way it's supposed to work. I found this bit of code from MS:

http://support.microsoft.com/kb/97524

which is what I expected to be able to do - round the value of a bound control. But it just doesn't work.

Thanks for you help in getting me something that works!
oskar
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Here's a shim that demo's it working
Although Round is rounding 95.45 to 95.4 which is not expected.
<<
Bankers rounding
The Round() function in Access uses a bankers rounding. When the last significant digit is a 5, it rounds to the nearest even number. So, 0.125 rounds to 0.12 (2 is even), whereas 0.135 rounds to 0.14 (4 is even.)
The core idea here is fairness: 1,2,3, and 4 get rounded down. 6,7,8, and 9 get rounded up. 0 does not need rounding. So if the 5 were always rounded up, you would get biased results - 4 digits being rounded down, and 5 being rounded up. To avoid this, the odd one out (the 5) is rounded according to the previous digit, which evens things up.
If you do not wish to use bankers rounding, get Ken Getz' custom function (linked above.)>>
http://allenbrowne.com/round.html


The data type, and formats are all important in both the table and the form
It took me long than I thought to make it work the way I described, but it does work.
The field in the table is Decimal | General Number | Precision = 18 | Scale = 2| Decimal Places = 2
On the form, it is General Number | Decimal Places = 2
and the AfterUpdate event is
Me.EntryValue = Round(Me.EntryValue, 1)

Had to get them all correct before it played nice though
rounding.mdb
0
 

Author Comment

by:oskar509
Comment Utility
Nick,

I know about the bankers rounding and I like it. It makes sense.

I think the problem is that Access isn't playing nice with SQL Server. I've tried setting the precision and scale that same, but it doesn't work like it does with the mdb.

It's like Access won't let me do any manipulations on bound data. And it would all be fine if Access would just act like an open portal to SQL Server because SQL Server would ROUND my data, but Access has to butt-in and TRUNCATE it beforehand. AARRGGGHHH!

I've given up. I've got it working with the data entry going into unbound txtBoxes.

Thanks for all your suggestions,
oskar
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
Comment Utility
Before I got all the settings right on the field data type, I got the same thing.
First it rounds to integer
then it still did
and then again
and then it finally played nice

I built a SQL Server Table and pointed the shim at it, and it worked the same as the shim did with a local table
Here it is scripted out
 
USE [TI_Data]
GO

/****** Object:  Table [dbo].[tblEntries]    Script Date: 09/28/2011 14:43:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblEntries](
	[EntryID] [int] IDENTITY(1,1) NOT NULL,
	[EntryValue] [decimal](18, 2) NULL,
	[timestamp] [timestamp] NOT NULL
) ON [PRIMARY]

GO

Open in new window

Here is the linked table shim too.

I realize you've moved on.
I hate unsolved mysteries
rounding.mdb
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
This is where you loose track:

[EntryValue] [decimal](18, 2) NULL,

To test, it should be:

[EntryValue] [decimal](18, 1) NULL,

Try (can even be done with Access 2007 and a local table) and you will see that Oskar is right; any decimal after the first gets truncated when the textbox is bound.

/gustav

0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Right.  That's why I made it 2.  That way it DOESN'T get truncated after one decimal, but after 2.  And then the AfterUpdate can round it!  I did test both shims before posting :)
0
 

Author Comment

by:oskar509
Comment Utility
I hate unsolved mysteries too, and now I have a new one b/c it's working!

But, I swear I'd done all of that before. I was testing it all on the "height" field, but we have several fields where we want the precision to be 1 decimal, but the scale varies (i.e. weight can certainly be >100 lbs, but if you type in height_in_inches >100, that's probably a typo).

So, I never changed the scale to 18, but I definitely changed the precision to 2 (in sql server). I tried a variety of formats for Access:
-General Number
-Fixed Number
-0,1,2 decimals

It seriously didn't work for me before.

Well now, I've got all of those decimal fields with precision of 2 and scale appropriate to the expected data (sql server) and Access set at General Number and decimal=Auto (just because I forgot to change it), and the simple Round() function in AfterUpdate. And it works for some reason I don't know b/c I probably changed too many things at one time rather than isolating my variables.

I seriously don't like not knowing, but it's working, so I won't complain. Well, except for this: the only downside of having the precision be 2 when you really want 1 is that if there's any data entry outside of this interface, there's no check on that second decimal being zero. I guess I can put some trigger there if that ever happens. Besides, that's the beauty of being the only person who can get around the front-end!

Both methods (nick's and gustav's) are there and working. I'm just going to hide one of them so that if I find any kinks in one method, I can easily switch to the other.

Thanks to both of you!!!
oskar :-)
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
It was the only decent way I could think of to not have the data entry people have to do the rounding, because, as you saw, the minute that the field won't accept more than one decimal, it truncates befoe you can round it.

That's the one thing with building a quick shim.
There's nothing to gum the works up with>
And if you can make it work in a shim, you know you should be able to make it work for real.

Glad you got it figured
0
 

Author Closing Comment

by:oskar509
Comment Utility
Both solutions ultimately provided for the functionality I needed, but they both are a work-around to something that may very well be a problem with MS Access front-end to SQL Server back-end. I would love to see how this configuration can work without either a) having to set the precision of a field to something other than what the data should be or b) having to program checks on unbound data entry that would happen automatically if the data were bound. For now, I blame Microsoft. Kudos to Nick and Gustav!
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome. However, I don't think you should blame anyone including MS as this is "by design" and to my knowledge the behaviour of most other SQL engines is the same.

Raising the decimal count in the field to two just moves the issue to the third decimal and beyond.

Also, you could create a trigger in SQL Server that performs the rounding for you for updates/inserts of values to one decimal and then adjust the decimal count for the field to a "large enough" values, say four or six.

/gustav
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now