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.
0
Utf
1/8/2008 6:44:08 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
583 Views

Similar Articles

[PageSpeed] 24

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.
0
Utf
1/8/2008 7:16:01 PM
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.
0
Utf
1/8/2008 7:37:01 PM
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.
0
Utf
1/8/2008 8:59:02 PM
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.
0
Utf
1/9/2008 12:52:01 AM
Reply:

Similar Artilces:

use macro to change dates
I have never used macros before but am trying to convert dates using excel 2003. The dates are in the format of +1.123.456.1234 and I would like to change that to 123-456-1234. There are also some listed as 123.456.1234 and would like them to be the same as the first ones, that is, 123-456-1234. I tried using the "start recording" method and "stop recording" but the cursor just jumps back to the original cell when I try to use the recorded macro. I have 2000 cells to change. Anybody have a suggestion on how to change these? I looked at the VBE, but don't kno...

Conditional Comment using Cell value as part of it
What I'm trying to do is the following: if cell(a1) is different from 0(zero) then, cell(b1) should have a comment that say, the value of cell(c1) is the payment, next line the value of cell(a1) was refund, next line Total for today = cell(c1)-cell(a1) Example a1 = $10.00 c1 = $30.00 B1 (comment): $30.00 is the payment (c1) $10.00 was refund (a1) Total for today: $20.00 (c1-a1) can anyone help me on that? thanks in advance ...

What Is The Difference Between i++ and ++i?
I use MFC. I would like to know the difference of ++ if it is used preceeding the variable name and if it is used after the variable name? int i = 5; int j = 10; i++; ++j; Thank you. "Landon" <Landon@discussions.microsoft.com> wrote in message news:E5F8D747-65A4-> I use MFC. I would like to know the difference of ++ if it is used preceeding > the variable name and if it is used after the variable name? > > int i = 5; > int j = 10; > i++; > ++j; In your example they both just get one added to them. The difference is in when it is being used. For exampl...

Why is .NET 2.0 required to deploy an app using VC2005 C++ Setup project?
I just created a setup project for my mfc app. I noticed that in the prerequisites section the .NET 2.0 is checked by default. My app does not need the framework (at least this is what I think). If I clear the checkbox, build the project, and then try to deploy it on a machine with ..NET 1.1 the installer fails with a message complaining about missing .NET 2.0. I'm not sure if this the installer that needs the framework or my app. Are there any mfc8 dlls dependant on the framework? Thanks Hi, > I just created a setup project for my mfc app. I noticed that in the > p...

Pivot table chart problem, How to show different subtotal levels
Hi, Using Excel 2003, I have a pivot table using external data via ODBC. I have a chart based on my pivot table. The table and chart are working correctly. The PTcontains two levels of sales subtotals 1st level is by county 2nd level is by state. My client is telling me that the data in the chart is too detailed. He wants to have the both levels of subtotals in the PT, but only the county subtotals in the chart. If I delete the county sales subtotals from the chart, they are also deleted from the PT. Is it possible for the chart to be based on the PT and not show ...

Insert fields dynamically into database from a staging table
Hi all, In our lab when ever a test is performed results are stored in test_results table ( 10 records at maximum) and with click of a button these results should be loaded to database, but I have any issue here, each time the test_result table will have different field names ( one time it will have test1 , test2 , test3 with labid, next time it might have test1, test4, test5 and labid) so how can I load these fields from test_results table into database auomatically. Datebase has all these fields so how can I map the test_results tables fields to database fields to run my dynamic inse...

Format cell to display numbers instaed of ###'s
Not sure how to querry this in the KB.... but I want to be able to have a number such as 4 1/8 in a cell that is set at width less than what is required to display it properly. Same with a date/time entry. In cells where I'm entering text, the text will "spill" over if the adjoining cells are empty of data. No so with number's. How do I change it so it will display as 4 1/8 without widening the cell? If I place an apostrophe ' in front of the number, or format the cell as plain text it will work, but I really need to retain the numeric value. Thanks, David... ...

Outlook Express Password #5
Last week I placed a password on my outlook express, so that no one could access it. I forget it on Monday morning, as the news of my mother's death on sunday. Please help? There is a product called snadboy which you can install. Once installed, you take the snadboy cursor over the ********* and it will reveal the paessword. BTW, it is freeware I believe. DJF >-----Original Message----- >Last week I placed a password on my outlook express, so that no one could access it. I forget it on Monday morning, as the news of my mother's death on sunday. Please help? >. > ...

