If statements with times - possible issue with the formatting of t

Hello,

I have a form with a start and a finish time (2 text boxs), and another text 
box call WAITING_TIME that calculates the difference. I'm trying to execute 
some code if that waiting time is less than one minute.

I've tried to use this code in the After Update event of the finish time:

If DateDiff("n", Me.START_TIME, Me.FINISH_TIME) <= #12:01:00 AM# Then
Do While Len(Me.OBSERVATIONS.Value) = 0
MsgBox "The processing time is unusually short. Please enter notes 
exaplining the reason ", , "Short Process Time"
Me.OBSERVATIONS.SetFocus
Loop
End If

I've tried changing the #12:00:01 AM# with a "1" (since datediff calculates 
the difference in minutes) 

I have also tried using the WAITING_TIME value instead of the datediff 
function:

If Me.WAITING_TIME <= #12:01:00 AM# Then...

Also tried 

If Me.WAITING_TIME <=  0.000694444444444444 Then...
(0.000694444444444444 is the numeric equivalent of 1 minute, at least in 
Excel)

Does anybody have an idea of why this If statement is not triggered when 
that wait time is less than one minute?

Thanks!

0
Utf
11/13/2007 5:19:03 PM
access.formscoding 7493 articles. 0 followers. Follow

6 Replies
741 Views

Similar Articles

[PageSpeed] 54

>>I've tried changing the #12:00:01 AM# with a "1" (since datediff calculates 
the difference in minutes) 
Omit the quotes from the 1.   Quotes make it text and not a number.

-- 
KARL DEWEY
Build a little - Test a little


"Alejandro" wrote:

> Hello,
> 
> I have a form with a start and a finish time (2 text boxs), and another text 
> box call WAITING_TIME that calculates the difference. I'm trying to execute 
> some code if that waiting time is less than one minute.
> 
> I've tried to use this code in the After Update event of the finish time:
> 
> If DateDiff("n", Me.START_TIME, Me.FINISH_TIME) <= #12:01:00 AM# Then
> Do While Len(Me.OBSERVATIONS.Value) = 0
> MsgBox "The processing time is unusually short. Please enter notes 
> exaplining the reason ", , "Short Process Time"
> Me.OBSERVATIONS.SetFocus
> Loop
> End If
> 
> I've tried changing the #12:00:01 AM# with a "1" (since datediff calculates 
> the difference in minutes) 
> 
> I have also tried using the WAITING_TIME value instead of the datediff 
> function:
> 
> If Me.WAITING_TIME <= #12:01:00 AM# Then...
> 
> Also tried 
> 
> If Me.WAITING_TIME <=  0.000694444444444444 Then...
> (0.000694444444444444 is the numeric equivalent of 1 minute, at least in 
> Excel)
> 
> Does anybody have an idea of why this If statement is not triggered when 
> that wait time is less than one minute?
> 
> Thanks!
> 
0
Utf
11/13/2007 6:19:03 PM
Karl, the quotes were only in my message. I didn't put them in the code. 
Something else is creating the problem.

"KARL DEWEY" wrote:

> >>I've tried changing the #12:00:01 AM# with a "1" (since datediff calculates 
> the difference in minutes) 
> Omit the quotes from the 1.   Quotes make it text and not a number.
> 
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Alejandro" wrote:
> 
> > Hello,
> > 
> > I have a form with a start and a finish time (2 text boxs), and another text 
> > box call WAITING_TIME that calculates the difference. I'm trying to execute 
> > some code if that waiting time is less than one minute.
> > 
> > I've tried to use this code in the After Update event of the finish time:
> > 
> > If DateDiff("n", Me.START_TIME, Me.FINISH_TIME) <= #12:01:00 AM# Then
> > Do While Len(Me.OBSERVATIONS.Value) = 0
> > MsgBox "The processing time is unusually short. Please enter notes 
> > exaplining the reason ", , "Short Process Time"
> > Me.OBSERVATIONS.SetFocus
> > Loop
> > End If
> > 
> > I've tried changing the #12:00:01 AM# with a "1" (since datediff calculates 
> > the difference in minutes) 
> > 
> > I have also tried using the WAITING_TIME value instead of the datediff 
> > function:
> > 
> > If Me.WAITING_TIME <= #12:01:00 AM# Then...
> > 
> > Also tried 
> > 
> > If Me.WAITING_TIME <=  0.000694444444444444 Then...
> > (0.000694444444444444 is the numeric equivalent of 1 minute, at least in 
> > Excel)
> > 
> > Does anybody have an idea of why this If statement is not triggered when 
> > that wait time is less than one minute?
> > 
> > Thanks!
> > 
0
Utf
11/13/2007 7:26:05 PM
The DateDiff does not return a time, it returns a duration.  If you are 
trying to determine if the duration is less than one minute

