difference between times returns a value

I have a table/form which i have 2 columns/fields which time is entered. 
"DRSR Time" and "Gate Time". 
What i'm trying to do is return the value 'Hit' (if the difference between 
the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins), 
in a 3rd column/field called "adhearance".

Any help would be great,

Thanks


 
0
Utf
5/5/2010 9:33:01 AM
access 16762 articles. 3 followers. Follow

7 Replies
993 Views

Similar Articles

[PageSpeed] 19

hi Charno,

On 05.05.2010 11:33, Charno wrote:
> I have a table/form which i have 2 columns/fields which time is entered.
> "DRSR Time" and "Gate Time".
> What i'm trying to do is return the value 'Hit' (if the difference between
> the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins),
> in a 3rd column/field called "adhearance".
Use DateDiff() in a query to calculate this value when needed:

   IIf(DateDiff("n", [DRSR Time], [Gate Time]) > 60, "Miss", "Hit")

E.g.:

   http://www.techonthenet.com/access/functions/date/datediff.php


mfG
--> stefan <--
0
Stefan
5/5/2010 9:43:43 AM
Set the control source property of your adhearance field to
=IIf(DateDiff("n",[DRSR Time],[Gate Time])<=60,"Hit","Miss")

"Charno" wrote:

> I have a table/form which i have 2 columns/fields which time is entered. 
> "DRSR Time" and "Gate Time". 
> What i'm trying to do is return the value 'Hit' (if the difference between 
> the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins), 
> in a 3rd column/field called "adhearance".
> 
> Any help would be great,
> 
> Thanks
> 
> 
>  
0
Utf
5/5/2010 10:12:01 AM
I know its a bit of an Access sin, but could the value be returned to the 
table?

"RonaldoOneNil" wrote:

> Set the control source property of your adhearance field to
> =IIf(DateDiff("n",[DRSR Time],[Gate Time])<=60,"Hit","Miss")
> 
> "Charno" wrote:
> 
> > I have a table/form which i have 2 columns/fields which time is entered. 
> > "DRSR Time" and "Gate Time". 
> > What i'm trying to do is return the value 'Hit' (if the difference between 
> > the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins), 
> > in a 3rd column/field called "adhearance".
> > 
> > Any help would be great,
> > 
> > Thanks
> > 
> > 
> >  
0
Utf
5/5/2010 10:24:01 AM
hi Charno,

On 05.05.2010 12:24, Charno wrote:
> I know its a bit of an Access sin, but could the value be returned to the
> table?
Yes, but this makes would allow you to get inconsistent data. Consider 
that you have entered time date, inserted this miss/hit flag and the 
changed the time data without recalculating it.

Thus you normally use this kind of calculation either in query or in a 
field directly.

So you don't need to store it.


mfG
--> stefan <--

0
Stefan
5/5/2010 11:11:07 AM
Hi

When you speak of columns/fields it sounds to me as is you are working on 
the table itself.  The table is just there hold data and a few other (very 
few) things.
You really should not be working on it.

If you to you can create a quesry based on the table and it will look a 
little like the table.
You can then add a caculated column using the formual that Stefan and 
Ronaldo gave you.
Don't store the results of a calculation - for lots of reasons.  You can get 
the results whenever you want.  You can us the result with other 
calculations, you can print it in a report, etc, etc.  But the main thing is 
that if the base of the calculation ever changes then the result will still 
be correct.
OK there are a (very) few time when you should save the results - I do this 
for a currency conversion calculation as I don't save the base (the amounts 
and rates) as I am only interested in the result.  But for almost all 
calculation you really should not store the result.

Hope this helps


-- 
Wayne
Manchester, England.



"Charno" wrote:

