this is probably very stupid, but I can't wrap my head around it

[mybrainhurts]

I have a number field in my table that I just can't seem to get it to
accept a number with a decimal in it. When I type in 2.5 it
automatically truncates it to 2. I have tried changing it to Fixed, and
everything else, but even in fixed and with decimal places showing, if
I type 2.5 it automatically sets it to 2.00. The reason this is
burning my ass so much is that I have two other time/date fields (time
start and time finish). I want to be able to have this number field
(total hours) populate itself automatically once I have typed in the
time_finish value.

I tried an after_update on the time_finish field

Me.total_hours = (("[time_start]"+"[time_finish]"))

but it keeps coming back saying that the value I entered is right for
the field, and debug brings me back to the code.

I don't know if the code is bad or if it isn't working because it
should be returning a half value (such as 2.5) and it doesn't match
with the addition of the two fields.

Any help would be greatly appreciated.

cheers
chanchito

 

[Douglas J. Steele]

Get rid of the quotes around the field names.

 

[Dirk Goldgar]

I have a number field in my table that I just can't seem to get it to
accept a number with a decimal in it. When I type in 2.5 it
automatically truncates it to 2. I have tried changing it to Fixed,
and everything else, but even in fixed and with decimal places
showing, if I type 2.5 it automatically sets it to 2.00. The reason
this is burning my ass so much is that I have two other time/date
fields (time start and time finish). I want to be able to have this
number field (total hours) populate itself automatically once I have
typed in the time_finish value.

I tried an after_update on the time_finish field

Me.total_hours = (("[time_start]"+"[time_finish]"))

but it keeps coming back saying that the value I entered is right for
the field, and debug brings me back to the code.

I don't know if the code is bad or if it isn't working because it
should be returning a half value (such as 2.5) and it doesn't match
with the addition of the two fields.

Any help would be greatly appreciated.

cheers
chanchito

Check the Field Size proeprty of the field in the table's design view.
Odds are, it's Long Integer or Integer, or maybe Byte. Those are all
integer data types, which can only store whole numbers. If you need
decimal fractions, use one of the floating-point formats, Single or
Double, or (conceivably) Currency or Decimal.

 

[mybrainhurts]

Check the Field Size proeprty of the field in the table's design view.
Odds are, it's Long Integer or Integer, or maybe Byte. Those are all
integer data types, which can only store whole numbers. If you need
decimal fractions, use one of the floating-point formats, Single or
Double, or (conceivably) Currency or Decimal.

this worked great. Thanks.

 

[mybrainhurts]

Get rid of the quotes around the field names.

Thanks for the help. If I could pick your brain a bit more that would
be even better. =)

I got rid of the quotes and the field will populate with a value, the
only problem is it is the wrong value. For example:

by putting a value of 23:00 in the time_start field, and then putting
02:00 in the time_finish field, the total_hours field then becomes
1.041667

Is this because I am dealing with time and it isn't the most math
friendly of units to be working with for access? Ultimately the correct
value would read as 3. 11pm to 2am would be a total of three hours
worked.

cheers
chanchito

 

[John Vinson]

by putting a value of 23:00 in the time_start field, and then putting
02:00 in the time_finish field, the total_hours field then becomes
1.041667

Is this because I am dealing with time and it isn't the most math
friendly of units to be working with for access? Ultimately the correct
value would read as 3. 11pm to 2am would be a total of three hours
worked.

Date/Time values are stored as double float numbers, counts of days
and fractions of a day.

If you want integer hours, try DateDiff("h", [Time_Start],
[Time_Finish]). Note however that this counts hour boundaries - that
is, if Time_Start is 1:58 am and Time_Finish is 2:02, you'll get a
full hour reported. If you want the time rounded to the nearest hour,
use a more complex expression, calculating minutes:

Round(DateDiff("n", [Time_Start], [Time_Finish]) / 60, 0)


John W. Vinson[MVP]

 

[mybrainhurts]

John,

Thanks for the explanation. The only problem that I am running into is
that when I put in the DateDiff code that you mentioned, I get some
strange answers again.