If DateDiff("n", Me.START_TIME, Me.FINISH_TIME) < 60
-- 
Dave Hargis, Microsoft Access MVP


"Alejandro" wrote:

> Hello,
> 
> I have a form with a start and a finish time (2 text boxs), and another text 
> box call WAITING_TIME that calculates the difference. I'm trying to execute 
> some code if that waiting time is less than one minute.
> 
> I've tried to use this code in the After Update event of the finish time:
> 
> If DateDiff("n", Me.START_TIME, Me.FINISH_TIME) <= #12:01:00 AM# Then
> Do While Len(Me.OBSERVATIONS.Value) = 0
> MsgBox "The processing time is unusually short. Please enter notes 
> exaplining the reason ", , "Short Process Time"
> Me.OBSERVATIONS.SetFocus
> Loop
> End If
> 
> I've tried changing the #12:00:01 AM# with a "1" (since datediff calculates 
> the difference in minutes) 
> 
> I have also tried using the WAITING_TIME value instead of the datediff 
> function:
> 
> If Me.WAITING_TIME <= #12:01:00 AM# Then...
> 
> Also tried 
> 
> If Me.WAITING_TIME <=  0.000694444444444444 Then...
> (0.000694444444444444 is the numeric equivalent of 1 minute, at least in 
> Excel)
> 
> Does anybody have an idea of why this If statement is not triggered when 
> that wait time is less than one minute?
> 
> Thanks!
> 
0
Utf
11/13/2007 9:22:03 PM
I made a mistake in my previous post.  I thought I read you were returning 
seconds, but it is minutes, so <= 1 should work.  You might try changing the 
DateDiff to return seconds and use < 60.
-- 
Dave Hargis, Microsoft Access MVP


"Alejandro" wrote:

> Hello,
> 
> I have a form with a start and a finish time (2 text boxs), and another text 
> box call WAITING_TIME that calculates the difference. I'm trying to execute 
> some code if that waiting time is less than one minute.
> 
> I've tried to use this code in the After Update event of the finish time:
> 
> If DateDiff("n", Me.START_TIME, Me.FINISH_TIME) <= #12:01:00 AM# Then
> Do While Len(Me.OBSERVATIONS.Value) = 0
> MsgBox "The processing time is unusually short. Please enter notes 
> exaplining the reason ", , "Short Process Time"
> Me.OBSERVATIONS.SetFocus
> Loop
> End If
> 
> I've tried changing the #12:00:01 AM# with a "1" (since datediff calculates 
> the difference in minutes) 
> 
> I have also tried using the WAITING_TIME value instead of the datediff 
> function:
> 
> If Me.WAITING_TIME <= #12:01:00 AM# Then...
> 
> Also tried 
> 
> If Me.WAITING_TIME <=  0.000694444444444444 Then...
> (0.000694444444444444 is the numeric equivalent of 1 minute, at least in 
> Excel)
> 
> Does anybody have an idea of why this If statement is not triggered when 
> that wait time is less than one minute?
> 
> Thanks!
> 
0
Utf
11/13/2007 9:37:00 PM
Klatuu, I'm afraid that you are incorrect. "n" is the format for minutes; if 
I made the expression <60 I would trigger the code every time the difference 
in times is less than 1 hour (60 min).

However, the formula seems to be working now. I just compacted and repaired 
the database and it started working.

Thanks!

"Klatuu" wrote:

> The DateDiff does not return a time, it returns a duration.  If you are 
> trying to determine if the duration is less than one minute
> 
> If DateDiff("n", Me.START_TIME, Me.FINISH_TIME) < 60
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Alejandro" wrote:
> 
> > Hello,
> > 
> > I have a form with a start and a finish time (2 text boxs), and another text 
> > box call WAITING_TIME that calculates the difference. I'm trying to execute 
> > some code if that waiting time is less than one minute.
> > 
> > I've tried to use this code in the After Update event of the finish time:
> > 
> > If DateDiff("n", Me.START_TIME, Me.FINISH_TIME) <= #12:01:00 AM# Then
> > Do While Len(Me.OBSERVATIONS.Value) = 0
> > MsgBox "The processing time is unusually short. Please enter notes 
> > exaplining the reason ", , "Short Process Time"
> > Me.OBSERVATIONS.SetFocus
> > Loop
> > End If
> > 
> > I've tried changing the #12:00:01 AM# with a "1" (since datediff calculates 
> > the difference in minutes) 
> > 
> > I have also tried using the WAITING_TIME value instead of the datediff 
> > function:
> > 
> > If Me.WAITING_TIME <= #12:01:00 AM# Then...
> > 
> > Also tried 
> > 
> > If Me.WAITING_TIME <=  0.000694444444444444 Then...
> > (0.000694444444444444 is the numeric equivalent of 1 minute, at least in 
> > Excel)
> > 
> > Does anybody have an idea of why this If statement is not triggered when 
> > that wait time is less than one minute?
> > 
> > Thanks!
> > 
0
Utf
11/13/2007 9:58:03 PM
I know, if you read my second post, you would see that I corrected myself.
Glad you got it working.
-- 
Dave Hargis, Microsoft Access MVP


