Query: override date formula?

[marthacasting]

I have a table that lists Event locations and dates. From that table I created a query that has that same information PLUS a new field that calculates the date that applications for that event need to be in. So if the event is June 1, the application is due 45 days prior to that. I made the field EventDate: [EventDate]-45, which works just fine for all 40 events (each with different event dates and thus different application due dates). BUT, my question is, for a few of the events, the application deadline date is NOT based on 45 days, but rather is based on special dates (maybe 30 days or maybe 50 days prior to the event). So is there a way that for those few exceptions, I can override my "formula" and put in the exception dates? I print reports off this query at least once a week, so it would be helpful to have the accurate dates.
THANK YOU!

 

[Isskint]

You could put an input parameter in your query for the user to input the no of days for the formula, like;

[EventDate]-Val([Enter Notification period]) - This will produce a prompt asking for the Notification period number of days.

The way i would approach this, is with an extra field called NotificationPeriod with a default of 45. Whenever the NotificationPeriod is different the user can overwrite this at the time of input. Your Query would then use [EventDate]-[NotificationPeriod])

 

[marthacasting]

Thank you for your prompt reply. I was remiss in not prefacing my question by stating that I am a self-taught Access user! (There was no one at my position to teach me!). Sooooo, could you try to maybe walk me through that a little more basically?
Thank you.

 

[Isskint]

No problem.

Lets start with the ParameterQuery.
A parameter query is a query where, when it runs, the user is prompted for a value used in the query. It could be a criteria or part of a calculated field. The square brackets [ ] tell Access the item in the brackets is an object (normally a field or table name) but if it can not find that object - in this case a field - it will prompt for the value.
In your case you already have the extra field in the query new field that calculates the date that applications for that event need to be in all you need do is make the number of days to be deducted, a parameter so you are prompted for this vlaue whenever the query runs. So change your formula from [EventDate]-45 to [EventDate]-Val([Enter Notification period]). The only problem with this approach is that you will be prompted for EACH record in the table!

My approach to this.

1. In the Events table, add another field;
   • Name = NotificationPeriod
   • Type = Number - Long Integer
   • Default Value = 45
     • Whenever you add a new event, the NotificationPeriod will automatically be recorded as 45.
2. Add this field to any forms you use to interact with the Events table. Now when a new event is added (or you are editing an existing event) the user can overwrite that value of 45 if a special date is required.
3. In the query replace [EventDate]-45 with [EventDate]-[NotificationPeriod]

Now the query will not need to prompt for the NotificationPeriod because it is a supplied field form the table.

 

[marthacasting]

Oh my goodness! THANK YOU, THANK YOU!!
I can't tell you how much I appreciate your help!