We have an age calculation that must be rounded and stored.
1) The Form and Table settings are the same I've tried Fixed and General
formats.
2) But everything submitted and processed through the LET statement
is automatically rounded.
We want : 45.4 to be 45.4 and 45.6 to be 45.6
But, the VBA changes it to 45.00 and 46.00.

The form field values do not show this.
-->Is there a VBA or access setting that affects this?<--

Check the decimal places property on the controls that display the value,
also double check the dataType for the underlying field. Not all Number
dataTypes support fractional values.

"Philip McC" wrote:

> We have an age calculation that must be rounded and stored.
> 1) The Form and Table settings are the same I've tried Fixed and General
> formats.
> 2) But everything submitted and processed through the LET statement
> is automatically rounded.
> We want : 45.4 to be 45.4 and 45.6 to be 45.6
> But, the VBA changes it to 45.00 and 46.00.
>
> The form field values do not show this.
> -->Is there a VBA or access setting that affects this?<--
>
>

You don't have something set to an integer datatype do you? If so it will
just return integers, or whole number. Also, if you int() or val() set
somewhere, it will return integers.

"Philip McC" wrote:

> We have an age calculation that must be rounded and stored.
> 1) The Form and Table settings are the same I've tried Fixed and General
> formats.
> 2) But everything submitted and processed through the LET statement
> is automatically rounded.
> We want : 45.4 to be 45.4 and 45.6 to be 45.6
> But, the VBA changes it to 45.00 and 46.00.
>
> The form field values do not show this.
> -->Is there a VBA or access setting that affects this?<--
>
>

Table Settings
FIELD DATATYPE
DOB Date/Time
AGE Long Integer, Fixed Number (General was tried too)
RoundedAge Long Integer, Fixed Number
TextBox Long Integer, Fixed Number

Form Field Settings
FIELD CONTROL SRC FORMAT
TextBox =DateDiff("y",[DOB],Date(),0)/365 General Number, Auto
RoundedAge =Round([TextBox],2) General Number,
Auto
AGE [AGE]
Fixed, Auto
DOB [DOB]
Date/Time

NOTE: I tried both General Number & Fixed.

FINALLY, [AGE] field is stored in the table via an AfterUpdate for DOB:
Let AGE.Value = Forms![TestForm5]![RoundedAge]

"dch3" wrote:

> Check the decimal places property on the controls that display the value,
> also double check the dataType for the underlying field. Not all Number
> dataTypes support fractional values.
>
> "Philip McC" wrote:
>
> > We have an age calculation that must be rounded and stored.
> > 1) The Form and Table settings are the same I've tried Fixed and General
> > formats.
> > 2) But everything submitted and processed through the LET statement
> > is automatically rounded.
> > We want : 45.4 to be 45.4 and 45.6 to be 45.6
> > But, the VBA changes it to 45.00 and 46.00.
> >
> > The form field values do not show this.
> > -->Is there a VBA or access setting that affects this?<--
> >
> >

Long Integers cannot store decimal points. You'd need Age to be Single or
Double.

However, you should not be storing Age since it changes every day! Simply
calculate the age in a query, and use the query wherever you would otherwise
have used the table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Philip McC" <(E-Mail Removed)> wrote in message
news:1E9C9286-5CEA-4CEC-95D1-(E-Mail Removed)...
> Table Settings
> FIELD DATATYPE
> DOB Date/Time
> AGE Long Integer, Fixed Number (General was tried too)
> RoundedAge Long Integer, Fixed Number
> TextBox Long Integer, Fixed Number
>
> Form Field Settings
> FIELD CONTROL SRC FORMAT
> TextBox =DateDiff("y",[DOB],Date(),0)/365 General Number,
> Auto
> RoundedAge =Round([TextBox],2) General Number,
> Auto
> AGE [AGE]
> Fixed, Auto
> DOB [DOB]
> Date/Time
>
> NOTE: I tried both General Number & Fixed.
>
> FINALLY, [AGE] field is stored in the table via an AfterUpdate for DOB:
> Let AGE.Value = Forms![TestForm5]![RoundedAge]
>
> "dch3" wrote:
>
>> Check the decimal places property on the controls that display the value,
>> also double check the dataType for the underlying field. Not all Number
>> dataTypes support fractional values.
>>
>> "Philip McC" wrote:
>>
>> > We have an age calculation that must be rounded and stored.
>> > 1) The Form and Table settings are the same I've tried Fixed and
>> > General
>> > formats.
>> > 2) But everything submitted and processed through the LET statement
>> > is automatically rounded.
>> > We want : 45.4 to be 45.4 and 45.6 to be 45.6
>> > But, the VBA changes it to 45.00 and 46.00.
>> >
>> > The form field values do not show this.
>> > -->Is there a VBA or access setting that affects this?<--
>> >
>> >