"Alejandro" wrote:

> Klatuu, I'm afraid that you are incorrect. "n" is the format for minutes; if 
> I made the expression <60 I would trigger the code every time the difference 
> in times is less than 1 hour (60 min).
> 
> However, the formula seems to be working now. I just compacted and repaired 
> the database and it started working.
> 
> Thanks!
> 
> "Klatuu" wrote:
> 
> > The DateDiff does not return a time, it returns a duration.  If you are 
> > trying to determine if the duration is less than one minute
> > 
> > If DateDiff("n", Me.START_TIME, Me.FINISH_TIME) < 60
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "Alejandro" wrote:
> > 
> > > Hello,
> > > 
> > > I have a form with a start and a finish time (2 text boxs), and another text 
> > > box call WAITING_TIME that calculates the difference. I'm trying to execute 
> > > some code if that waiting time is less than one minute.
> > > 
> > > I've tried to use this code in the After Update event of the finish time:
> > > 
> > > If DateDiff("n", Me.START_TIME, Me.FINISH_TIME) <= #12:01:00 AM# Then
> > > Do While Len(Me.OBSERVATIONS.Value) = 0
> > > MsgBox "The processing time is unusually short. Please enter notes 
> > > exaplining the reason ", , "Short Process Time"
> > > Me.OBSERVATIONS.SetFocus
> > > Loop
> > > End If
> > > 
> > > I've tried changing the #12:00:01 AM# with a "1" (since datediff calculates 
> > > the difference in minutes) 
> > > 
> > > I have also tried using the WAITING_TIME value instead of the datediff 
> > > function:
> > > 
> > > If Me.WAITING_TIME <= #12:01:00 AM# Then...
> > > 
> > > Also tried 
> > > 
> > > If Me.WAITING_TIME <=  0.000694444444444444 Then...
> > > (0.000694444444444444 is the numeric equivalent of 1 minute, at least in 
> > > Excel)
> > > 
> > > Does anybody have an idea of why this If statement is not triggered when 
> > > that wait time is less than one minute?
> > > 
> > > Thanks!
> > > 
0
Utf
11/13/2007 10:01:02 PM
Reply:

Similar Artilces:

How do I fix this so I don't get an "Error 2015"
Dim x x = Application.CountIf("E5,E6000", "T13065") How do I fix this so I don't get an "Error 2015" Thanks, Rick Not tried it but I would think x=Application.Countif(Range("E5:E6000"),"T13065") Providing T13065 is a string you are counting. If it's a range reference x=Application.Countif(Range("E5:E6000"),Range("T13065")) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "2D Rick" <rbrowniii@compuserve.com> wrote in m...

Money 2003 restoring a backup asking for a password that does't ex #2
I have microsoft money 2003 at my work and would like to have the same data there in my home. So i made a backup, put on a pendrive and brought it to home. Installed microsoft money 2003 in my home and tried to restore this backup. Money 2003 insists asking a password that does not exist in the original backup file. I already tried to use the repair tool and didn't work. So, what i have to do to make the backup work and does not ask a password that does not exists? Thankyou sorry about the spam, i was using the google chrome and it bugged, sorry again about the tripple post :( "...

Can't use Merge feature
If I have a postcard file I've already made on the screen, when I try to pull up the "Merge" feature under Tools, it is shaded grey and I can't open it. It insists that I either form a new database list of addresses or edit the one that I want to merge. I don't want to edit the one I want to merge. I just want to MERGE it. If, however, I have just a postcard template on the screen (without it having been made into anything), I CAN pull up the "merge" feature under Tools. It is not shaded out. How do I un-shade the "merge" feature? To merg...

