Number Field give wrong result.

  • Thread starter Thread starter dbl
  • Start date Start date
D

dbl

Hi I have a form with a field called "LicHeldFor" the data has to be entered
in Years & Months. This data is used to work out the policy excess but
gives me the wrong result i.e. 00Yrs 02Mths returns a value of 2. Also 02Yrs
00Mts returns the value of 2. (If a driver has held a licence for less that
a full year then an additional excess is add to the policy)

I need all data under 12 months to give a result of less than 1 i.e. 6
months 0.06 11 months 0.11 as long as everything under 12 months returns
less that 1 it will work fine.

When I set up the field to show decimal places i.e. 00.01 it gives the
following result 0.00 for some reason it only gives full numbers 1 2 3 etc.

Is it possible to do this? if it is how do I go about it.

Thanks for your help

Bob
 
You can not use a number field to give you years and months by entering
02.11. Standard numbers a decimal - otherwise known as base 10. Every
number is in powers of 10. Years have 12 months.

Access uses a numerical value to display dates and time. Each hole number
is a day and anything less than a day is a decimal faction. On display
Access converts the number to years, months, days, hours, minutes, and
seconds according to the selected presentation method.

You could use a date-time field to do what you want with a lot of work but
you did not say that the data was to be used in any calculation. So why not
just use a text field with an input mask.
 
On the surface it would appear what you really want is to capture the date a
License was issued and then calculate the number of years/months that have
passed since then.
so you would store a DateIssued as a date/time field and then do a
calculation to determine the number of years/months that have passed.

Ed Warren
 
That's correct, but most people only known the year the licence was issued,
which is fine until the licence has been
issued for less then a full year then the full date would be required.

Is it possible then to enter a part date i.e. just the year "1958" and when
needed the full date i.e. 20-05-2005 ?

Thanks for your help Bob
 
No, in a date time field you must enter a full date/time.

