Repeating dates, DateAdd/DatePart

[Lauren1989]

First of all a big thank you for the help last week. I am somewhat ashamed to be back so soon! Last week I was trying to figure out how to make a query where I could enter the month and the results would show up based on the Last Review Date and Freqency in my table. (Frequency here is going to be either 3, 6, or 12 depending on if the accounts get reviewed quarterly, semi-annually or yearly.) Here is what was suggested:

First query:

NextReview: DateAdd("m",[strFrequency],[dtmLastReview])

NRMonth: DatePart("m",[NextReview])

NRYear: DatePart("yyyy",[NextReview])

Parameter query: (using query 1)
NRMonth
Criteria: [Which Month?]


NRYear
Criteria: [Which Year?]

This works beautifully however I think I need to tweak it a little. Sometimes an account will not get reviewed when it is supposed to. In order for it to show up on the next date it should get reviewed I would need to change the last review date (which I would prefer not to do as I would like this to be accurate). How can I rework the query so that the frequency repeats? IE- Last Review Date 1-1-2010 will show up in query for 4-1-2010, 7-1-2010, 10-1-2010 regardless of if the April gets missed. I tried just taking the NR Year out but they wouldn't repeat going forward. I think the trick is to just get it going off the month but I'm not sure how to do this. Any help would be greatly appreciated.

[kennejd]

I think you just need to change the criteria in your query. Instead of '= [Which Month]', try '<= [Which Month]. Do the same thing with year.

[Lauren1989]

That works in terms of the 3 and 6 month frequencies but it made the 12s go haywire. In a query for next March I have all different months coming up under NR Month (all with a 12 frequency).

[kennejd]

It might be easier to base your due dates on the NextReview field. Prompt [Enter due date mm/dd/yyyy]. Otherwise you might have to jump through some big hoops. If you want to stick with the month/year prompts then maybe look at an iif function.

[Lauren1989]

I am unfortunately still stuck. Does anyone else have any ideas? If not, what type of iif function?

I'm also considering just adding another field to my table splitting it into "Actual" Last Review Date and "Review Date to Trick the Computer Into Putting on Query" but the database is on a network and I'm afraid this would be confusing for others. So if there's another solution that would be much preferred.

[Lauren1989]

After much experimenting I finally solved the problem on my own! Wow, that feels good. In case anyone else should need a solution here is what I did:

Query 1 SQL:

SELECT tblClientsAll.strHousehold, tblClientsAll.strLNamePrime, tblClientsAll.dtmLastReview, tblClientsAll.strFrequency, DateAdd("m",[strFrequency],[dtmLastReview]) AS NextReview, DatePart("m",[NextReview]) AS NRMonth, DatePart("yyyy",[NextReview]) AS NRYear, DateAdd("m",[strFrequency],[NextReview]) AS NextReview2, DatePart("m",[NextReview2]) AS NR2Month, DatePart("yyyy",[NextReview2]) AS NR2Year, DateAdd("m",[strFrequency],[NextReview2]) AS NextReview3, DatePart("m",[NextReview3]) AS NR3Month, DatePart("yyyy",[NextReview3]) AS NR3Year, DateAdd("m",[strFrequency],[NextReview3]) AS NextReview4, DatePart("m",[NextReview4]) AS NR4Month, DatePart("yyyy",[NextReview4]) AS NR4Year, DateAdd("m",[strFrequency],[NextReview4]) AS NextReview5, DatePart("m",[NextReview5]) AS NR5Month, DatePart("yyyy",[NextReview5]) AS NR5Year
FROM tblClientsAll
WHERE (((tblClientsAll.ysnAccountReview)=-1))
ORDER BY tblClientsAll.strLNamePrime;

Query 2 SQL:

SELECT [Acct Review A].strLNamePrime, tblClientsAll.strFNamePrime, [Acct Review A].dtmLastReview, [Acct Review A].strFrequency
FROM [Acct Review A] INNER JOIN tblClientsAll ON [Acct Review A].strHousehold = tblClientsAll.strHousehold
WHERE ((([Acct Review A].NRMonth)=[Which Month?]) AND (([Acct Review A].NRYear)=[Which Year?])) OR ((([Acct Review A].NR2Month)=[Which Month?]) AND (([Acct Review A].NR2Year)=[Which Year?])) OR ((([Acct Review A].NR3Month)=[Which Month?]) AND (([Acct Review A].NR3Year)=[Which Year?])) OR ((([Acct Review A].NR4Month)=[Which Month?]) AND (([Acct Review A].NR4Year)=[Which Year?])) OR ((([Acct Review A].NR5Month)=[Which Month?]) AND (([Acct Review A].NR5Year)=[Which Year?]));

In the second query (my parameter query) I just uncheck all next review months and years for a clean interface. And problem is solved!