DateAdd in VBA

[jonnywakey]

Hi all,

I have developed an access db to track laboratory sample process times but am stuck with the following:

I have a form (Job Tracker Form) which has a combobox [Task_Ref], the form
also has the following text boxes which I need to use when performing a calculation:-

[Start_time]
[Cycle]
[Est_Comp_Time]

Processes are stored in table (Task Picklist) which contains process and cycle time.

Combobox [Task_Ref] on form calls the following rowsource:-

SELECT [Task Picklist].[Task Ref], [Task Picklist].[Cycle] FROM [Task Picklist] ORDER BY [Task Ref];

[Cycle] textbox contains =[Task_Ref].[column](2) to pull the cycle time from the selection made in [Task_Ref] combobox.


What I would like to happen is that in the "After Update" event of [Task_Ref] combobox, the [Est_comp_time] textbox is populated with the following calculation:-

[Est_Comp_Time]=[Start_time]+[Cycle] eg

Start time = 20:00 + cycle time of 60 mins = Est Comp Time of 21:00hrs

I have tried to use the DateAdd function with "n" as the minute control but am failing, can anyone help?

Thanks

jonny

 

[pr2-eugin]

So you have the code..

Private Sub Task_Ref_AfterUpdate()
    Me.[Est_Comp_Time] = DateAdd("n", Me.[Cycle], Me.[Start_time])
End Sub

and it does not work?

 

[TJPoorman]

Are all of your cycle times in minutes?

Also, make sure that your Start_Time and Est_Comp_Time are Date/Time type with a Short Time format.

Then this should work:

Me.Est_Comp_Time = DateAdd("n", Me.Cycle, Me.StartTime)

 

[jonnywakey]

Guys

Thanks for the code, unfortunately still not working, the time in [Est_Comp_Time] isn't adding the minutes from [cycle], i must be missing something.

Also tried the code in the On Change Event on the [Task_Ref] combobox!

Thanks

Jonny

 

[namliam]

is your start_time a date field? or a text field?

If its a text field try: DateAdd("n", Me.Cycle, Timevalue(Me.StartTime))

 

[pr2-eugin]

Show the Code you have written, What is the Type of Start_Time in the table. Try Debugging.

It needs to be in the After Update of the ComboBox, On Change might be too early..

Also what is the Row Source, Bound Column, Column Count and Column width of the [Task_Ref] combobox

 

[jonnywakey]

Thanks for the replies guys,

The start_time field is a "short time" field.

The Row Source, Bound Column, Column Count and Column width of the [Task_Ref] combobox is:-

Row source = SELECT [Task Picklist].[Task Ref], [Task Picklist].[Cycle] FROM [Task Picklist] ORDER BY [Task Ref];

Bound Column = 1

Column Count = 2

Width of Column = 5cm;2.542cm

Fields in the Task Picklist table are:-

0 = TaskID 1=Task Ref 14=Cycle

 

[pr2-eugin]

Try the following RowSource..

SELECT [Task Picklist].[Cycle], [Task Picklist].[Task Ref] FROM [Task Picklist] ORDER BY [Task Ref];

 

[namliam]

Dateadd("N"... seems to me to throw some wierd answers when I do it in an immediate window :/ Is it bugged?

Try Timevalue() + Cycle / (60 / 24)

 

[jonnywakey]

pr2-eugin,

Thanks but the change to the rowsource forces the user to select the cycle time from the combobox rather than selecting the product they are working on which then populates the [cycle] textbox on the form with the textbox [cycle] bound with the following expression:-

=[Task_Ref].[column](2)

This does show the correct cycle time in the textbox when the user makes their selection from [Task_Ref], is just seems like the following vba doesn't capture the cycle time from [cycle] and add it to the [start_time] to give [est_comp_time].

Code is:-in After Update event on [Task_Ref]combo

Me.[Est Comp Time] = DateAdd("n", Me.[Cycle], Me.[Start_Time])


Maybe I'm missing something.

Thanks

jonny

 

[pr2-eugin]

Could you quickly Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[jonnywakey]

pr2-eugin

Stripped out DB attached, appreciate your support.

 

[namliam]

Dateadd("N"... seems to me to throw some wierd answers when I do it in an immediate window :/ Is it bugged?

Try Timevalue() + Cycle / (60 / 24)

quoting myself because I have a feeling this post may have been overlooked by Jonny

 

[jonnywakey]

namliam

Thanks for the post, I will try your suggestion and let you know how i get on!

Thanks again

Jonny

 

[pr2-eugin]

List of things that you need to work on:

• Normalization - A simple Google search should give you several hits.
• Calculated fields is a No No - http://allenbrowne.com/casu-14.html
• Lookup fields in tables is a No No - http://access.mvps.org/access/lookupfields.htm
• Multivalued fields not good !

All the above factors seems to have put in in a position where normal/simple calculation is a struggle. You need to fix the issues now, before it is too late !

 

[TJPoorman]

Thanks for the replies guys,

The start_time field is a "short time" field.

The Row Source, Bound Column, Column Count and Column width of the [Task_Ref] combobox is:-

Row source = SELECT [Task Picklist].[Task Ref], [Task Picklist].[Cycle] FROM [Task Picklist] ORDER BY [Task Ref];

Bound Column = 1

Column Count = 2

Width of Column = 5cm;2.542cm

Fields in the Task Picklist table are:-

0 = TaskID 1=Task Ref 14=Cycle

The problem is with your rowsource. Change the rowsource to:

SELECT [Task Picklist].[Cycle], [Task Picklist].[Task Ref] FROM [Task Picklist] ORDER BY [Task Ref];

Then change the column width to "0;5"

Or you could change the bound column to "2"

The reason it isn't working is because the control isn't storing the cycle time it is storing the Task Ref

 

[jonnywakey]

Re: DateAdd in VBA - UPDATE

Guys

I would like to thank you all for your support with my access project, I have taken heed of the advice and stripped the database back to basics and planned my requirements and relationships in a more structured way.

PROGRESS.........at last!

Thanks again

Jonny