Printing Multiple Receipts at a Time From Journal
Hey guys, Is there a way to print multiple journaled receipts at a time? Say all receipts for a batch or by date or other criteria? Thanks Tom, Not that I've seen. -- = Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on the right. http://tinyurl.com/75bgz ========== "Tom Bombadill" <Genius_pos...

To write living will do I need special format
I just need to change and update a living trust You should consult someone with the appropriate legal knowledge in the jurisdiction in which you are domiciled. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "belladonna" <belladonna@discussions.microsoft.com> wrote in message news:F25A64CB-355F-44E9-A005-16AA61DA15A7@microsoft.com... > I just need to change and update a living trust ...

Nested schema bug? Project compiles, but doesn't read XML properly
We've run into an issue with VS2005 (at least I *think* it's an issue with Visual Studio) and one of our XML dataset schemas. We're trying to add a nested XML element to an existing element, but even though the project compiles fine, the XML document is not being read into a dataset properly. This is a trimmed-down sample of our current (working) XML document: <?xml version="1.0" encoding="utf-8"?> <AppMasterDataset xmlns="..."> <Aspects> <Key>...</Key> <Label>...</Label> <Aspect> ...

Change outbound server in header to fix 550 Can't verify your host name error
The headers on the outbound emails show the internal DNS name of our exchange server; obviously this won't resolve properly at the destination. How/where in Exchange 5.5 can I force the IMC to use a real fqdn on outbound mail? Thanks! Frinky You can do this in TCP/IP properties\Advanced\DNS tab of machine. And yes, this is not just for Exchange, so you may consider forwarding all outgoing mail to some relay server (your firewall or ISP's server). Professor Frink wrote: > The headers on the outbound emails show the internal DNS name of our > exchange server; obviously this...

Amex download issue
1/23/2005 was the last date that my American Express credit card account downloaded into Money (2004). Prior to that it happened automatically daily. I went to the American Express website and downloaded an ofx file - named ofx.ofx - and upon clicking on it and making sure that the account matched, money reported that the info was imported correctly. But it wasn't. There are still no account register entrees past 1/23/2005 (and yes there was activity beyond that date). Any ideas would be appreciated. thx Just spoke to Amex - they are having tech issues and are aware of the...

How do I set the number format to Base 12?
I would like to change the number format on my spreadsheet from Base 10 to Base 12, eg. 12 bottles makes up 1 case. Therefore, if I were adding up three different cells 9 bottles + 11 bottles + 6 bottles, my result should be 2 cases 2 bottles if possible 2.2 in a case column. See http://www.cpearson.com/excel/fractional.htm for details. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andrew Moore" <AndrewMoore@discussions.microsoft.com> wrote in message news:893CABE9-37D7-4E6B-8A7E-A5E679C8C824@microsoft.com... >...

IE8 Won't Install
I'm using a Toshiba Satellite laptop running Vista Home Pro, 32 bit. I uninstalled IE8 via Control Panel and now want it back. I downloaded it from Microsoft but when trying to install I get a dialog box that says "setup cannot install Explorer because it has detected a more recent version on my system". What's more recent than IE8? Using Start Button I typed in Internet Explorer and all it shows is 2 items with that name: a folder with only a weird picture of IE8 and a folder for quick launch of my other browsers that doesn't contain Explorer. I ho...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

Conditional Formatting w/ a List/Icons
I am trying to allow someone to select "Green", "Yellow" or "Red" from a list and the cell to display a green/yellow/red icon appropriately. Or, if possible, the user could just select the icon (instead of selecting text). Is this possible? Use Data Validation for the list. Type in Red, Yellow, Green as the list. This give the user the list to select from. Use Conditional Formatting for the fill part. Set three conditions, If Cell Value-"Green" (select a green fill), etc.. -- If this helps, please remember to click yes. "...

OWA logout issue
When using OWA (Exchange 2003) and logging out with IE it seems to delete ALL session cookies. Is there a setting to change this? It does not delete all of the session cookies if using Netscape or Firefox. Thanks ...

Column styles doesn't appear on the worksheet
Hello, one of my customers has changed the windows xp designs to its own. From this moment excel doesn't show the font color and the background color of a cell in the worksheet. Only in the print preview you can see the color settings of the cells. I'm not shure if this problem belongs to the changing of the windows xp designs, but from this moment it did occur. This occurs on new excel-documents and on existing ones. With another user account on the same machine the problem doesn't occur. I have reinstalled Office 2003 and even deleted user registry entries for Office...

