Correct Currency Data Type and Validation Coding

[LisaInAz]

Any suggestions are so greatly appreciated as I am really really stuck
Using Access 2003 on XP OS

My Access and VBA experience is limited.

I have a several fields that I am checking for validation as currency.

I have two questions
1) what is the best data type for currency. Currently I am using Table Data
Type = Number. (I have tried other types with no luck)
Field Size = Single Format=Currency. On the form I am formating to currency.

2) I want to be able to check for whole numbers and for those that are
entered with a decimal.

Currently I am using the below.


Private Sub OverUnder_AfterUpdate()
With Me.OverUnder
If Not IsNull(.Value) Then
If InStr([OverUnder], ".") = 0 Then
.Value = .Value / 100
End If
End If
End With
End Sub

However, when I type 45.00 it comes out as .45 When I type 4500 it comes out
as 45.00

I have tried to search on line. I just don't seem to be hitting the right
criteria.

 

[John W. Vinson]

Any suggestions are so greatly appreciated as I am really really stuck
Using Access 2003 on XP OS

My Access and VBA experience is limited.

I have a several fields that I am checking for validation as currency.

I have two questions
1) what is the best data type for currency. Currently I am using Table Data
Type = Number. (I have tried other types with no luck)

Oddly enough, the best datatype is.... Currency. It's not one of the subtypes
of Number, it's a different datatype in its own right.

A Currency value is a huge scaled integer, with exactly four decimal places
(no more, no fewer) and a range into the trillions. Not enough for the US
national debt though.

Field Size = Single Format=Currency. On the form I am formating to currency.

The trouble with Single is that it is an approximation, with roundoff error.

2) I want to be able to check for whole numbers and for those that are
entered with a decimal.

Currently I am using the below.


Private Sub OverUnder_AfterUpdate()
With Me.OverUnder
If Not IsNull(.Value) Then
If InStr([OverUnder], ".") = 0 Then
.Value = .Value / 100
End If
End If
End With
End Sub

However, when I type 45.00 it comes out as .45 When I type 4500 it comes out
as 45.00

No type of number is a string, and no number will have a decimal point
*character* in it. It is stored as a binary number; the decimal comes when
it's formatted for display. To see if a Single, Double, Decimal or Currency
value is a whole number, you can use

If Fix([fieldname]) = [fieldname] Then
< it's a whole number>
Else

I have tried to search on line. I just don't seem to be hitting the right
criteria.

Use a Currency datatype for your data; you may want to use the Round()
function if you want calculations rounded to two decimals: e.g.

[PurchasePrice] * [TaxRate]

might give you a value of 3.1025, which would be displayed as 3.10 but would
end up causing trouble; it isn't EQUAL to 3.10 (even though it appears to be),
and the odd fractions of a cent will add up to throw your totals off;

Round([PurchasePrice] * [TaxRate])

will round to two decimals. Note that this is "banker's rounding" - if the
next digit is 5 it will round either up OR down to the nearest *even* value:

?Round(3.245, 2) = 3.24
?Round(3.235, 2) = 3.24

This keeps the average of the rounded values closer to the average of the raw
values, whereas the traditional "always round 5 up" makes the rounded values
creep up.

 

[John Spencer]

The following should work. I think your code was looking at the field's value
and not the control's value. The field's value has not yet changed, the
control's value has changed.

Private Sub OverUnder_AfterUpdate()
With Me.OverUnder
If InStr(.Value & "", ".") = 0 Then
.Value = .Value / 100
End If
End With
End Sub

As John Vinson noted, for currency it is usually best to the use the currency
data type.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[LisaInAz]

Thank you all- I will try all suggestions - in reference to using currency
for data type in Access - I read some were that it wouldn't convert nicely if
the application was to ever move to SQL Server (which mine possible could)
....the person recommend to use Decimal in Access - but I don't understand.
of course this was from a 2005 post. And maybe things will have advanced by
then.

By the way I did try the below and debug - I can see that my value comes
across as
15 instead of 15.00 - and would bring back .15 instead of the 15.00- at
least i resolved why the below didn't work for me.

Private Sub OverUnder_AfterUpdate()
With Me.OverUnder
If InStr(.Value & "", ".") = 0 Then
.Value = .Value / 100
End If
End With
End Sub

The following should work. I think your code was looking at the field's value
and not the control's value. The field's value has not yet changed, the
control's value has changed.

Private Sub OverUnder_AfterUpdate()
With Me.OverUnder
If InStr(.Value & "", ".") = 0 Then
.Value = .Value / 100
End If
End With
End Sub

As John Vinson noted, for currency it is usually best to the use the currency
data type.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Any suggestions are so greatly appreciated as I am really really stuck
Using Access 2003 on XP OS

My Access and VBA experience is limited.

I have a several fields that I am checking for validation as currency.

I have two questions
1) what is the best data type for currency. Currently I am using Table Data
Type = Number. (I have tried other types with no luck)
Field Size = Single Format=Currency. On the form I am formating to currency.

2) I want to be able to check for whole numbers and for those that are
entered with a decimal.

Currently I am using the below.


Private Sub OverUnder_AfterUpdate()
With Me.OverUnder
If Not IsNull(.Value) Then
If InStr([OverUnder], ".") = 0 Then
.Value = .Value / 100
End If
End If
End With
End Sub

However, when I type 45.00 it comes out as .45 When I type 4500 it comes out
as 45.00

I have tried to search on line. I just don't seem to be hitting the right
criteria.

.