Calculate Days Between Dates & Other Criteria

[TClark14]

I have a database with 10-12 fields containing dates certain things were completed. There is also a field for the 3 different locations we have and a program year 2006, 2007, or 2008. What they would like to do is click a button and get a list to choose the first date they which to compare, then a list to choose the second date, calculate the number of days between the two dates, select the program year from a list, and a location from a list. Can anyone help me with how to do this if it's possible?

The fields look something like this:

Prog Year Location DateA Date B DateC DateE
2006 G date date date date
2006 G date date date date
2006 P date date date date
2008 D date date date date
2007 P date date date date

For Example, I might want 2006 Program Year, Location P, calculating days between DateA and DateB, except that the next time I might want to choose a different set of criteria.

Any ideas?

THANK YOU!

 

[ajetrumpet]

Clark, all of this...

I have a database with 10-12 fields containing dates certain things were completed. There is also a field for the 3 different locations we have and a program year 2006, 2007, or 2008. What they would like to do is click a button and get a list to choose the first date they which to compare, then a list to choose the second date, calculate the number of days between the two dates, select the program year from a list, and a location from a list.

...which can be narrowed down to this...

For Example, I might want 2006 Program Year, Location P, calculating days between DateA and DateB, except that the next time I might want to choose a different set of criteria.

...is, in fact, quite complicated. I would not attempt it in a query though, because of the variation in possible output data. I would do it in a form, with controls displaying the output you want. I would also write some VB code for this, as it would probably better compliment the various criteria you are going to be specifying. Does that sound too scary? It's not really, it just takes a bit of work, that's all...

 

[TClark14]

Thank you for the response. Actually it does sound scary, however; a lot of work I don't mind. I'm just not sure I follow what you are saying to do in the form.

Thanks!

 

[ajetrumpet]

I'm just not sure I follow what you are saying to do in the form.

Well, I'm not sure how to initiate this then, because I don't know how much you know about the program, and the functions that are available to you. I guess I see a couple of possibilties here:

1) You can try it on your own, and post back for help.
2) I can upload a sample that shows some syntax that you might want to use.

It's really up to you now...

 

[TClark14]

I would appreciate it if you would upload a sample. I have used Access quite a bit, but there is so much I don't know - I am still learning. If you upload a sample, that will give me a starting point anyway and possibly I can work my way through it.

Thanks for your help!

 

[neileg]

It's scary because your design is not normalised. It would be quite simple if the dates were held as individual records in a related table instead of a repeating group.

 

[ted.martin]

They key to this may well be to use a calendar embedded within a form to get the dates, then use some SQL code to do the calculation and take the next step. SQL is not so easy but one thing is for certain, you will learn a lot thoguh making it work.

In the attached, you call the pop-up form FP-Dates and afterwards you have the myStartDate and myEndDate. The SQL part is the hard bit. Try designing a query first with some specified date e.g. #01/01/07# to #30/11/07#; view the SQL view and then strip this code into a module replacing the above dates with some SQL that is may be like as follows:

myStartDate = MakeUSDate(myStartDate) ' - need this as SQL runs in US date format
myEndDate = MakeUSDate(myEndDate)

Dim mySQL as String

mySQL = "UPDATE Patients SET Patients.ReminderOption = True, Patients.[SecondReminder] = Null " & _
"WHERE (((Patients.NextVisit) >= " & myStartDate & " And (Patients.NextVisit) <= " & myEndDate & " " & _
"AND ((Patients.Status)='Current')));"

DoCmd.RunSQL mySQL

 

[ajetrumpet]

Clark,

Maybe take a look at Ted's example. I haven't looked at it, but maybe it can help. To put together an easy search form, using the data you have now, would involve some techniques that are not desirable, or even worthwhile for programmers to use. Neil is right in terms of normalized data. It would be much easier for you if you had your tasks (related to the completion dates) in a separate table so you could get the values in a columnar form. The structure now looks a bit more like a spreadsheet layout.

I have a sample for you, but it might be difficult to follow, and it's certainly not efficient, because of the data structure you have posted. Do you want to try and change this first? If not, I'll just post the sample, and you can try to "disect" it. If you see it thought, you might change your mind about your data structure!