Full Version: DateAdd function in controlsource of a bound field?
UtterAccess Forums > Microsoft® Access > Access Forms
freakazeud
Hi everyone,
I'm a newbie and have one small questions which has been bugging me with a project of mine.
Ocreated a database which tracks training of people and now I want a reminder pop up to appear when the refreshing course needs to be taken. I added a field called DateDue, which I want to populate with the calculation of the DateTaken field + 3 years. Plaing a little around I used the DateAdd function in the controlsource of DateDue with the following code ( =DateAdd("yyyy",3,[Date]) ). Ok now it calculates the day the refresher course needs to be taken. Now I want to have a pop up message when you start the database/ before the switchboard which counts the training entrees which need to be refreshed. So I wanted to add some kind of code to the on load event of my switchboard. The problem I have now is that if I want to add vb code to that event I will need to have the calculated refreshing course date (3 years later then the original taken date) in a table where the original taken date is in, too. So how do I bound the field which has the code in it's controlsource to the table which has column for that field, without loosing the calculation part. I tried all kind of expression in the control source, but nothing works and I either bound it to the collumn and it doesn't calculate the due date, or I don't bound it, but have the calculation for the due date?
I figured to be able to count the dates which are due I will need to have that due date field somehow in a table with the original date taken field, so that I could end up with something like this:
Private Sub Form_Load()
'On Load of the switchboard check Dates table for any completed Dates
Dim intStore As Integer
'Count of complete dates that are past the Expected Completion Date
intStore = DCount("[DateID]", "[tblDates]",
"[DateDue] <=Now() AND [Complete] =0")
'If count of uncomplete dates is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
If intStore = 0 Then
Exit Sub
Else
If MsgBox("There are " & intStore & " uncompleted dates" & _
vbCrLf & vbCrLf & "Would you like to see these now?", _
vbYesNo, "You Have Uncomplete dates...") = vbYes Then
DoCmd.Minimize
DoCmd.OpenForm "frmReminders", acNormal
Else
Exit Sub
End If
End If
End Sub
If anyone has any suggestions I would really appreciate it.
Thank you for your time,
freak.
Jack Cowley
Generally speaking you do not save the values of a calculation but do the calculation 'on demand'. In your case you want to find records that are 3 years old, 6 years old, etc. A query will do that for you and then you can use your DCount() code to see if the count of records in the query >=1. If so, then you can go from there...
Create a query that uses the DateAdd() function to find the records that meet your requirements. Using DCount() of that query will tell you if have any records that are due...
hth,
Jack
freakazeud
Could you tell me step by step how to make the query, where to apply the dateadd code and so on?
I'm new to all this, sorry.
Thanks for your help and fast respons.
Freak.
Jack Cowley
You can use your DateAdd() function in the Criteria line of a query based on you table. In the column with the date use code similar to this:
lt;=DateAdd("yyyy",-3,Date())
This should get you started...
Jack
freakazeud
Ok jack I will try it over the weekend.
If I can't get it to work or need some other things I might contact you again.
Thanks so much for all your help.
Have a good weekend.
Oli.
Jack Cowley
Oli -
You are welcome and good luck!
Jack
freakazeud
Hi jack,
I have done what you said and created a query. This is how it looks.
I saved it as qryAesp, collumn one in the query is the CourseID from table tblcourseAespId, collumn 2 is the Course from the same table, collumn 3 is the date of the course taken from the same table with the following in the criteria: <=DateAdd("yyyy",-3,Date01()).
So where do I go from that. How to I implant the query in the code I had pasted up above earlier, so that I have a pop up window showing me the counted fields, which are due to refreshment training?
When I try to run the query it gives me the following error by the way: "Undefined Function "Date" in Expression!"
So the DateAdd function thing must be wrong in my date collumn?
Please let me know what I can do to fix all this.
Thanks in advance and I really appreciate all you help,
Oli.
Jack Cowley
Oli -
Y
Your DateAdd() function is wrong. Here is the correct syntax:
<=DateAdd("yyyy",-3, Date())
Now you can use DCount() or other function to see if this query is returning any records. If it is then open a form based on the query to show the people who are due for more training or whatever. Pseudo code might look something like this:
If DCount("*", "qryAesp") >= 1 Then
Select Case MsgBox("Trainings due. Do you want to see the list?", vbYesNo Or vbExclamation Or vbDefaultButton1, Application.Name)
Case vbYes
DoCmd.OpenForm "FormName"
Case vbNo
Exit Sub
End Select
End If
This code is an example and has not been tested...
hth,
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.