Calculating a Specific Date and day of week.

[PeteAJr]

I'm trying to make it possible for students to schedule appointments w/ staff via Web. I've gotten to the point where a student can search for available times, select and submit an appointment time. But the return info is not specific enough. The database is set up according to the weeks of the semester beginning each Monday(ie, Week 1=8/26/02, week2=9/2/02,etc) and the available appointments are set up according to the day of the week and the time. A record is created for each available time.

A typical record looks like:

Week:1, DayofWeek:T, Time:10-11a,Available:Yes,StudentID#:(blank),Name:(Blank).

A student can go to the website and search for available appointments (based on Available:Yes). Based on the above example, the result looks like:

On Tuesday during the week of 8/26/02 between 10-11a

10-11a is a link which when clicked allows the student to fill in StudID# which looks up his name, phone, and email which he can edit to update. Once he enters his StudID, Yes become No in the database. He then gets a response that says:

Jane Doe,

Your writing dropin appointment will be on Tuesday during the week of 8/26/02 (week 1 of the semester) between 10-11am.

My questions/problems: I would like to be able to return the actual date of the appointment in both the search and response web pages. I would like the search results to read:

Tuesday, August 27, 2002 between 10-11a

and the response result to read:

Jane Doe,

Your Writing DropIn Appointment will be on Tuesday, August 27,2002 between 10-11am.

I'm thinking a calculation using the Date function can work but I'm not sure how to format it. I have ideas: for tuesday: (weekdate(month, day +1, year)) given that the weekdate is 8/26/02 I'm hoping that the result will be 8/27 (day+1)/02

Has anyone done something like this before. Is there someone who can help me with this?

Thanks

[danjacoby]

The DayOfWeek function returns a number, from 1 (Sunday) through 7 (Saturday). Seems to me that this would be used in calculating the date -- add "DayOfWeek-1" to the Monday date and return a date ... I think; this is just off the top of my head (and there's less up there every day).

[PeteAJr]

Well, after a long day of trial and error (and the book: Database Design and Publishing by Jeff Gagne and Donn Crabb,M&T Books), I figured it out. I created a few more fields one of which was a DATE(date, month, year) field. I assigned numeric values to the days of the week: 1=Mon, 2=Tues, etc. I then created a calculated field called apptdate and used the CASE function. Here's the calculation:

Case(Day = 1, Date(wkmo, wkday, wkyr), Day = 2, Date(wkmo, wkday + 1, wkyr) , Day = 3, Date(wkmo, wkday + 2, wkyr), Day = 4, Date(wkmo, wkday + 3, wkyr) , Day = 5, Date(wkmo, wkday + 4, wkyr) ,"")

In my database, every week starts with Monday's date, so Day 1 results in Monday's date, Day 2 results in Tuesday's date (Monday's date +1), etc.

I'm sure there probably was an easier way but this works!

[Keith M. Davie]

I had not looked at this until your post about the trouble you had and the rather elaborate solution. I went to a db I use which had a date field in it. I tried to get a number to appear and was having no success. Then I took my own good advice and went to FileMaker TechInfo. Did a search on dayofweek. Found a few titles (my searches usually find many). One of them helped me resolve the how to get the number to appear.

http://www.filemaker.com/ti/103766.html

All told it took about 15 minutes.

[kennedy]

Case(Day = 1, Date(wkmo, wkday, wkyr), Day = 2, Date(wkmo, wkday + 1, wkyr) , Day = 3, Date(wkmo, wkday + 2, wkyr), Day = 4, Date(wkmo, wkday + 3, wkyr) , Day = 5, Date(wkmo, wkday + 4, wkyr) ,"")

Just a thought:

apptdate = gStartOfSemester + (7 * Weeks) + Day

where gStartOfSemester is a global holding the Sunday prior to the zero'th week of the semester.

HTH.