Example: 23:00 for time_start and 02:00 for time_finished, the
total_hours field ends up with a value of -21. I can see how the code
is generating the answer because it is infact a difference of minus 21
when working with those two values. However, for my purposes I need the
code to be able to recognize and generate the amount of time that has
passed between those two values so that it would come up with the
proper result of 3.

Thanks again
chanchito

 

[John Vinson]

John,

Thanks for the explanation. The only problem that I am running into is
that when I put in the DateDiff code that you mentioned, I get some
strange answers again.

Example: 23:00 for time_start and 02:00 for time_finished, the
total_hours field ends up with a value of -21. I can see how the code
is generating the answer because it is infact a difference of minus 21
when working with those two values. However, for my purposes I need the
code to be able to recognize and generate the amount of time that has
passed between those two values so that it would come up with the
proper result of 3.

The proper result is -21.

A Time value - without a date portion - is stored as a number between
0 (midnight, December 30, 1899) and 0.999999999999999
(11:59:59.9999999) on that same date. 2 am is in fact 21 hours before
11 pm, if you don't specify the date!

I can see two options. The preferable one would be to include the date
and time together - #9/24/2006 23:00:00# is in fact three hours before
#9/25/2006 02:00:00#, and DateDiff will find it to be so.

If for some reason you cannot do that, AND if your end time will never
be more than 24 hours after your start time, then you can use an
expression like

DateDiff("n", [Start_Time], [End_Time]) + IIF([Start_Time] >
[End_Time], 1440, 0)

to add 24 hours (1440 minutes) if the times span midnight.

John W. Vinson[MVP]

 

[Jamie Collins]

A Time value - without a date portion - is stored as a number between
0 (midnight, December 30, 1899) and 0.999999999999999
(11:59:59.9999999) on that same date.

Thinking of date and time values as being from the domain of double
precision floating point numbers is the wrong mental model, IMO. Such
thinking can quickly lead to misunderstandings e.g. to use your
example:

CREATE TABLE Test1 (
date_col DATETIME NOT NULL
)
;
INSERT INTO Test1 (date_col)
VALUES (0.999999999999999)
;
SELECT DATEVALUE(date_col), TIMEVALUE(date_col)
FROM Test1
;

The sensible conclusion is to think of dates as being from the domain
of date values and to operate on them using temporal functions only.

Another sensible conclusion is to consider Access/Jet DATETIME values
as being accurate to one second granularity e.g. to use your example:

INSERT INTO Test1 (date_col)
VALUES (#1899-12-30 11:59:59.9999999#)
;

Oops! That insert failed. The time value 11:59:59.9999999 does not
exist in the domain of Access/Jet DATETIME values.

Jamie.

--

 

[JK]

Assuming Total_Hours *never* exceeds 24 hours

Me.Total_Hours=([Time_Finish]+IIf([Time_Finish]<[Time_Start],1,0)-[Time_Start])*24

Result will be in decimal hours e.g 1h:15m=1.25

Regards/JK

by putting a value of 23:00 in the time_start field, and then putting
02:00 in the time_finish field, the total_hours field then becomes
1.041667

Is this because I am dealing with time and it isn't the most math
friendly of units to be working with for access? Ultimately the correct
value would read as 3. 11pm to 2am would be a total of three hours
worked.

Date/Time values are stored as double float numbers, counts of days
and fractions of a day.

If you want integer hours, try DateDiff("h", [Time_Start],
[Time_Finish]). Note however that this counts hour boundaries - that
is, if Time_Start is 1:58 am and Time_Finish is 2:02, you'll get a
full hour reported. If you want the time rounded to the nearest hour,
use a more complex expression, calculating minutes:

Round(DateDiff("n", [Time_Start], [Time_Finish]) / 60, 0)


John W. Vinson[MVP]

 

[Larry Linson]

The time value 11:59:59.9999999 does not
exist in the domain of Access/Jet DATETIME values.

Correct. Immaterial to the discussion at hand, but correct.

Larry

 

[mybrainhurts]

Thanks everybody who took the time to help me in this dillema. All the
help has been great.

Since the total hours worked would never go past 24 the code listed
below works like a charm JK.

cheers.
chanchito