DateAdd + Days

[afrosheen]

I have a table that has several fields however the ones I'm working with are Date, Letter, RollOff.

Date being the date the record was entered example 6/1/2006

Letter can be letter codes simply a,b,c,d,e etc

RollOff is 1 year from the Date the record was entered.

I've searched though several pages of dateadd info but haven't ran across an exact similar situation so far.

Getting the RollOff is no problem, simply DateAdd("yyyy",1, [Date]). However what I'm attempting to do is I need to add a certain amount of days to the calc between the date entered and the 1 year rolloff that match the Letter of "B".

Each record entered will only have 1 Letter and each entry is only for 1 day.

I'm thinking I will need a second query that will gather the sum of "B" codes to determine the amount of days needed to add to my DateAdd calc.

sample layout would be.

Date Letter RollOff
6/1/2006 A DateAdd("yyyy", 1, [Date]) + 3 days of B Codes for 6/2,3,4
6/2/2006 B
6/3/2006 B
6/4/2006 B
6/5/2006 C

Since 6/1/2006 RollOff is 1 year 6/1/2007 however since there was "B" enteries between the time the record was entered and the RollOff date the amount of days needed to be added so the RollOff would actually be 6/4/2007.

Any help would be appreciated.

 

[afrosheen]

suggestions??

Anyone have any suggestions to this issue?

 

[namliam]

Do you allways have a consequtive A B C D etc? How far does it go?

you can use Chr(Asc([Letter]) +1) to change the A into a B, and a B into a C.

then use some "smart" query to count how many there are and how many days to add and such....

 

[afrosheen]

These will always be a mixture of Letter Codes and could possbile be a to z. I do not need to make any changes to the Letter Codes.

A better example would be:

Date ------ Letter------ RollOff
6/1/2006 ------A------- DateAdd("yyyy", 1, [Date]) + 4 days of B Codes for 6/2,6/3,6/4,6/25
6/2/2006 ------B------- DateAdd("yyyy", 1, [Date]) + 4 days of B Codes for 6/2,6/3,6/4,6/25
6/3/2006 ------B------- DateAdd("yyyy", 1, [Date]) + 3 days of B Codes for 6/4,6/25
6/4/2006 ------B------- DateAdd("yyyy", 1, [Date]) + 2 days of B Codes for 6/4,6/25
6/5/2006 ------C------- DateAdd("yyyy", 1, [Date]) + 1 days of B Codes for 6/25
6/6/2006 ------D------- DateAdd("yyyy", 1, [Date]) + 1 days of B Codes for 6/25
6/12/2006 ------A------ DateAdd("yyyy", 1, [Date]) + 1 days of B Codes for 6/25
6/19/2006 ------C------ DateAdd("yyyy", 1, [Date]) + 1 days of B Codes for 6/25
6/25/2006 ------B------ DateAdd("yyyy", 1, [Date]) + 1 days of B Codes for 6/25
6/30/2006 ------C------ DateAdd("yyyy", 1, [Date]) + 1 days of B Codes for 6/25

As you can see I'm trying to calc the RollOff Date which is always 1 year from the Date of the record, however anytime there is a B code between the Date of the record and the default RollOff (1 year) this will add 1 day to the calc. I can easily get a query to give me to Sum of "B" codes, but I can't seem to break down each entry's RollOff.

If you could elaborate more on the "smart" query you might be on the right track.

Thanks greatly.

 

[namliam]

Is your logic indeed "simply add any Bs with a Date >= the current record" ?
If so... try this... It will not be fast and its not pretty....
DCount("*","YourTable","[Letter]='B' and [Date]>=" & [Date])

You use this for the + part of your examples.... i.e.


P.S. I sure hope that you didnt use Date as your actual field name...

Date ------ Letter------ RollOff
6/1/2006 ------A------- DateAdd("yyyy", 1, [Date]) + DCount("*","YourTable","[Letter]='B' and [Date]>=" & [Date])

Where that DCount should return 4 in you example...

Hope it helps...

 

[afrosheen]

DateAdd

Yep that's the logic needed is simply add all B's to the RollOff date. I shall give this a go tomorrow, I have also came up with a solution that seems to work with about 4 different queries while i'll post tomorrow.