Recurring Event, can't seem to get it right

[Stubbs]

Hi Everyone
I need some help with what should be a simple query, but I can't seem to pull it off. I have to be able to specify a record every 90 days from its start date, which is ReportDate in my table. What I tried was enterring separate expressions, [ReportDate]+90, [ReportDate]+180, etc in the query, but some of my records may be on hand for 5 years or more and that would be an excessive amount of expressions to be enterring into the query, I think. Isn't there a better way to show each 90 days than what I was going to do? I also need to reference each 90 day mark for a report. Of course, the report date is totally different for each record, otherwise it would be easier. Any suggestions will be appreciated, thanks in advance.

 

[RichO]

Give a little more detail. When you say "specify a record" what exactly do you mean? Do you want to query ALL records where the report date is either 90,180,270... days old?

This can be done with VBA and SQL. Just need a little more information to proceed. I'm thinking something like...

SQL = "SELECT * FROM MyTable WHERE "
For i = 90 to 1800 Step 90 ' every 90 days for about 5 years
SQL = SQL & "MyTable.ReportDate = #"
SQL = SQL & DateAdd("d", Date, -i) & "# "
If i < 1800 Then SQL = SQL & " or "
Next i

This will build a SQL statement that will compare the report date to the date 90 days prior to today, 180 days prior to today..., all the way back for 5 years.

I did not test this code, just typed it on the fly but I think it would do the trick.

 

[Stubbs]

More Detail

Hey RichO
Thanks for the reply. What I mean is I have personnel check in whenever. There is no set date or time when they arrive, all depends on how they are billeted to me. What I need to do for each person is to conduct an evaluation on them each 90 days from the day they report for the duration of their tour with me, which is generally anywhere from 3 to 5 years. I want to be able to have my database tell me when they are due for a eval, preferably like a month or so in advance as I currently have over 100 personnel to deal with and anticipate being closer to 250 by the end of the year. Everything else I have all ready figured out, I just can't seem to get how to figure out the recurring 90 days. Thanks for the look.

 

[Stubbs]

Expediant workaround, ain't pretty but it worked

Hey Guys
Thanks for your help, I do appreciate it. What I ended up doing, as my supervisor was climbing up my butt to get this done, was make a select query with DateAdd expressions for the 90, 180 and 270 days only, then formatted them to only show days and months, then made a make table query using DartPart expressions to get the month value and matched those to a list box on the form to pull the records for the desired month. Like I said, it ain't pretty, but it works. Once I have a couple of free minutes, I intend on trying to work your suggestions into the database. Again, thanks.
Dean