Cell Formating Problems
This is what I do: I open up a brand new worksheet. I format cell A1 to text. Then I enter the number 1 into it. Then I format the cell to Numeric and the 1 in the cell does not change. it stays as (left justified 1. If I delete the 1 and type a 1 into that cell and hit Enter, it become 1.00 (a number). Whats happening here??? Why doesn't it convert the 1 to a 1.00 when format the cell to numeric? Why do I have to re-enter the number -- Message posted from http://www.ExcelForum.com brain It was originally entered as Text. Simply re-formatting doesn't change its underlying ...

format end notes to appear at the chapter, not end of document?
I need my end notes to appear at the end of each chapter. Word puts them at the end of the document. Can I do this manualy, or is there a shortcut format I can use? Right-click an endnote and choose Note Options from the context menu. At "Location," choose "End of document." Make sure that "Apply changes to" reads "Whole document"; then click Apply. -- Stefan Blom Microsoft Word MVP "Liz" <Liz@discussions.microsoft.com> wrote in message news:B9569343-3363-45DD-A61A-9990C9689443@microsoft.com... >I need my e...

empty messages in Outlook Express
Anyone got any ideas on this. Today all my email messages appeared to be blank, although attachement, headers and file size remain. To complicate things further, I can no longer recv or send email, I get the following message. Any ideas? Your server has unexpectedly terminated the connection. Possible causes for this include server problems, network problems, or a long period of inactivity. Account: 'Annon BT account', Server: 'mail.btopenworld.com', Protocol: POP3, Server Response: '+OK 1487 octets', Port: 110, Secure (SSL): No, Error Number: 0x800CCC0F And...

How to use Find (Ctrl-F) to find non-alpha
Is there some way to search a sheet for anything BUT a-z or A-Z? tx ;-) Finding numerals or a mixture of numbers and letters (and other characters) works ok for me. What are you having trouble with? On 10/22/2010 16:31, Heather Mills wrote: > Is there some way to search a sheet for anything BUT a-z or A-Z? > > tx ;-) -- Dave Peterson On Fri, 22 Oct 2010 17:54:28 -0500, Dave Peterson <petersod@XSPAMverizon.net> wrote: >Finding numerals or a mixture of numbers and letters (and other characters) >works ok for me. > >What are you having trouble with? > >...

VC++ 2005
I am attempting to compile a project in Visual Studio 2005 which compiles and links properly in VC++ 6.0. It seems to have problems with C++ i/o functions. Here is the error I get: g:\src\analyzer\digitdialpages.h(136) : error C2146: syntax error : missing ';' before identifier 'm_InFile' Line 136: ifstream m_InFile; The statment: include <fstream> is at the top of the source file. Some assistance would be helpful. We didn't have any problems with using ifstream or ofstream in VC++ 6.0. Thanks. On Tue, 12 Dec 2006 18:22:47 -0500, "Raj Kulkarni...

How do I create custom border using peoples names?
There are many it could be done. I'd set up text boxes and go from there. -- JoAnn Paules MVP Microsoft [Publisher] "Val" <Val@discussions.microsoft.com> wrote in message news:BFF16BB3-4C90-4E05-B0AA-303874A445AA@microsoft.com... > In addition to JoAnn's reply, you could use WordArt as well. WordArt allows you the opportunity to rotate, flip, arrange etc. and resize as well as save as an image. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. &...

Outlook Express #294
I created folders for emails I wanted to save. Decided to dispense with several folders & compact emails into just a few folders. I inadvertently deleted certain folders that I wanted to keep. I also deleted them from the recycle bin. Can I retrieve these folders from anywhere behind the scenes. I've done a restore that doesn't work for this particular problem. This is the Microsoft Office Student & Teacher Edition 2003. TKS proad <proad@discussions.microsoft.com> wrote: > I created folders for emails I wanted to save. Decided to dispense > with sever...

Formatted Text in Autocorrect, Word 2008 for Mac
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I want to set up an autocorrect entry where I replace something with =formatted= as opposed to =plain= text. Specifically, I want to replace "qqq" with "QQQQQ" formatted in 14 pt Britannic Bold. However, I cnanot change the signal button from "plain' to "formatted" text. is there a way to do this? Type the word you want to use to replace "qqq" and format it inside your document. Select it, then click on Tools>AutoCorrect. You can now click the "formatted text&...

