Auto calc Date field

[Tony O]

hi gang-have a question here regarding preferred method to do an autocalc in a date field when a new record is made.

I have an incident report database for a venue client (stadium). They have 12 events per year. I would like the field "Incident Date" in the db to autocalc to the nearest but not greater than event date. I've tried using a nested if statement but results have been, well, weird. I can post the statement for scrutinizing but before I do that would like to throw this out there in case someone else had done this and used a different tack. The calculation is pretty long, which is probably part of the problem, and I'd like to avoid the eyestrain if possible.

I did think I might use a startup script to set a global with a simpler or more logical calculation, then autocalc the date field with the global result. Anyone have any other ideas?

thanks in advance -

T

[-Queue-]

Are the incident dates similar? Are they all on a particular day of the month? What are the dates?

[Tony O]

12 dates, mostly Sunday, some Thursday and Monday, through the end of January. Basically, it's a football team's schedule.

I'm thinking I might- instead of using >= , specify a range instead, and am going to try that now.

T

[-Queue-]

If you have all the dates in another table, you could use the LookupNext function.

[Tony O]

If you have all the dates in another table, you could use the LookupNext function.

Awesome, That one never occured to me. Yes, the dates are in another table. Perfect! Thanks so much!

Tony