outlook inbox refresh issue
Has anyone come acrosos an issue in MS Outlook 2000 where an incoming data packet processes but isn't deleted? There seems to be a refresh issue. Does anyone have any suggestions? Can you give a little more detail? Did you press the delete key and the message remained in the inbox? Or do you have a rule processing that should have deleted the message? Or...? -- Nikki Peterson [MVP - Outlook] "Johnny Jackman" <anonymous@discussions.microsoft.com> wrote in message news:02d301c39ffa$66c91380$a401280a@phx.gbl... Has anyone come acrosos an issue in MS Outlook 2000 where...

Issue installing FRx 6.7 on Windows 2003
I am encountering an unhandled exception error when attempting the installation. The installation prompts for the language, I select English, then Next. The progress bar makes it all the way to the right of the next screen, then I get the above error. This is a brand new quad CPU Dell OS is base WIndows Server 2003 Enterprise, not SP1 Only installed software is: IIS 6.0 (FTP, WWW, MSMQ) CA ArcServe (backup software) SQL 2000 Enterprise, Analysis Services, both at SP3a WinZip Acrobat 6.0 Reader Hello, Can you give us the exact error message you're receiving (all words and numbers fro...

how do I remove fx from the function line, can't enter data
I have the fx displayed just under my toolbar, and I can't enter or change data in any of the cells in the file. I can't get the red X, the Green check mark, or the black = sign to appear. There are very few areas that are not "greyed out" under the headings at the top. This situation applies to all of the excel files on this computer. I have Excel 2000. Please help. Can you move the cursor around anywhere in the spreadsheet? "dmdranch" wrote: > I have the fx displayed just under my toolbar, and I can't enter or change > data in any of the c...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

conditional format 04-15-10
Hi, I want the color of the text in cell B1 to change depending on the value in cell A1 How can this be done? I know I can do it with conditionale format for the cell itself but not for another one. Thanks JP Try this. Assume you want B1 text to change colour when the value in A1 is 5, select B1 and go to conditional formatting. In the condition, select 'Formula is' then in the empty space, type =A1=5 choose the font colour that you want. "Jean-Paul" wrote: > Hi, > > I want the color of the text in cell B1 to change depending on th...

Special format
How can I change the format from numerical to English?? e.g.: 420 to Four Hundred and Twenty Only It is called typing - UNBELIEVABLE or SEARCH and REPLACE but MS Office 2002 Chin support numerical to chinese: 123456789 -----> = &#19968;&#20740;&#20108;&#21315;&#19977;&#30334;&#22235;&#21313;&#20116;&= #33836;&#20845;&#21315;&#19971;&#30334;&#20843;&#21313;&#20061; =3D.=3D" Can anyone help~~ >-----Original Message----- >It is called typing - UNBELIEVABLE > >or SEARCH and REPLACE > > >. > ...

reply formatting in Outlook 2010
I set up my normal.email.dotm file to have 12 pts below each paragraph, which helps formatting issues when sending emails to people using gmail and other programs that interpret paragraph breaks differently than Outlook. That works just fine with new emails. However, when I reply to an email, even an html email, the formatting is not applied. I can fix this by choosing Format Text and then setting the spacing as per above, but this is a pain in the neck. Why don't reply emails use the same template? Is there a way to template replies so i can use the same formatting? thanks in advance, ...

Can't Stop External Hard Drive
I have a Western Digital 250 gb external hard drive connected via USB port. I am also running Outlook 2007, which I believe has a function searchindexer.exe that searches all my drives to index the files on them. I want to be able to unplug the drive without first turning off my computer. But I get messages saying that the drive can't be stopped. It used to be that if I stopped searchindexer.exe, I could stop the drive. But that's stopped working. Here are two questions: 1. What is preventing me from stopping the drive?; 2. What is the worst that can happe...

O.T. FYI BlackBerry Enterprise Server Express for Free for SBS2008
BlackBerry Enterprise Server Express for Free http://na.blackberry.com/eng/services/business/server/express/ Connect two people via BlackBerry� Smartphone's and ideas get bigger. Progress happens faster. Answers get smarter. So imagine what happens when you give a business the freedom to connect all its employees. BlackBerry� Enterprise Server Express enables businesses of any size to quickly and easily get started with the BlackBerry solution. It provides advanced BlackBerry Smartphone features with no additional software or user license fees, and works with any Internet-...

Time Delayed Response to MsgBox
I have a several "research" files that I set up to run a macro as soon as a file is opened. In each case the macro retrieves and summarizes a bunch of data taking several minutes to execute. I'm setting up a "master" file with a simple macro that will open and close each of the "research" files sequentially so I can run the series of reports without being at the computer for hours. There are times where I want to open a "research" file to review the data but not execute the macro. I'd like to add a MsgBox that prompts the user t...

How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...