DTPicker: Incrementing Time Value

I have a form with the following DTpicker's

InterviewStart (format= "mm/dd/yyyy HH:mm")
InterviewEnd (format= "mm/dd/yyyy HH:mm")

When the user inserts a record, the InterviewStart field updates to Now().

I would like to set the default value of InterviewEnd to be 1.5 hours from 
Now(). How does the VBA code look in order to accomplish this?

TIA!
0
Utf
1/7/2010 4:30:01 AM
access.formscoding 7493 articles. 0 followers. Follow

7 Replies
1350 Views

Similar Articles

[PageSpeed] 25

Hi Brandon,

You might try something like:

Me.InterviewEnd = DateAdd("h", 1.5, Now())

or

Me.InterviewEnd = DateAdd("h", 1.5, Me.InterviewStart

Hope that helps...


"Brandon P" wrote:

> I have a form with the following DTpicker's
> 
> InterviewStart (format= "mm/dd/yyyy HH:mm")
> InterviewEnd (format= "mm/dd/yyyy HH:mm")
> 
> When the user inserts a record, the InterviewStart field updates to Now().
> 
> I would like to set the default value of InterviewEnd to be 1.5 hours from 
> Now(). How does the VBA code look in order to accomplish this?
> 
> TIA!
0
Utf
1/7/2010 4:40:11 AM
On Wed, 6 Jan 2010 20:30:01 -0800, Brandon P
<BrandonP@discussions.microsoft.com> wrote:

>I have a form with the following DTpicker's
>
>InterviewStart (format= "mm/dd/yyyy HH:mm")
>InterviewEnd (format= "mm/dd/yyyy HH:mm")
>
>When the user inserts a record, the InterviewStart field updates to Now().
>
>I would like to set the default value of InterviewEnd to be 1.5 hours from 
>Now(). How does the VBA code look in order to accomplish this?
>
>TIA!

I think you'll need to use:

Me!InterviewEnd = DateAdd("n", 90, Me!InterviewStart)

The increment argument to DateAdd needs to be an integer so you can add 90
minutes but not 1.5 hours.
-- 

             John W. Vinson [MVP]
0
John
1/7/2010 4:51:15 AM
>InterviewStart (format= "mm/dd/yyyy HH:mm")
>InterviewEnd (format= "mm/dd/yyyy HH:mm")
This is wrong as "m" is used for Months and not Minutes.

Correct would be --
    InterviewStart (format= "mm/dd/yyyy HH:nn")
    InterviewEnd (format= "mm/dd/yyyy HH:nn")

-- 
Build a little, test a little.


"John W. Vinson" wrote:

> On Wed, 6 Jan 2010 20:30:01 -0800, Brandon P
> <BrandonP@discussions.microsoft.com> wrote:
> 
> >I have a form with the following DTpicker's
> >
> >InterviewStart (format= "mm/dd/yyyy HH:mm")
> >InterviewEnd (format= "mm/dd/yyyy HH:mm")
> >
> >When the user inserts a record, the InterviewStart field updates to Now().
> >
> >I would like to set the default value of InterviewEnd to be 1.5 hours from 
> >Now(). How does the VBA code look in order to accomplish this?
> >
> >TIA!
> 
> I think you'll need to use:
> 
> Me!InterviewEnd = DateAdd("n", 90, Me!InterviewStart)
> 
> The increment argument to DateAdd needs to be an integer so you can add 90
> minutes but not 1.5 hours.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
1/7/2010 5:06:01 PM
John is, of course, correct. Help says that it has to be a "numeric
expression" which it defines as a "any expression that can be evaluated as a
number." Then about a page down, after listing all the various intervals and
the rest of the ins and outs of using the function, it mentions that if it
isn't a Long type it'll round it to the nearest whole number.

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

0
Linq
1/7/2010 8:58:01 PM
On Thu, 7 Jan 2010 09:06:01 -0800, KARL DEWEY
<KARLDEWEY@discussions.microsoft.com> wrote:

>>InterviewStart (format= "mm/dd/yyyy HH:mm")
>>InterviewEnd (format= "mm/dd/yyyy HH:mm")
>This is wrong as "m" is used for Months and not Minutes.

To my moderate surprise, the Format feature is sufficiently context-sensitive
that it interprets the first mm as month and the second as minutes:

?format(now,"mm/dd/yyyy hh:mm")
01/07/2010 16:34


I'd still use n for miNutes and m for Months, though!
-- 

             John W. Vinson [MVP]
0
John
1/7/2010 11:35:36 PM
Thanks for the correction, John.


"John W. Vinson" wrote:

> On Wed, 6 Jan 2010 20:30:01 -0800, Brandon P
> <BrandonP@discussions.microsoft.com> wrote:
> 
> >I have a form with the following DTpicker's
> >
> >InterviewStart (format= "mm/dd/yyyy HH:mm")
> >InterviewEnd (format= "mm/dd/yyyy HH:mm")
> >
> >When the user inserts a record, the InterviewStart field updates to Now().
> >
> >I would like to set the default value of InterviewEnd to be 1.5 hours from 
> >Now(). How does the VBA code look in order to accomplish this?
> >
> >TIA!
> 
> I think you'll need to use:
> 
> Me!InterviewEnd = DateAdd("n", 90, Me!InterviewStart)
> 
> The increment argument to DateAdd needs to be an integer so you can add 90
> minutes but not 1.5 hours.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
1/8/2010 9:55:01 PM
On Fri, 8 Jan 2010 13:55:01 -0800, theDBguy <thedbguy(at)gmail(dot)com> wrote:

I remember a very confusing afternoon trying to figure out why my DateAdd
wasn't working. IIRC I asked a question here and someone pointed me the right
direction.

>Thanks for the correction, John.
>
>
>"John W. Vinson" wrote:
>
>> On Wed, 6 Jan 2010 20:30:01 -0800, Brandon P
>> <BrandonP@discussions.microsoft.com> wrote:
>> 
>> >I have a form with the following DTpicker's
>> >
>> >InterviewStart (format= "mm/dd/yyyy HH:mm")
>> >InterviewEnd (format= "mm/dd/yyyy HH:mm")
>> >
>> >When the user inserts a record, the InterviewStart field updates to Now().
>> >
>> >I would like to set the default value of InterviewEnd to be 1.5 hours from 
>> >Now(). How does the VBA code look in order to accomplish this?
>> >
>> >TIA!
>> 
>> I think you'll need to use:
>> 
>> Me!InterviewEnd = DateAdd("n", 90, Me!InterviewStart)
>> 
>> The increment argument to DateAdd needs to be an integer so you can add 90
>> minutes but not 1.5 hours.
>> -- 
>> 
>>              John W. Vinson [MVP]
>> .
>> 
-- 

             John W. Vinson [MVP]
0
John
1/8/2010 11:39:07 PM
Reply:

Similar Artilces:

Duplicate switches second time
Hi, I'm having some difficulty to figure out how to achieve full redundancy on parts of my network. At this moment I have 2 routers 2821 in standby configuration (HSRP) and 2 PIX firewall in failover configuration with one switch between them. Client requires duplicate switches as well. Any idea how to achieve this? Any document out there? Firewall Failover | sw1 | <-----------> | sw1a | Internet ^ ^ | | v v | fw1 | <---sync----> | fw1a | Firewall Failover ^ ^ | ...

Time and Materials
I've been playing with CRM 3.0 for a couple of weeks now. We have a number of clients who would like the functionality in the Sales and Marketing Modules, but don't use contracts, contract lines, etc. They just bill time and materials. They would like the advantage of being able to schedule services and have the schedules show up in outlook, but need a simple interface to do basic service work. Technicians need to be able to log in and see their schedules work, enter their time on these jobs and a description of work, includes parts used, and close the work order to have it ...

test for not blank, use value in cell and set as variable
I posted this earlier this week, found it. Now can't find it again. I want to check a range in a column. ie, G8-G18 for a cell that isn't blank. So if G12 has a value in it, I want to enter the value of G12 into a variable declared as var16th. I've searched for the past hour trying to find the post again. I've turned on the email of replies this time ;) thanks folks! ...