> I know its a bit of an Access sin, but could the value be returned to the 
> table?
> 
> "RonaldoOneNil" wrote:
> 
> > Set the control source property of your adhearance field to
> > =IIf(DateDiff("n",[DRSR Time],[Gate Time])<=60,"Hit","Miss")
> > 
> > "Charno" wrote:
> > 
> > > I have a table/form which i have 2 columns/fields which time is entered. 
> > > "DRSR Time" and "Gate Time". 
> > > What i'm trying to do is return the value 'Hit' (if the difference between 
> > > the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins), 
> > > in a 3rd column/field called "adhearance".
> > > 
> > > Any help would be great,
> > > 
> > > Thanks
> > > 
> > > 
> > >  
0
Utf
5/5/2010 11:18:01 AM
Thanks Wayne, 
I kind of thought that i shouldn't be working on the table, but i thought i 
would ask the question just incase i was missing something.
Using the formula's i was given i created a query and then added it as a 
subform on my form. 

Thanks to everyone for the help, much appreciated :)

Charno

"Wayne-I-M" wrote:

> Hi
> 
> When you speak of columns/fields it sounds to me as is you are working on 
> the table itself.  The table is just there hold data and a few other (very 
> few) things.
> You really should not be working on it.
> 
> If you to you can create a quesry based on the table and it will look a 
> little like the table.
> You can then add a caculated column using the formual that Stefan and 
> Ronaldo gave you.
> Don't store the results of a calculation - for lots of reasons.  You can get 
> the results whenever you want.  You can us the result with other 
> calculations, you can print it in a report, etc, etc.  But the main thing is 
> that if the base of the calculation ever changes then the result will still 
> be correct.
> OK there are a (very) few time when you should save the results - I do this 
> for a currency conversion calculation as I don't save the base (the amounts 
> and rates) as I am only interested in the result.  But for almost all 
> calculation you really should not store the result.
> 
> Hope this helps
> 
> 
> -- 
> Wayne
> Manchester, England.
> 
> 
> 
> "Charno" wrote:
> 
> > I know its a bit of an Access sin, but could the value be returned to the 
> > table?
> > 
> > "RonaldoOneNil" wrote:
> > 
> > > Set the control source property of your adhearance field to
> > > =IIf(DateDiff("n",[DRSR Time],[Gate Time])<=60,"Hit","Miss")
> > > 
> > > "Charno" wrote:
> > > 
> > > > I have a table/form which i have 2 columns/fields which time is entered. 
> > > > "DRSR Time" and "Gate Time". 
> > > > What i'm trying to do is return the value 'Hit' (if the difference between 
> > > > the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins), 
> > > > in a 3rd column/field called "adhearance".
> > > > 
> > > > Any help would be great,
> > > > 
> > > > Thanks
> > > > 
> > > > 
> > > >  
0
Utf
5/5/2010 12:08:01 PM
On Wed, 5 May 2010 03:24:01 -0700, Charno <Charno@discussions.microsoft.com>
wrote:

>I know its a bit of an Access sin, but could the value be returned to the 
>table?

Storing derived data such as this in your table accomplishes 
three things: it wastes disk space; it wastes time (almost 
any calculation will be MUCH faster than a disk fetch); and 
most importantly, it risks data corruption. If one of the 
underlying fields is subsequently edited, you will have data 
in your table WHICH IS WRONG, and no automatic way to detect 
that fact.

Just redo the calculation whenever you need it, either as a 
calculated field in a Query or in the control source of a Form or a Report
textbox.
-- 

             John W. Vinson [MVP]
0
John
5/5/2010 4:38:10 PM
Reply:

Similar Artilces:

Convert a number to a time
I have the following number 232.5 which is 232 hours and 0.5 of an hour. I want to convert this to 232 hours and 30 minutes or 232:30. How can i do this? I know i need to multiply the decimal by 60, but how can i perform a calculation on just the decimal part of a number and not the whole number? Depending what you wish to do excel stores times as well as dates as numbers, with 1 being a full day. An option is to divide everything by 24 and use a custom format of dd hh:mm although this will express the hours as days and hours It depends what you want to do really -- Dav --------------...

