Number fields

[budgie]

I am looking for a way to have a decimal point inserted automatically before the last two digits of whatever I enter, e.g. entering 123 would result in 1.23 being stored. In Excel this was called "Accountant's Keypad" I believe.
I have tried various number types and fixed decimals, and input masks, but can't make it work.
My version of Access is from the Office 2000 suite.

[Minty]

You could simply divide the entered value by 100?
But What happens if you really only want to enter 1.00

You would have to type 100, instead of simply 1 , so I'm not sure what the saving is?

[budgie]

The saving is that if you're entering a lot of dollar and cent values you don't have to use the decimal point key. That adds up to a lot of keystrokes after a time. If entering a whole dollar amount you can terminate with a "." instead of the two additional zeroes.
BTW setting the field as "currency" will not provide the function I require, at least not in my version of Access.

[Welshgasman]

Jeez,
I am not the fastest typist in the world, but even I would not even bother with that 'shortcut'?
As mentioned you could divide by 100, after some basic checks?

This should get you started.

Private Sub ToleranceValue_LostFocus()
If InStr(1, Me.ToleranceValue, ".") > 0 Then
    Me.ToleranceValue = Val(CStr(Me.ToleranceValue) & ".00")
End If
Me.ToleranceValue = Me.ToleranceValue / 100
End Sub

[Minty]

You point about pressing the decimal point when you need to is what makes this difficult, or at least harder.
If you use a function you can detect if it contains a decimal point and if so not process it.
if it doesn't have a decimal point then divide by 100.

This is aircode but something like

Function fnDecmalEntry(dNumber As Currency) As Currency


    
    If InStr(1, CStr(dNumber), ".") > 0 Or dNumber = 0 Then
        fnDecmalEntry = dNumber
        Exit Function
    End If
            
    fnDecmalEntry = dNumber / 100




End Function

[Micron]

Shouldn't be too hard to code something in the BeforeUpdate event for the form control, but that's the limitation (must use a form).
Logic being "if ends with . just remove it, otherwise divide by 100. Not sure, but perhaps you don't have to remove the trailing . Access just might drop it anyway.

EDIT - just tried leaving the . at the end directly in a table - it was just dropped. So even simpler than the posted code?

If Right([fieldnamehere],1) = "." then simply divide?

EDIT 2 - works if it is a whole number only, so not so simple I guess.

[Welshgasman]

Shouldn't be too hard to code something in the BeforeUpdate event for the form control, but that's the limitation (must use a form).
Logic being "if ends with . just remove it, otherwise divide by 100. Not sure, but perhaps you don't have to remove the trailing . Access just might drop it anyway.

EDIT - just tried leaving the . at the end directly in a table - it was just dropped. So even simpler than the posted code?

If Right([fieldnamehere],1) = "." then simply divide?

Yes, this appears to work

If Not Right(Me.ToleranceValue, 1) = "." Then
    Me.ToleranceValue = Me.ToleranceValue / 100
End If

[Micron]

I added an edit. Does it work with numbers that are not whole numbers? Does the data type matter (integer, decimal, currency, long) I wonder?

[Minty]

Hmm my aircode doesn't actually work, unless the input is really a string.
If you enter 10.0 as a number access strips the .0 from it during evaluation, so you end up with 0.1 as a result.

I'll revisit it later.

[Welshgasman]

I used double for the table and currency format for the form?
O/P will have to do some testing?