How do i increment a date field?

[jezjez]

I maintain a Time Tracking db for office workers. They log in and select the task, billing code, number of hours per task worked etc.

I need a 1 click solution for the users to enter a weeks records at a time - eg for vacation, illness or a weeks training course. Currently they have to enter a record for each day. Most fields are automatically populated (based on defaults they can specify elsewhere) but it still means about three clicks to enter a record for a day, which for a two week vacation starts to add up.

Basically; how can i automatically increment a date field?

I am an intermediate VB/Access admin so could handle answers using a date range specified through a calendar form or just a solution where the user just clicks SUBMIT for each day and the date is incremented.

cheers Duncan

 

[Dan_T]

are you looking for the datediff() function? Add/subtract any part dd mm yy or time for a date.

 

[jezjez]

i dont think so; that will just give me a figure output (number of days between two dates).

i need to actually create records on the table all the same except the date incremented by one day eych time. as mentioned above this can be user driven (they click submit on the form 10 times for 10 days vacation), or by the giving of From and To dates. I suppose they coud give "10" days vacation from x date but then i have to include public holidays and weekends in the calculations...

i have tried using dateadd but this only adds one day to todays date for the second time the user presses the 'submit' button, (so user gets a record dated today, and all further records created just have tomorrows date).

The problem is that i dont know how to hold the date in memory so that it can manipulate it; it just reads the date on the form, which is todays date (or any date the user has set it to). Can i write to a label with a VB calender function embedded?

cheers.

 

[Dan_T]

hehe sorry meant dateadd. Show us yer code.

 

[jezjez]

he he - no worries.

which bit of code; the Time Entry form?

This is the bit of code that copies a record, which i would like to have increment:

Private Sub Command70_Click()
On Error GoTo Err_Command70_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Date = DateAdd("D", 1, Date)

Exit_Command70_Click:
Exit Sub

Err_Command70_Click:
MsgBox Err.Description
Resume Exit_Command70_Click

End Sub

 

[Dan_T]

the reason the date only adds one is each time you call the routine you are asking it to add 1 day to todays date not the last date you have. You need to pass the last date to the function. ie the field name where the date is being updated. Therefore when you add a day the next time you run the function it will add the day to it not today. Make sense

so instead of Date = DateAdd("D", 1, Date)

something like

me.fieldname = dateadd("d",1,me.fieldname)

where me.fieldname is the fields where the date you are modifiying lies.

Any good to you?

 

[jezjez]

Hi Dan,

Thanks, i think thats exactly the solution i need to head for..

I have tried adding this code but i am having trouble getting it to accept the location of "fieldname".

Do i need to declare the location first?

Do i need to specify the date field on the form or on the table?

thanks for your help on this...

 

[Mile-O]

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

This code is now defunct. It has been since Access 95. The wizards, for some reason, continue to use it. Bit of advice - don't use the button wizards.

These commands have been superceded by the DoCmd.RunCommand methods.

 

[Dan_T]

HI Jez what you need to do is read the word feildname as my fieldname and bung whatever the name of the field is into the expression. You can declare a variable for the control and set it to equal the fieldname and use that if you like. Something like

dim fldDate as control
set fldDate = me.WhateverYouCalledYourField

then use fldDate in your expression

NOTE. Don't copy these examples as I'm jst throwing them down in a hurry without checking, sorry.

 

[jezjez]

thanks ..

just for the record, dont worry i wasn't trying to use "fieldname" - i was aware that was an example ... i always find that funny when you see people post: " but where do i find 'yourtablename' in my DB??!"

anyway, i'll try your suggestions now..

 

[jezjez]

But the bit i dont understand is : "set fldDate = me.WhateverYouCalledYourField"

Is "WhateverYouCalledYourField" the fiels on the Form (CmbDate) or the field on the table ([TrackTime]![Date])?

 

[Mile-O]

But the bit i dont understand is : "set fldDate = me.WhateverYouCalledYourField"

Is "WhateverYouCalledYourField" the fiels on the Form (CmbDate) or the field on the table ([TrackTime]![Date])?

Do you have the table bound to your form? If so, make aquery based on the table and bind this to the form - it's a better practice.

Next, you can't get fields on forms; you get controls bound to fields.

Me.CmbDate = DateAdd("d", 1, Me.CmbDate)

will work fine.

 

[jezjez]

thanks guys. we now get a record for today, then today + 1 repeated as many times as you press submit.

ie - still cant get it to take the last created date and add one to that...

 

[jezjez]

Private Sub Command70_Click()
On Error GoTo Err_Command70_Click

Dim CmbDate As Control

Set CmbDate = Me.CmbDate

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

CmbDate.Requery

'Date = DateAdd("D", 1, Date)

'fldDate = DateAdd("d", 1, fldDate)
Me.CmbDate = DateAdd("d", 1, Me.CmbDate)

Exit_Command70_Click:
Exit Sub

Err_Command70_Click:
MsgBox Err.Description
Resume Exit_Command70_Click

End Sub