Writing an expression using fields with different formats

I need to subtract a field sum from another, but they are in different
formats. How can I convert one of themor get one to recognize the other?
For example:
Total Shift Time (hh:nn:ss)
Wrap Up Time (standard)

Need to do the following expression:
[Total Shift Time]-[Wrap Up Time]

The problem is I'm pulling the data from two different sources, one provdies
it in hh:nn:ss format, and the other provides the data in standard number
format.

Format does not matter. What matters is the datatype of the field it is
stored in. Are they the same or compatible?
A datetime fields can be subtracted from a datetime field no mattter the
format they are displayed in. But if one is datetime and the other is text
you must do some format/calculation/manipulation.

the other provides the data in standard number format.
Numbers may look the same but be stored in different ways - integer, single,
double, currency.
--
KARL DEWEY
Build a little - Test a little


"Whitney" wrote:

I need to subtract a field sum from another, but they are in different
formats. How can I convert one of themor get one to recognize the other?
For example:
Total Shift Time (hh:nn:ss)
Wrap Up Time (standard)

Need to do the following expression:
[Total Shift Time]-[Wrap Up Time]

The problem is I'm pulling the data from two different sources, one provdies
it in hh:nn:ss format, and the other provides the data in standard number
format.

Sorry for the confusion:

Total Shift Time (Date/Time)
Wrap Up Time (Number)

"format/calculation/manipulation" what can you suggest?



"KARL DEWEY" wrote:

Format does not matter. What matters is the datatype of the field it is
stored in. Are they the same or compatible?
A datetime fields can be subtracted from a datetime field no mattter the
format they are displayed in. But if one is datetime and the other is text
you must do some format/calculation/manipulation.

the other provides the data in standard number format.
Numbers may look the same but be stored in different ways - integer, single,
double, currency.
--
KARL DEWEY
Build a little - Test a little


"Whitney" wrote:

I need to subtract a field sum from another, but they are in different
formats. How can I convert one of themor get one to recognize the other?
For example:
Total Shift Time (hh:nn:ss)
Wrap Up Time (standard)

Need to do the following expression:
[Total Shift Time]-[Wrap Up Time]

The problem is I'm pulling the data from two different sources, one provdies
it in hh:nn:ss format, and the other provides the data in standard number
format.

A datetime field store a 'point in time' and not an 'amount of time' as you
are using it.
Try this if you have seconds in [Wrap Up Time] ---
DateDiff("s",0,[Total Shift Time]-Int([Total Shift Time])) - [Wrap Up Time]

Change "s" to "h" if in hours, "n" if in minutes.
--
KARL DEWEY
Build a little - Test a little


"Whitney" wrote:

Sorry for the confusion:

Total Shift Time (Date/Time)
Wrap Up Time (Number)

"format/calculation/manipulation" what can you suggest?



"KARL DEWEY" wrote:

Format does not matter. What matters is the datatype of the field it is
stored in. Are they the same or compatible?
A datetime fields can be subtracted from a datetime field no mattter the
format they are displayed in. But if one is datetime and the other is text
you must do some format/calculation/manipulation.

the other provides the data in standard number format.
Numbers may look the same but be stored in different ways - integer, single,
double, currency.
--
KARL DEWEY
Build a little - Test a little


"Whitney" wrote:

I need to subtract a field sum from another, but they are in different
formats. How can I convert one of themor get one to recognize the other?
For example:
Total Shift Time (hh:nn:ss)
Wrap Up Time (standard)

Need to do the following expression:
[Total Shift Time]-[Wrap Up Time]

The problem is I'm pulling the data from two different sources, one provdies
it in hh:nn:ss format, and the other provides the data in standard number
format.

It doesn't appear to be working.

I guess what I really need to do is convert Wrap Up Time for a Number to
date time data type to mirror the other metrics. I have several others that
I'll use for the full equation and the are all in date/time data type.

Currently I run a create table query for the Wrap Up Time. Then I use that
data to run another query combining the Total Shift Time and Wrap Up Time,
plus other metrics. Is there a way to change the data type in the table that
is created or the second query so that it reflects the correct total hours,
mins, and seconds as the other metrics do?

"KARL DEWEY" wrote:

A datetime field store a 'point in time' and not an 'amount of time' as you
are using it.
Try this if you have seconds in [Wrap Up Time] ---
DateDiff("s",0,[Total Shift Time]-Int([Total Shift Time])) - [Wrap Up Time]

Change "s" to "h" if in hours, "n" if in minutes.
--
KARL DEWEY
Build a little - Test a little


"Whitney" wrote:

Sorry for the confusion:

Total Shift Time (Date/Time)
Wrap Up Time (Number)

"format/calculation/manipulation" what can you suggest?



"KARL DEWEY" wrote:

Format does not matter. What matters is the datatype of the field it is
stored in. Are they the same or compatible?
A datetime fields can be subtracted from a datetime field no mattter the
format they are displayed in. But if one is datetime and the other is text
you must do some format/calculation/manipulation.

the other provides the data in standard number format.
Numbers may look the same but be stored in different ways - integer, single,
double, currency.
--
KARL DEWEY
Build a little - Test a little


"Whitney" wrote:

I need to subtract a field sum from another, but they are in different
formats. How can I convert one of themor get one to recognize the other?
For example:
Total Shift Time (hh:nn:ss)
Wrap Up Time (standard)

Need to do the following expression:
[Total Shift Time]-[Wrap Up Time]

The problem is I'm pulling the data from two different sources, one provdies
it in hh:nn:ss format, and the other provides the data in standard number
format.