Appointment schedule - with a twist

[Guest]

I'm trying to attempt really hard at the moment...I have been asked to create a database that schedules appointments. The database is for a major research study in which participants have time periods between each visit and the study runs over 5 years. They also have 7 days either side of the set date in which they can attend their visit. The users want a program that calculates 3 months in advance the visit dates and creates an appointment record for each visit with the date and time of the appointment. However, they must be able to adjust this record if the participant can't attend the appointment on that day. I'm not really worrying with calculating the visits in advance and creating records yet but I am trying to figure out how to check that the user hasn't scheduled an appointment on a day that the place isn't open - this is the twist...not only do I need to cut out weekends, but also whichever day is entered as a day the place is closed...in other words, the place may be closed every wednesday and weekdays - how can I cut these days out as potential appointment dates.

I have a drop down calendar from which they can pick or type in a date for an appointment but I need to prevent them from selecting a weekend date/public holiday/day when the place is closed. As this program is going to be used by a number of different studies...the day that the place is closed on, can vary. Weekends are always out but it may be in Study A that Wednesday is the closed day or in Study B that Friday is the closed day

How can I set up some process that checks the date selected and makes sure it is a viable date/day and that I will be able to adjust to suit the various studies

Any help will be greatly appreciated...I have programming experience but not in VB and I don't know if there is some way to check in a table of dates, what day each date is

Thanks in advance

 

[Arvin Meyer]

There are 2 ways you can do it. If it is always a certain day you can check
(in code) after picking the date and inform the user that the date must be
changed. Or, if you can't find a regular pattern, you can use a table to
lookup and see if the date is there. Holidays are a good example for this
method. You can also use both of the methods together. Here is some sample
to start with.

http://www.datastrat.com/Code/GetBusinessDay.txt

The code is for adding n days to a start date, but you can easily adapt it
by adding another date constant to the weekend days (vbSaturday and
vbSunday) that are already there.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I'm trying to attempt really hard at the moment...I have been asked to

create a database that schedules appointments. The database is for a major
research study in which participants have time periods between each visit
and the study runs over 5 years. They also have 7 days either side of the
set date in which they can attend their visit. The users want a program
that calculates 3 months in advance the visit dates and creates an
appointment record for each visit with the date and time of the appointment.
However, they must be able to adjust this record if the participant can't
attend the appointment on that day. I'm not really worrying with
calculating the visits in advance and creating records yet but I am trying
to figure out how to check that the user hasn't scheduled an appointment on
a day that the place isn't open - this is the twist...not only do I need to
cut out weekends, but also whichever day is entered as a day the place is
closed...in other words, the place may be closed every wednesday and
weekdays - how can I cut these days out as potential appointment dates.

I have a drop down calendar from which they can pick or type in a date for

an appointment but I need to prevent them from selecting a weekend
date/public holiday/day when the place is closed. As this program is going
to be used by a number of different studies...the day that the place is
closed on, can vary. Weekends are always out but it may be in Study A that
Wednesday is the closed day or in Study B that Friday is the closed day.

How can I set up some process that checks the date selected and makes sure

it is a viable date/day and that I will be able to adjust to suit the
various studies?

Any help will be greatly appreciated...I have programming experience but

not in VB and I don't know if there is some way to check in a table of
dates, what day each date is.

 

[Guest]

Thanks Arvin, I'll try use your code for finding dates...my idea is to have a table of holidays and hopefully have set days that the place is closed and be able to check that. Any idea how I can allow the users to enter the day that their place is closed (in a form) and then use that day as the day that must be crossed off with the sunday and saturday

I'm not sure how the vbSunday and vbSaturday work - are they predefined in vb and if so, can i use vbWednesday for example?

 

[Arvin Meyer]

Thanks Arvin, I'll try use your code for finding dates...my idea is to

have a table of holidays and hopefully have set days that the place is
closed and be able to check that. Any idea how I can allow the users to
enter the day that their place is closed (in a form) and then use that day
as the day that must be crossed off with the sunday and saturday?

Use a variable when you call the added day. I'd use either a textbox or
better still an Option Group Frame (MyFrame) to select the day of the week.
Something like:

I'm not sure how the vbSunday and vbSaturday work - are they predefined in

vb and if so, can i use vbWednesday for example?

Exactly. 1 = vbSunday, 2 = vbMonday, etc. so using a Frame whose values are
typically 1, 2, 3 ... works perfectly. When in doubt about what a constant
is, select it in the code window, then right-click and choose definition.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Brian Kastel]

I had to write something similar a number of years ago for a database that
processed EFT transactions. The bank's non-business days had to be excluded
for purposes of time tracking (e.g., hold for 3 business days). On my own,
I developed a rather elegant solution, but I'm certain it is far from the
simplest.

I created a bit string for a year, and saved each specified year as a single
record in a business days table. To add or edit the bit strings, I created
a monster calendar form with zillions of check boxes (since Access doesn't
have control arrays, this was no small feat) and a combo box that listed the
years already in the table. Entering a year in the combo rearranged the
calendar for that year, and if a record existed for that year, checked the
non-business day checkboxes. If no record existed for the specified year,
then the checkboxes would all be cleared (except for weekends), and a new
record would be saved with the bit string defined by the calendar layout.

Using the Format date functions, you can determine what day of the year a
certain day is, then do an simulated bitwise And on the string for that
position. A 1 indicates a day off, while a 0 indicates a business day.

This may be the only workable way to deal with the problem, since there may
very well be days off that have no relationship to weekends or national
holidays.

I'm trying to attempt really hard at the moment...I have been asked to

create a database that schedules appointments. The database is for a major
research study in which participants have time periods between each visit
and the study runs over 5 years. They also have 7 days either side of the
set date in which they can attend their visit. The users want a program
that calculates 3 months in advance the visit dates and creates an
appointment record for each visit with the date and time of the appointment.
However, they must be able to adjust this record if the participant can't
attend the appointment on that day. I'm not really worrying with
calculating the visits in advance and creating records yet but I am trying
to figure out how to check that the user hasn't scheduled an appointment on
a day that the place isn't open - this is the twist...not only do I need to
cut out weekends, but also whichever day is entered as a day the place is
closed...in other words, the place may be closed every wednesday and
weekdays - how can I cut these days out as potential appointment dates.

I have a drop down calendar from which they can pick or type in a date for

an appointment but I need to prevent them from selecting a weekend
date/public holiday/day when the place is closed. As this program is going
to be used by a number of different studies...the day that the place is
closed on, can vary. Weekends are always out but it may be in Study A that
Wednesday is the closed day or in Study B that Friday is the closed day.

How can I set up some process that checks the date selected and makes sure

it is a viable date/day and that I will be able to adjust to suit the
various studies?

Any help will be greatly appreciated...I have programming experience but

not in VB and I don't know if there is some way to check in a table of
dates, what day each date is.