Trouble turning period into comma

[Esoteric]

Hi guys,

I'm trying to turn any period entered into a certain textfield on my form into a comma
(to prevent access from turning 1.1 into 11 for example).

I have the on dirty event set to me.fieldname = replace(me.fieldname,".",",").
Everything works fine as long as I use the period key on the numpad. However, when I use the regular period key on the keyboard, it does not convert it.

Any ideas on what might cause this, and how to solve it?

Thanks

 

[SOS]

try using the Before Update event instead and then also change to this:

me.fieldname = replace(me.fieldname, Chr(44),Chr(46))

 

[missinglinq]

There's nothing wrong with your code, you simply picked the wrong event to use! Put your original code in the Control's AfterUpdate event.

You can't do this kind of thing in a Control's BeforeUpdate event; you'll pop an error.

Trying to do it in the OnDirty event didn't work because that event fires the instant the first character is entered in any Control! Obviously, when the first character is entered into the Record, the Control in question, your fieldname, doesn't have any data in it to be changed!

Linq ;0)>

 

[Esoteric]

Have tried both suggestions, but unfortunately the problem persists.

Whenever I enter a number that contains a period into my text box and press enter, the period gets removed and the incorrect value is entered into my database (e.g. 1.1 turns into 11 after enter is pressed).

The AfterUpdate event is now set to me.fieldname = replace(me.fieldname,".",",").

It seems to have no effect whatsoever.

 

[pr2-eugin]

Have you set any Format to the Control? What is the type of the Field that is associated with this control?

 

[Esoteric]

Have you set any Format to the Control? What is the type of the Field that is associated with this control?

Format is set to General Number. The field type is Number with field size set to Double.

 

[pr2-eugin]

So why would a Number field be using a String manipulation function? If you have set the type of the field to be Double, it should store the values as entered.. 1.1 will be 1.1..

 

[spikepl]

The behaviour you describe corresponds to "." being the thousand separator, and so "." should not be used for entering decimal numbers in your environment.


Update:
If you have problems with your keyboard then it's probably becasue it is not set to the same input language as the language setting that determines the decimal separator.

 

[Esoteric]

I did not realize the replace function applies to strings only.

In the current system the users are entering the values as text and are able to use either periods or commas when entering decimal numbers. When they switch over to Access, I wanted to still allow them to use both options to avoid confusion.

If this is not possible then I guess I'll have to solve it in a different way.

Thanks for your responses.

 

[pr2-eugin]

I did not realize the replace function applies to strings only.

Okay, maybe I might have worded that wrong.. But that is not what I meant.. Replace will return a String value.. If you try to use a String to a Number, it will not like it..

In the current system the users are entering the values as text and are able to use either periods or commas when entering decimal numbers. When they switch over to Access,

I think the main issue is because you are using a Format on the Control.. Remove the Format.. This will allow it to treat the entry as String.. Then in the After Update event..

Private Sub controlName_AfterUpdate()
    Me.controlName = CDbl(Replace(Me.controlName, ",", "."))
End Sub

 

[pr2-eugin]

Okay I just recreated your problem, I think it is because of the fact it is also bound to the field which has its type set to be Double it is considering the value to be a separator as spike has suggested..

 

[gemma-the-husky]

I did not realize the replace function applies to strings only.

In the current system the users are entering the values as text and are able to use either periods or commas when entering decimal numbers. When they switch over to Access, I wanted to still allow them to use both options to avoid confusion.

If this is not possible then I guess I'll have to solve it in a different way.

Thanks for your responses.

enabling number entry with both embedded commas and periods sounds to me like a recipe for confusion. the decimal separator ought to be whatever is appropriate for the selected locale surely.

when you say "they are able to use both (coomas and periods)" what system are they currently using that accepts both?

 

[Esoteric]

enabling number entry with both embedded commas and periods sounds to me like a recipe for confusion. the decimal separator ought to be whatever is appropriate for the selected locale surely.

when you say "they are able to use both (coomas and periods)" what system are they currently using that accepts both?

They are currently entering the numbers as strings. I need those numbers for calculations so don't want to store them as text.
The problem is that for some values they use commas as the decimal separator, and for others they use periods.

Seeing as people generally don't like changes, or get confused by them, I wanted to allow both options in the new system as well.

 

[gemma-the-husky]

They are currently entering the numbers as strings. I need those numbers for calculations so don't want to store them as text.
The problem is that for some values they use commas as the decimal separator, and for others they use periods.

Seeing as people generally don't like changes, or get confused by them, I wanted to allow both options in the new system as well.

personally, i think it most unusual to enter numbers as strings. it sounds to me like an older 3GL solution. I would have thought people would quickly get used to using the format appropriate for the locale. ie I have only ever used a period. I would imagine if I moved to Europe and had to get used to inputting a comma there would be a bit of a learninig curve, but I would soon get used to it.

However, if you are keen to go down your strings to numbers plan, I think you will need to build up your own standard strings to numbers library to convert and validate numbers from both formats, control the number of dps, limit the range of numbers, etc, and accept suitable separators.

the easiest way is probably to accept a string, replace commas with periods (or vice versa, depending on your standard separator) and then test whether you have a number using the isnumeric function.