manipulating number formats
Hi If I type in $1,238,000 I want the cell to display $1,000. Similarly if I type in $15,965,945 I want the cell to display $16,000. Can I do this without using the round function? Thanks I do not believe you can if you are using ws functions. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Billy" <anonymous@discussions.microsoft.com> wrote in message news:081001c3af96$c1e9dc30$a001280a@phx.gbl... > Hi > > If I type in $1,238,000 I want the cell to display $1,000. > Similarly if I type in $15,965,945 I want the cell to > display $16,00...

Issue writing IF OR Error Formula Types
Having trouble putting a formala together. I have the following formula which works: Senario : $d$d = either weight or unit or unknown (of an ingredient) e8= is a quantity number ( either in grams or units, o an ingredient. eg 100 or 1) Raw data is in nutrition!$b$6:$g$70 The following formula works so far: f8 ( which is the no. o calories)=if($d$d="weight",e8/100,e8)*vlookup(c8,nutrition!$b$6:$g$70,3,false) But I also want to add to it that If e8(quantity)=0 or blank or invalid then "qty?" should appear in th f8 cell And not sure what to do to avoid an error in f8...

how to remove non commercial use in the title bar in office
how to remove non commercial use in the title bar in office 2007 Buy an edition that is not for non-commercial use only. You own the Home and Student edition. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Mario" <Mario@discussions.microsoft.com> wrote in message news:0E14F850-0B65-4093-926F-B680E1C7D6B1@microsoft.com... > how to remove non commercial use in the title bar in office 2007 ...

Expression must evaluate to a node-set
I am using xpath within a BizTalk 2006 orchestration. I get the following error "Expression must evaluate to a node-set". My xml: <EmailListResponse xmlns="http://BTTest"> <PersonContact EMailContactValue="test1@mail.com" /> <PersonContact EMailContactValue="test2@mail.com" /> </EmailListResponse> My xpath expression which generates the error message: varNoOfEmailAddresses = (System.Int32)xpath(msgEmailListRes, "count(/ *[local-name()='EmailListResponse']/*[local-name()='PersonContact']/ *[[local-name...

vlookup cant be used
I need a way to transfer data. I have one sheet that contains all information. All of this data needs to be transferred to a template ready for print. I have contemplated using a vlookup to quicken the method, although the data sheet will be moving too and from computers, and so the vlookup will not work. Is there any code that might possibly help me, i.e. a code that looks for the datasheet and does some sort of vlookup after it has located the file? Or should this problem be tackled using macros, although i imagine that this will have the same effect. How "ready for print"? Are...

Auto Fill Fields
I have a database of contacts (names, addresses, phone numbers, emails). I have created a table with this info already filled out (imported from Excel). All I want to do is create a form with a single drop-down list where, when the last name is selected, all the other fields automatically populate with that person's correct information. I have tried DLookup but that seems too cumbersome and doesn't work unless I go to design view and back to form view. Is there not a simple function in Access that lets me do what I need to without having to write code. I am NOT a code writer,...

Upgrading Multiple Companies at Different Times
I have multiple companies currently in my GP 8.2 installation on a single server. Of course these companies each share the DYNAMICS database. I am plotting out the upgrade process to 9.0 and would like to upgrade one company at a time, as one of them is ready to go, while the other will require more work due to customizations. Would there be any issue with upgrading CompanyA to 9.0 on a new server, and then a month or two down the line, upgrading CompanyB to that same server? Would CompanyB be able to slide right in to using a DYNAMICS database that it had not used for a couple of...

problem with adding column, formatting
I saved a workbook in quatro pro 8 to excel format and then opened it in excel (ms office 2000). some of the columns will not permit reformatting - changing the date format or changing from currency to accountant number format. I can not add blank columns between columns containing data. A window pops up indicating that Excel will not insert a column as I will lose data. I have attempted to follow instructions in the documentation and in the help screen but can not insert blank columns. In the transfer process does excel end up with locked column and row structure and/or formatting of so...

format date in excel
hi, Can you format a date in a formula to recognise the date only? i have a column with the day number only and need to calculate the figure directly to the right as this day number reaches todays date. example: day cost to date 12 £60.00 if todays date was 11th september, to date would be empty but if todays date was 12th september, to date would have the figure £60.00 in it. thanks in advance, N.S. in the To Date column - assume it is C2 =(if(day(today())<=A2,B2,"") "Nigel" wrote: > hi, > > Can you ...