Automatically update date field everyday

[chapterhouse]

Hello,

I am using Access2007.
Attached is my database.

I am i am attempting to track the duration of days since an initial date.

A individual has 3 business days to have their bloodwork/labs drawn after given instructions to do so. 'StartDate'= the initial date. 'LabsDate'= current date(would like it to automatically update to the current date everytime the database is open). BD ElapsedLabs= # of days that has elapsed between the initial day and todays date.

I am able to calculate the #days elapsed no problem, but I am having a problem with 'LabsDate' automatically updating to todays date. I am able to automatically place the current date in the first/ fresh entry, but it fails to update the next day.

I have tried placing the code Me.LabsDate= date() in the Before Update Form Property Sheet. Cannot get it to work.

I have followed these directions to the same question posted on this forum to no avail.

I have minimal experience write code/ programming. I am able to manipulate it after hours of looking at it.

Any help is appreciated.

 

[pbaldy]

Why would you try and save the current date in a table when it can be derived at any time using the Date() function? Just use the function instead of your field in your calculation.

 

[chapterhouse]

Thanks for the reply.
What you wrote makes sense. I think I did it because I am a programming novice.
I made the current date a field because it's not as abstract as writing code and I thought it might be easier to manipulate the dates.

I really don't know how to use the function in the calculation instead of the field.

 

[pbaldy]

Just switch them, like instead of:

intTotalDays = DateDiff("d", StartDate, LabsDate) + 1

you'd have:

intTotalDays = DateDiff("d", StartDate, Date()) + 1

Note sometimes Access will drop the parentheses after Date(); if so, don't worry about it.

 

[chapterhouse]

k. Thanks again.

I 'foundd&replaced' all instances. I get an error when compiling in the first function line:

Function BusinessDays(StartDate As Date Date()As Date) As Integer

So I dropped the Date() and now the code looks like:
Function BusinessDays(StartDate As Date) As Integer

No compiling error but get an error. '#Name?' in the field 'BD ElaspedLabs'

I think the problem lies with the function 'date()' - I have to declare it somewhere, but i don't know how or if that is the case.

 

[Galaxiom]

I would just use the expression directly in queries but if you want to have a function it looks like this:

Public Function BusinessDays(ByVal StartDate As Date) As Integer
      BusinessDays = DateDiff("d", StartDate, Date) + 1
End Function

Put in a Standard Module.

 

[chapterhouse]

Hi,

Thanks for the help.

Replacing enddate with date does not work. I cannot get it to work.

and using the

Public Function BusinessDays(ByVal StartDate As Date) As Integer BusinessDays = DateDiff("d", StartDate, Date) + 1 End Function

does not take into account holidays

 

[chapterhouse]

here is the code.

i really just need enddate to always reflect current date so it returns a value which increases with every passing day(taking into account weekends and holidays). for example if start date was yesterday and i opened the database today, the function would return a value of 2 days- today and yesterday. if i open the database tomorrow, the value should automatically update to 3 days.

thank you everyone above for the help so far. like i said i am a novice.


Function BusinessDays(startdate As Date, enddate As Date) As Integer 'By Susan Sales Harkins and Doris Manning 'Inside Microsoft Access Feb 2004

Dim intHolidays As Integer
Dim intTotalDays As Integer
Dim intWeekendDays As Integer
'Dim rst As New ADODB.Recordset
Dim rst As DAO.Recordset
Dim strSQL As String
Select Case DatePart("w", startdate, vbMonday) 'Week starts on monday
Case 6
startdate = DateAdd("d", startdate, 2) 'Saturday
Case 7
startdate = DateAdd("d", startdate, 1) 'Sunday
End Select
Select Case DatePart("w", enddate, vbMonday) 'Week starts on monday
Case 6
enddate = DateAdd("d", enddate, -1) 'Saturday
Case 7
enddate = DateAdd("d", enddate, -2) 'Sunday
End Select
strSQL = "Select Count(*) as HolidayCount From tblHolidays " & _
"Where HolidayDate BETWEEN #" & startdate & "#"
& _ " AND " & "#" & enddate & "#;"
'rst.Open strSQL, CurrentProject.Connection
Set rst = CurrentDb.OpenRecordset(strSQL)
intHolidays = rst!HolidayCount 'Count Holidays between dates
intTotalDays = DateDiff("d", startdate, enddate) + 1 'Calc dif in days
intWeekendDays = DateDiff("ww", startdate, enddate, vbMonday) * 2 'Calc dif in weeks and multiply by 2
BusinessDays = intTotalDays - intWeekendDays - intHolidays
Set rst = Nothing End Function

 

[pbaldy]

You can go one of two ways. Leave the function as is and use Date() when calling it:

BusinessDays SomeFieldName, Date()

or what I would do, drop the second parameter:

Function BusinessDays(startdate As Date) As Integer

Replace each instance of enddate in the function with Date(), and when you call it, only pass the one parameter"

BusinessDays SomeFieldName

 

[smig]

Not reading the full thread.
Leaving the function as is and sending Date() as paramater will let you send other dates to the same function, if required.
You may not need it now but might need it at a latter time.
I always prefer more global functions even if more parameters are required.

 

[Alvin neil]

Hi my name is Alvin
My table contains a field name current date that should update automatically as days go in order to compute how many day it is since members registration
Please help me to get the current date to update automatically

 

[namliam]

Thanks for resurecting a thread over 4 years old, anyways as the thread suggests, current date should not be stored at all.

To compute the days you can run a simple query and calculate: DaysSinceRegistration: Date() - RegDate

Or put a simular calculation in a form.

 

[Alvin neil]

Thank

Thanks for resurecting a thread over 4 years old, anyways as the thread suggests, current date should not be stored at all.

To compute the days you can run a simple query and calculate: DaysSinceRegistration: Date() - RegDate

Or put a simular calculation in a form.

Thanks alot it works
I was really stuck