listing records from 2 database tables even if there are no values in one table
I hope that this makes sense, but as an example: I have 2 databases. The first one (db1) contains Product Name & Serial Number. The second (db2) contains Serial Number and Price (but does not contain all of the records in db1). I want to see ALL Product Names (from db1) and Prices (from db2), where price is available. When I link "Serial Number" from each database, I only get the common results, withe some Product Names eliminated. How could I get All Product Names and Prices, showing "n/a" (or something like that) when there is no Price on record? Any help is gr...

Format Axis
Hi, Using XL 2003. I have a workbook with 3 graphs. Two of the graphs are correct and they both source their data off of a particular worksheet. The third graph is giving me grief as I am not able to have its X-Axis show as time. It sources its data from a different sheet. When I Format Axis, I expect to see Time (X) axis scale Minimum Maximum Base Unit Major Unit Minor Unit Value (Y) blah blah... Instead I get the following: Value (X) axis scale Minimum Maximum Base Unit Major Unit Minor Unit Value (Y) I am missing the base unit. The source data is DEFINITELY a date as I have format...

Set lookup value(s) from IFrame (Quote Product)
I've created an IFrame that will have links or buttons that refers to commonly used products. When a user clicks one it will set the correct lookup value for the product and unit. Doing this will avoid having to navigate through the lookups and in our case cut down the number of clicks by a half. I can do this one of two ways but I can't figure out how to do either one. - Option 1: The Iframe contains javascript functions and buttons that set variables like product GUID and then pass that to the parent form. Since this isn't just a single field value, I cannot figure out how to p...