What I do in a case like that is to pick an arbitary date like 6/1/1958
June 1st, the middle of the year, or 1/1/1958, the start of the year and use
that if I don't know the exact date, then set a unique time for these
'unknown' e.g. 1/1/1958 00:00:01 (assumes not many licences are issued on
new year's day) (a 'real issue date' would be 1/1/1958 00:00:00) then I can
enter what I know and find them based on the time.

Hope this helps

Ed Warren
 
Ed I have set up a new date/time field called "LicHeldDate" I have then set
up a new field Text472 and entered the following code.

=Age([LicHeldDate])&" Yrs "&AgeMonths([LicHeldDate])&" Mths "

This gives me the time the licence has been held for in Yrs and Mths (That
part works fine) but when I use this with the code below,
00 Yrs 01 Mths still reports as the value of 1 so when it looks up the time
a licence has been held it does not pick up a novice driver. I need
everything under a full year to show as less than 1.

If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
(This is only part of the code used but the part with the problem)

Any idea's on how I can get Text472 to show less that 1 for any licence held
for less that a full year.

Thanks for held Bob
 
You're TextBox472 is storing a "text" value not an numeric value, you have
"00 Yrs 01 Mths" entered into that field, therefore the quesion ?<1 is
non-sensical and access is returning the 'correct' but wrong answer.
Assuming Age(...) returns either an integer or double, what you want is
another texbox with a value set to =Age([LicHeldDate]) this will be
0,1,2,3,.... etc. now you can ask the question is it <1 and get the
'correct' correct answer.

You can also use the following:

if (Me.ExAge >=25 and Age([LicHeldDate])<1) then
.....
end if

Ed Warren


dbl said:
Ed I have set up a new date/time field called "LicHeldDate" I have then
set
up a new field Text472 and entered the following code.

=Age([LicHeldDate])&" Yrs "&AgeMonths([LicHeldDate])&" Mths "

This gives me the time the licence has been held for in Yrs and Mths (That
part works fine) but when I use this with the code below,
00 Yrs 01 Mths still reports as the value of 1 so when it looks up the
time
a licence has been held it does not pick up a novice driver. I need
everything under a full year to show as less than 1.

If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
(This is only part of the code used but the part with the problem)

Any idea's on how I can get Text472 to show less that 1 for any licence
held
for less that a full year.

Thanks for held Bob



Ed Warren said:
No, in a date time field you must enter a full date/time.

What I do in a case like that is to pick an arbitary date like 6/1/1958
June 1st, the middle of the year, or 1/1/1958, the start of the year and
use that if I don't know the exact date, then set a unique time for these
'unknown' e.g. 1/1/1958 00:00:01 (assumes not many licences are issued
on new year's day) (a 'real issue date' would be 1/1/1958 00:00:00) then
I can enter what I know and find them based on the time.

Hope this helps

Ed Warren
Click to expand...
Click to expand...
 
Ed thanks very much that now works just as I need to.

Thanks Bob

Ed Warren said:
You're TextBox472 is storing a "text" value not an numeric value, you
have "00 Yrs 01 Mths" entered into that field, therefore the quesion ?<1
is non-sensical and access is returning the 'correct' but wrong answer.
Assuming Age(...) returns either an integer or double, what you want is
another texbox with a value set to =Age([LicHeldDate]) this will be
0,1,2,3,.... etc. now you can ask the question is it <1 and get the
'correct' correct answer.

You can also use the following:

if (Me.ExAge >=25 and Age([LicHeldDate])<1) then
....
end if

Ed Warren


dbl said:
Ed I have set up a new date/time field called "LicHeldDate" I have then
set
up a new field Text472 and entered the following code.

=Age([LicHeldDate])&" Yrs "&AgeMonths([LicHeldDate])&" Mths "

This gives me the time the licence has been held for in Yrs and Mths
(That
part works fine) but when I use this with the code below,
00 Yrs 01 Mths still reports as the value of 1 so when it looks up the
time
a licence has been held it does not pick up a novice driver. I need
everything under a full year to show as less than 1.

If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
(This is only part of the code used but the part with the problem)

Any idea's on how I can get Text472 to show less that 1 for any licence
held
for less that a full year.

Thanks for held Bob



Ed Warren said:
No, in a date time field you must enter a full date/time.

What I do in a case like that is to pick an arbitary date like 6/1/1958
June 1st, the middle of the year, or 1/1/1958, the start of the year and
use that if I don't know the exact date, then set a unique time for
these 'unknown' e.g. 1/1/1958 00:00:01 (assumes not many licences are
issued on new year's day) (a 'real issue date' would be 1/1/1958
00:00:00) then I can enter what I know and find them based on the time.

Hope this helps

Ed Warren

That's correct, but most people only known the year the licence was
issued, which is fine until the licence has been
issued for less then a full year then the full date would be required.

Is it possible then to enter a part date i.e. just the year "1958" and
when needed the full date i.e. 20-05-2005 ?

Thanks for your help Bob

On the surface it would appear what you really want is to capture the
date a License was issued and then calculate the number of
years/months that have passed since then.
so you would store a DateIssued as a date/time field and then do a
calculation to determine the number of years/months that have passed.

Ed Warren

Hi I have a form with a field called "LicHeldFor" the data has to be
entered in Years & Months. This data is used to work out the policy
excess but gives me the wrong result i.e. 00Yrs 02Mths returns a
value of 2. Also 02Yrs 00Mts returns the value of 2. (If a driver has
held a licence for less that a full year then an additional excess is
add to the policy)

I need all data under 12 months to give a result of less than 1
i.e. 6 months 0.06 11 months 0.11 as long as everything under 12
months returns less that 1 it will work fine.

When I set up the field to show decimal places i.e. 00.01 it gives
the following result 0.00 for some reason it only gives full numbers
1 2 3 etc.

Is it possible to do this? if it is how do I go about it.

Thanks for your help

Bob
Click to expand...
Click to expand...
Click to expand...
 
Back
Top