Pulling data daily from a differently named report everyday
Hello, I have a spreadsheet where I use formulas to pull data from multiple reports on a shared server everyday. What I do is to save those reports under a certain name everyday since the formulas have to have the static report name in them to pull data. (For example, A1 may pull data from c:\reportfolder\reportA, B1 may pull from c: \reportfolder2\reportB, etc). In these report folders our IT group runs a new report in it everyday but of course they change the name of the report every day (to reflect the date) . . so in the reportfolder there will be "reportA-3-12-2009", and "...

Can't receive after an amount of time
Windows XP Professional Roadrunner Connection Office XP Professional PC-Cillin Microsoft ActiveSync for Pocket PC (I sync calendar/tasks/notes only) I run Outlook 2003 and have several email addresses I check. Everything works but after awhile I get the following error from every email address (most from different companies): Task '*** - Receiving' reported error (0x80042108) : 'Outlook is unable to connect to your incoming (POP3) e-mail server. If you continue to receive this message, contact your server administrator or Internet service provider (ISP).' After res...

Different margins on different pages
I am setting up a template for letters at work. The first page of our letterhead has one ste of margins and all subsequent pages will have a different sent of margins. I know that section breaks can be used, but if staff copy and paste text from existing letters into this new template, the section break moves. Is there any way of locking the section break to the page? so the pasted text goes over the top of it? Hi Lilly80, You could setup your document with a 'different first page' layout. That allows the first page to have a different set of margins to the rest o...

Page Load/Click Event Timing Issue
I have a webform that alters it's content depending on whether or not the current user is logged in. It does this in the Page_Load event. My master page has a logout button. If the user clicks this button, it fires the Page_Load event in my content page, the Page_Load event in my master page, and finally the Logout_Click event for my logout button. This means that my code that alters the page (depending on whether or not the current user is logged in) fires before the code that logs the user out. This results in serving a page for a logged-in user when they are now logged ...

Different values for error bars in series
In Excel 2007, how do you put in different error bar values for each data point in a series? So far I am only able to put in one value for all in my bar chart. Hi, See Jon's blog on the subject. http://peltiertech.com/WordPress/error-bars-in-excel-2007/#comments Cheers Andy On 06/04/2010 15:52, wdwind1 wrote: > In Excel 2007, how do you put in different error bar values for each data > point in a series? So far I am only able to put in one value for all in my > bar chart. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

A Different CRM for GP
Hello, We are pondering the idea of implementing MS Dynamics CRM. However, I am curious whether anyone else has had success in integrating a different CRM product with GP 10.0. It's not that we don't like the MS product; I just like to ensure I have covered all bases. Our partner does not have any other recommendations as they have not worked with any other CRM applications. Any information would be greatly apprecaited. Thank you, -- Jessie GoldMine is a good product, very mature, and easier to maintain table structures. There are links to GP but I cannot remember the fir...

2 users access calendar online with different rights
hi user A wants to give user B permission to see, add and edit entries in calendar online http://www.exchangedomain.ch/usersname/kalender/ user B is able to see, but not to add or edit entries at the moment in outlook itself its working. user B can open the folder calendar of user A and make entries and change entries what have to be done that this is possible also with web access exchange ? thankx mike schwarz On Thu, 26 Oct 2006 14:09:29 +0200, "Mike Schwarz" <ctek@ctek.ch> wrote: >hi > >user A wants to give user B permission to see, add and edit entries in &...

Same column, different cell width at different row
I have tried inserting a break and split, didn't work. I just want to separate the top half of a page with the bottom half so I can apply different cell width on the same column. Or how would I be able to do this? Same column but different cell width. Thanks! Glenn You can't do that. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Glenn Chung" <GlennChung@discussions.microsoft.com> wrote in message news:20746997-9D99-4684-A352-120370D27036@microsoft.com... >I have tried inserting a break and split, di...

