vba code to claculate a date based on data in a field

[dopfer]

I'm working on an access 2007 data base to track suspensions in a high school.

I need to calculate how long a student will be exlcuded from school activities depending upon the date they return from suspension and the type of suspension they were on.

I have a field for their return date and a field for the type of suspension - In-School, Short or Long. If a student was on In-School then they are excluded for 3 weeks from their return date.

If they were on a short suspension then they are excluded for 5 weeks from their return date. If they were on a long suspension then they are excluded for 10 weeks. These time periods should also ignore weekends so it would be 15, 25 and 50 school days respectively.

I was thinking of putting the code behind a command button that says "Calculate Exclusion Date".

Thanks in advance.

[vb5prgrmr]

Hmmm... I read your requirements a couple of times and I think I/we are missing some information but I can give it a guess. You are wanting to calculate from todays date, based upon infraction severty, when the student should return...

Use the DateAdd Function (see help) to calculate this...



Good Luck

[dopfer]

Hmmm... I read your requirements a couple of times and I think I/we are missing some information but I can give it a guess. You are wanting to calculate from todays date, based upon infraction severty, when the student should return...

Use the DateAdd Function (see help) to calculate this...



Good Luck

I think my code needs to be something like:

If Susp_Type = In-School then add 3 weeks to the return date and put the result in a field called excluded until
ElseIf Susp_Type = Short then add 5 weeks to the return date and put the result in a field called excluded until
ElseIf Susp_Type = Long then add 10 weeks to the return date and put the result in a field called excluded until
EndIF

I'm not sure of the correct syntax for use with Access 2007 fields. Do I need to name the table that the fields are in? My DB only has one table currently.

[dopfer]

Just solved my problem. I used an expression in the field row of a query.

Excluded:IIf([SuspType]="In-School",DateAdd ("w",15,[ReturnDate]),IIf([SuspType]="Short",DateAd d("w",25,[ReturnDate]),IIf([SuspType]="Long",DateAdd ("w",50,[ReturnDate]))))

Thank you.