Cleanest way to do this criteria

[glasgowlad1999]

Hi All,

I would like your thoughts on how you would write a formual or VBA to handle this date needed.

I have 3 fields ("tblMedSubmit" Title: T21 or T19, Requested Type: Urgent, Standard, Inpatient or Retro, and Med3Date: a date field )

I need to show a date based on a criteria:

For Title T21

Urgent I need to show + 72 hours
Standard I need to show + 15 days
Inpatient I need to show + 24 hours


Retro I need to show + 30 days

For Title T19

Urgent I need to show + 72 hours
Standard I need to show + 14 days

With each of these I need to make sure I don't count weekends and holidays (tblHoliday), so if it is due on a Monday we get it to display the Friday date.

Thanks for your views and hopefully help on this.

[ajetrumpet]

if you want to exclude holidays, you have to use a call to a function. none of the date functions in access exclude weekends and holidays. so the call would be included on one side of the criteria, most likely on the right side of the BETWEEN statement.

these functions are pretty long too. I've written one myself, and so have others. are you sure there isn't another way you can do it?

[glasgowlad1999]

Adam,

Ok lets forgot the weekends and holidays. How do I do the first section?

[ajetrumpet]

Adam,

Ok lets forgot the weekends and holidays. How do I do the first section?

define "first section". this does make sense:

Urgent I need to show + 72 hours
Standard I need to show + 15 days
Inpatient I need to show + 24 hours
Retro I need to show + 30 days

what does all of that mean? e.g. - return records with criteria of "standard" in one field that have datefields of more than 15 days from now? I have no idea.

[glasgowlad1999]

Ok I have a drop down that you can pick T19 or T21, and other drop down that have Urgent,Standard, Inpatient and Retro. I have a date field.

If they select T19, Urgent I need to take the date field and + 72 hours and put it in its own field

[ajetrumpet]

OK, then all you have to do is issue a couple of nested SELECT CASE statements in code behind an event. either that, or a select case and a small IF inside of each one. inside the conditionals, you will use the built-in date functions. Most of them will be dateadd() I would assume, right? You can find the syntax and criterias for using times and days and other period lengths in the vba help menu under that function.