Named range(s) of non-adjacent cells return #VALUE! error in array formulas
I have ranges made up of non-adjacent cells from one worksheet and I get a #VALUE! error when I try to do any conditional count or sum calculation with an array formula on either or both of them. Each range is a selection of 32 non-adjacent cells from a single column. The cells contain array formulas that return percentages. Example of range values I8: 5.9% I11: 12.1% I14: 22.3% I17: 0.0% I was able to get values returned from simple functions like Max and Min but the following example gives me the #VALUE! error: {=SUM(IF((Rng>0),1,0))} =COUNTIF(Rng,"<.0125") Any wisdom ...

Macro to coppy cells to certain rows depending on value in cell
I want his macro to after it have inserted the colmns and added the formula to 1. copy range A1 to E1 to every row where the word "Header" is in colmn F. 2. Then copy paste the whole sheet as values. 3. Then the range now standing left of "header" must be copied to the empy cells beneath each heading. For example a b c d e f 1)12/12/2005 F001 SAO3 1 CCE Header 2) Detail ...

Convert String to Numeric Values
I have data string that I must convert to numeric values. Ex: 00100200300400500600700800901000110015016 How do I get the above data string example to return the numeric values that are three digit integers. How do I get the number 16 for example? Any assistance is appreciated -- JenISCM Try this: Public Sub MySplit(txtIn As String) ' copy/paste to a standard module ' input from debug window: ' call mysplit("00100200300400500600700800901000110015016") Dim i As Integer Dim n As Integer i = 1 For n = 1 To CInt(Len(txtIn) / 3) Debug.Print n & " - " ...

Fixed Assets
When using the Fixed Asset module, I am finding some small differences when compared to the Depreciation calculated by the External Accountants for tax purposes. Client would like to match exactly to the amounts calculated by external accounts. What is the best way to fix this issue? thanks, -- Patti Need more info Patti. What are the depreciation methods/settings that are creating the differences? Does this occur on every asset or just some? Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your gptip42today at www.gp2themax.blogs...

Dates Difference
Hi, I need to get the difference between 2 dates (in number of days), how do i get that in vc++? I have used the DateDiff function in asp but couldn't find anything like that. Also, how can i convert string to a date? thanks in advance karan >I need to get the difference between 2 dates (in number of days), how >do i get that in vc++? Karan, Convert (if the dates aren't already in this format) the dates/times to FILETIME and subtract the values. >Also, how can i convert string to a date? Try COleDateTime::ParseDateTime or VarDateFromStr. Dave -- MVP VC++ FAQ: http://ww...

2006 Sick time accrual problem
My users are experiancing problems with Sick time accrual for Salaried employees. The hours are correct, but the dollers are not. The hourly employees are working fine, only salaried are wrong. This is our first run of the 2006 pay year, and we have applied all year end and 2006 updates. Any ideas of what could be wrong would be appreciated! Thanks! ...

Want to display value and difference on one chart
I am trying to create a chart that displays x and 10-x on the same chart. I want the chart to show a meaured value and the difference of that value to 10 in a different color. On Wed, 6 Aug 2008, in microsoft.public.excel.charting, JMH <JMH@discussions.microsoft.com> said: >I am trying to create a chart that displays x and 10-x on the same chart. I >want the chart to show a meaured value and the difference of that value to 10 >in a different color. Use your spreadsheet to calculate the values, then use the chart to graph them. Never try to use a chart for calculation. ...

