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.

On Wed, 3 Mar 2010 22:19:01 -0800, LisaInAz
<(E-Mail Removed)> wrote:

>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
<it's got a fractional part>
End If

>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 W. Vinson [MVP]

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 wrote:
> 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.

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



"John Spencer" wrote:

> 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 wrote:
> > 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.
> .
>