Solved

Access front-end truncates instead of rounds my decimal input

Posted on 2011-09-21
21
444 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
[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
  • 9
  • 8
  • 4
21 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 36574742
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
ID: 36574916
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 50

Expert Comment

by:Gustav Brock
ID: 36574958
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:oskar509
ID: 36575069
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
ID: 36575112
There's input masks.
They are evil and I hate them but an input mask of ##.# gets you there
0
 

Author Comment

by:oskar509
ID: 36575414
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
ID: 36575516
<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
ID: 36711811
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
ID: 36711893
<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 50

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 36714743
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
 

Author Comment

by:oskar509
ID: 36718905
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
ID: 36719030
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
ID: 36719123
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
ID: 36719984
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
ID: 36720065
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 50

Expert Comment

by:Gustav Brock
ID: 36720298
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
ID: 36720368
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
ID: 36720518
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
ID: 36720605
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
ID: 36720607
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 50

Expert Comment

by:Gustav Brock
ID: 36813309
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

726 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