Outlook 2002 and Safe Mode all the time.
Outlook keeps asking to launch in safe mode... can't find the reason. Any ideas on what I should look for? ...

Changing hourly increment in Plan A Meeting/Meeting Request window??
I have a user who, when she goes to Plan a Meeting/Meeting Request window, she sees 3 hour increments (on the timeline to the right of each selected attendee). INcrement go... 12 o'clock (space)3 o'clock(space) 6 o'clock, etc. I cannot recreate it. Changing the normal calendar view does not affect it. I have combed thru every menu and submenu with no luck. any ideas? ...

How do you use DTpicker in a Userfrom
Hi, Can you please show me how to use dtpicker in a userform. I am new to vba and i am having some trouble trying to understnad this part. Thanks -- TDR You simply drag and drop the control on to the userform, and resize it to the desired size. You get the currently selected date with the Value property. E.g., MsgBox Me.DTPicker1.Value You might want to be more specific in your question. What problems are you having with the control? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "TDR" <TDR.1tg3yr@news.excelbant...

Run-Time error after Windows Update
Windows just completed an automatic update that required a re-boot. Now RMS POS will not start. I get the following error: RUN-TIME ERROR '-2147319779 (8002801d)': Automation Error Library not registered. This is RMS 1.20183. It has been working fine for a year and a half. Any suggestions?? The manager program works fine. Thanks in advance, Tom This is a multi-part message in MIME format. ------=_NextPart_000_0217_01C6AA5F.4C3DC750 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Tom, I repor...

series point value
hi, how to get a seares point value, for example, first point value and last point value? Thanks. ...

Increment using a Command Button
I am trying to get a cell to go up in value at the press of a comman button - i.e. click the button, cell A1 goes up by 1. Please help -- Message posted from http://www.ExcelForum.com Hi ksears Sub test() Range("A1").Value = Range("A1").Value + 1 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "ksears >" <<ksears.1515sf@excelforum-nospam.com> wrote in message news:ksears.1515sf@excelforum-nospam.com... > I am trying to get a cell to go up in value at the press of a command > button - i.e. click the button, cell A1 goes up by 1. ...

run-time error 1004
i have a workbook with macros and some vba scripts and have password protected some of the sheets. i have created this workbook in Excel 2002. I distributed it to users using Excel 2000, who are getting Run Time Error 1004, the file is protected and will be read-only. The sheets are protected but i have opened ranges for data entry. All works well for users with Excel 2000. What I have tried without success: - Save File As | Tools | General Options | Advanced | Encryption Type - Weak or Office 97/2000 compatible. it still gives the run-time error. anyone has seen this? Ooh. I think...

Incrementing a Field to the Next Value
Hello: I need a T-SQL query that returns a string value and, from the latest dataset that I have, returns the next number. This would be, then, that string value "+1". Now, regardless of whether there are leading 0's, I want SQL to return the next number for this string field. This, in essence, is the value returned from the query "+ 1". So, if the value in the field were 000039, then I would want SQL through this T-SQL query to return "40". Or, if the field were 1157, I would want it to return "1158”. In other words, I want for...

Est.Value field type different in schema
Greetings, The estimated value attribute type in the Lead object is float, but in Opportunity it is currency. Therefore I cannot map from the lead to the opportunity. I tried to create a new attribute in the Opportunity object, type float, so I could map the estimated value from Lead into it. Which is working okay. However, in the Opportunity form I can't remove the existing Estimated Value field, I get an error when I tried to remove it in the customization dialog. Did Microsoft screw this up? What are the field types different? I also noticed the Est. Value in the opportunity i...

Value of sheet A cell = sheet B cell => fill in sheet name
I have sheet A and three sheets N1, N2, N3 How to have a value "name of sheet" filled in a cell in sheet A when a value in a cel in A and a cell in one of the sheets N1, N2, N3 is the same? Sheet A 10 | | 11 | | X 12 | | Sheet N1 ... ... ... Sheet N3 ... 11 ... X -> N3 I cound't find it out via the Help section. Bart Assume data to be compared in sheets: N1, N2, N3 are in col A. Data is assumed unique in each sheet and across all 3 sheets, ie there are no duplicates Then in sheet: A, with the data in A1 down, Put in B1: =IF(ISNUMBER(MATCH(A1,'N1'!A:A,0)...

