BIG problem: Time stored as text & cannot re-format table

Morning all-

Here's my goal:

I have to take "Start Time" + "Max Hours" - "Break" = "End Time".

Here are my problems:

"Start Time" is a text field and I am unable to change the format of the
field in the table (it'll mess up the entire system). To add to the fun, this
is also Zulu time so I cannot use "TimeValue" as it will take 0801 and make
it 8:01 AM which is incorrect.

"Max hours" and "Break" are from formulas (ie IIf statements, etc.) and are
not stored as hours or minutes, just as general numbers (I guess).

So, right now, it looks like this if you try to get to my goal:

0801 + 15 - 0.25 = 814.2358645

I'm stuck and frustrated so any suggestions are highly appreciated.

Use TimeValue to convert it to a time, then use DateAdd to add or subtract
the appropriate number of hours to convert it to your local time. You'll
also need to use DateAdd to add max hours and subtract break. If you don't,
adding 15 will add 15 days, not 15 hours.

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


"Cita" wrote in message
...
Morning all-

Here's my goal:

I have to take "Start Time" + "Max Hours" - "Break" = "End Time".

Here are my problems:

"Start Time" is a text field and I am unable to change the format of the
field in the table (it'll mess up the entire system). To add to the fun,
this
is also Zulu time so I cannot use "TimeValue" as it will take 0801 and
make
it 8:01 AM which is incorrect.

"Max hours" and "Break" are from formulas (ie IIf statements, etc.) and
are
not stored as hours or minutes, just as general numbers (I guess).

So, right now, it looks like this if you try to get to my goal:

0801 + 15 - 0.25 = 814.2358645

I'm stuck and frustrated so any suggestions are highly appreciated.