Writing an expression using fields with different formats

  • Thread starter Thread starter Whitney
  • Start date Start date
W

Whitney

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.
Numbers may look the same but be stored in different ways - integer, single,
double, currency.
--
KARL DEWEY
Build a little - Test a little


Whitney said:
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.
Click to expand...
 
Sorry for the confusion:

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

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



KARL DEWEY said:
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.
Numbers may look the same but be stored in different ways - integer, single,
double, currency.
--
KARL DEWEY
Build a little - Test a little


Whitney said:
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.
Click to expand...
Click to expand...
 
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 said:
Sorry for the confusion:

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

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



KARL DEWEY said:
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.
Click to expand...
Numbers may look the same but be stored in different ways - integer, single,
double, currency.
--
KARL DEWEY
Build a little - Test a little


Whitney said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Sorry for the confusion:

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

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



KARL DEWEY said:
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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top