How can I set Office document to print from same printer ea time?
In past versions of Word if you sent the doc to a printer, each time you printed the document it would go to the same printer. Now, in 2003 Word, the printer changes to whatever printer I sent the last document to. For instance, I send envelopes to one printer and letters to another. I shouldn't have to reset the printer everytime. On Mon, 8 Feb 2010 19:29:01 -0800, JudyD <JudyD@discussions.microsoft.com> wrote: >In past versions of Word if you sent the doc to a printer, each time you >printed the document it would go to the same printer. Now, in 2003 Word, ...

increment on exit
How can I increment a numeric field to the next number on exiting the field's control in a subform? I can't find any exmples here and I'm sure it must be easy! Thanks for your help! -- www.Marzetti.com Private Sub YourTextBox_Exit(Cancel As Integer) If Not IsNull(Me.YourTextBox) Then Me.YourTextBox = Me.YourTextBox + 1 Else Me.YourTextBox = 1 End If End Sub BTW, Love your Veggie Dip! -- Message posted via http://www.accessmonster.com Thanks, Linq! I wish I could say it was *MY* veggie dip - then maybe I could have someextra money in the bank! -- www.Marz...

Increment a cell
I have a date in a cell (constant, not the result of a formula). Is there any keyboard shortcut that will add 1 to the date, or do I need to write a little macro? Example: cell contains 2007-12-14; pressing the key combination should change it to 2007-12-15. (I tried "increment" in Excel help but didn't get anything useful. Maybe I need a different search term?) -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life...

Checking the Value of a Check Box in a Macro
I have a macro that hides a named range on rows. I also have a check box. If the check box is checked I want an error message to display and tell the user to clear the check boxes before teh rows will hide. ' ' Hide Spec_Plu_22_10_00 ' Sub Hide_Spec_Plu_22_10_00() 'Unprotect Time Sheet ActiveSheet.Unprotect If CheckBox135 = True Then MsgBox "Uncheck boxes in spec section your trying to close.", vbInformation, "Alert Message" GoTo 300 Else GoTo 200 End If 'Hide Rows 200 Range("Plu_22_10_00").Selec...

DTPicker in outlook forms
I have added the MS Date & Time Picker Control version 6.0 to a custom form. In the Properties of control, I have binded the control to a user-defined field (type: Date/Time) but when I run the form, the value of the field is always null. Could anyone give sugestions or solutions to me? thanks a lot! See http://www.outlookcode.com/d/datepick.htm. The best place for Outlook forms questions is the outlook.program_forms newsgroup "down the hall". -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003 Rem...

How can I keep track of when (date and time) data is entered into.
I am trying to create a spreadsheet for a high school class. I need to be able to track when a student has entered data into specific cells of the spreadsheet. Any ideas? In the code behind the worksheet, enter (eg) Private Sub Worksheet_Change(ByVal Target As Range) Cells(1, 1).Value = Now() End Sub This will enter in Cell A1 the date and time at which any entry is made in that worksheet. If you need the location of the time-stamp to vary according to which cell is changed then you can test the value of Target and vary the destination cell accordingly. -- Return email address is n...

Adding time
I am trying to add times such as 28:00,35:00,12:00 and I keep getting zero for the sum, could someone please give me a formula or format for this problem. Thanks, Mike Hi Mike, I would suggest that your times are entered as text and not as formatted times. Select the column and then set the number format to custom format to [hh]:mm Then select any blank cell on the worksheet and enter 1 and then copy that cell and then select all of your times and Paste Special -> Multiply to force the entries from text to time. (Multiplying by 1 does not change the value) Note...

Check last Sync Time
I have a client who's CRM system will not goback on-line. Anyone know how can I find outt the last successful synch time/date? Thanks Roger I was searching for the same subject and didn't find an answer, so I wrote a query to display this information for all users, you can filter or group on FullName if you want a specifc user: select ssi.SyncResult, ssi.StartTime-.208 as SyncStart, ssi.EndTime-.208 as SynchEnd, datediff(minute, ssi.StartTime, ssi.EndTime) as TotalTime, ssi.DataSize, ssi.InsertObjectCount, sub.FullName, sub.InternalEMailAddress from SubscriptionSyncInfo ssi...

How to calculate a Time Difference
I currently am looking to be able to subtract two times (using the 24 hour clock), where the result is a Decimal. For example. I leave at 14:00 and arrive at 17:30. Thats a difference of 3.5 hours. I believe I have this solved with the following formuals: =(C2-INT(C2))*24-(B2-INT(B2))*24 (WHERE C2 is the arrival time and B2 is the DEPARTURE Time). My problem is that if you leave at 23:30 and don't arrive until 03:30, then it gives me a large negative number instead of 4.0 hours. Any ideas? Thanks, Try: =(C2-B2+(B2>C2))*24 And format the cell as #.0. For more info on su...