Sending from 2 different computers
I have Outlook 2003 on my desktop at home and 2002 on my laptop while traveling. My server is Comcast. I would strongly prefer to use Outlook (rather than Comcast's webmail) on both computers. I have configured Outlook on both computers to receive email. I can also leave a copy on the server so it can be downloaded again later on the other computer, assuring that I always have copies of all emails no matter which computer I'm using. Problem is I can't send from my laptop. I've tried Comcast's instructions for configuring Outlook 2002 for use while traveling, but it doesn&...

Different shape width in different views in Excel 2007??
In Excel 2007, I create a shape in "Normal" view and set its width to any value (e.g. to 15cm). Now I change to "Page Layout" view and guess what... the width now shows up as something more than 16cm??! (The width property as points in VBA changes accordingly.) Switching to "Page Break View" will result in one more different width value. Is this "just" a big bug in Excel 2007 or did I miss something?? I know that the selected default printer or the chosen zoom factor may have an influence on the shown size of a shape, but these discrepancies are ...

appointment prints different time
Hi, My client uses a french CRM with date format dd/mm/yyyy. when he creates an appointment with the SFO client from e.g. 11 o'clock until 16 o'clock it is saved like correctly. but when he prints it out, the starttime is 8 o'clock and end time 13 o'clock. it is like that for every appointment. it always prints out 3 hours earlier. Anybody had this too? Thanx Cypress, You can check if both the server and the user are in the same time zone. Server: Open Control Panel, Date and Time, Timezone Client: On the laptop open Control Panel, Date and Time, Timezone Open CRM vi...

Different Columns on different pages
Hi - think this will be easily resolved but I am stuck! In publisher 2000 how do you create a multipaged, two page spread that has different columns on some pages. I keep bringing myself back to "ignore background" and trying to use "arrange" - "layout Guides" - but this isnt right. Am I thinking too deep? Is the whole point that the layout guides are simply guides and stay the same thorugh the whole document regardless.... you just use ruler guides to make the changes through the different pages? Any idea's? Thanks Julia ...

Difference 05-21-04
What is the difference between CRM standard and professional? -Johnny Johnny wrote: > What is the difference between CRM standard and professional? > > -Johnny Does it have anything to do with Outlook integration? -- - I am Johnny! Your Internet penpal. I have a scooter, how about you? "Johnny" <alphascooter-verizon@yahoo.com> wrote in message news:evfBJNwPEHA.3708@TK2MSFTNGP10.phx.gbl... > Johnny wrote: > > > What is the difference between CRM standard and professional? > > > > -Johnny > Does it have anything to do with Outlook inte...

divs are different in different browsers
I just ran accross something strange. I tried to used "Div Tags" instead of tables. So it looks something liket his: <div> <div></div><div></div> <div></div><div></div> <div></div><div></div> </div> It looks great in Internet Explorer, but firefox and chrome destroy it. Can anyone let me know on what to look for within the css or layout of the divs to make it work proper. Or in this case...is it just best to go back to tables within 1 div tag. Like this: <div> ...

Compare and Merge Two different CRM Instances
We have two different CRM environments that we want to merge into one. My first step was to try and compare the different instances to see what the different were between them. I exported all of the customizations from each and performed a diff. Yuck. Since the customizations don't export the elements in the same order it looks like their are over 7,000 differences between the two files. Reality says that at best there may be 50-100. Is there any way to get these to output in the same order so that I can get a more realistic diff between them? The only other thought I have at this po...

IHTMLAreaElement and coords parameter returns incorrect value
Hi, I am having problems with retrieving the coords parameters for image maps of type "POLY". For some reason, IHTMLAreaElement only retrieves the first 8 values of the 'coords' parameter even though there can be more since the shape is a polygon. I thought I try to be clever by using get_outerHTML to retrieve the tag string and parse those values myself, but it seems that MSHTML only recognises the first 8 values. Any ideas? or maybe a newsgroup that might know more? Andrew ...

searching different files macro
I need to search for a certain asset number in the asset column in man different excel files. If the asset number does exist it should copy paste the whole row into a new sheet and build on that sheet . all th excel files have the same columns but different data. i am looking for a macro or any other way to automate this thank yo -- Message posted from http://www.ExcelForum.com Hi see Excel.programming P.S.: please don't multipost -- Regards Frank Kabel Frankfurt, Germany > I need to search for a certain asset number in the asset column in > many different excel files. If ...