Automatic year month day with user entered time

[Tvanduzee]

Hello
Im setting up schedule times (8 AM till 9 PM) in increments of 30 minutes.
In my table, I have start time and end time. As some of you may already know, the date reverts to 1899 for the year.


Because each year the Dates for my event will change, I cannot fill the table with Year, Month, Day.
On my form, I have cbo's to select the start and end time for shifts (values come from the tblTimes table). When I attempt to calculate the duration, it always ends up with about 150 days (depending on the times I select) (Im assuming it is days) -- (End time - Start time) * 24. I noticed it was calculating in the year 1899. So, I need, somehow to change the default year month and day at the form level to the dates of the event, so when the user selects the time, it will be for 1 of 3 days (event is always on Fri, sat, sun). I say at the form level so that this year I can still run off a report on last years event.

Ex:
This year:
Friday, September 11 was the start of the event, and Sunday, September 13 was the last day.
Next year, I do not know what the date will be, but the end user will simply need to schedule people based on the day (Fri, sat or sun) and not have to enter the year, Month (which should be put in automatically based on an input field or something.

Im sorry if I have not articulated this very well, it seems more difficult to explain than what I thought it would be.

Thank You
Terry

[Perceptus]

Many times I solve my own issues by just typing out what the issue is on the these forums. Getting all my thoughts out and organizing them does wonders.

[June7]

Need better understanding of data structure. If you want to provide db for analysis, follow instructions at bottom of my post.

[Tvanduzee]

Need better understanding of data structure. If you want to provide db for analysis, follow instructions at bottom of my post.

I have no data in the database except the times in the tblTimes table.
I have started a form for TeamLeads which each person in that table for the current year will be scheduled (start and end times). I need to calculate the duration so that I can explore making a gaant chart for it.. so we can see visually where we might be short handed in any particular area (there are 11 areas).

Thank You
Terry

[June7]

What is this database for? I am confused by the data structure. Some dummy records would be helpful.

Multiple similar fields (Area, Area2, Area3) is not a normalized data structure.

Why is tblAreas linked to both tblTeamLeads and tblH4MYear? This can be problematic - review http://www.codeproject.com/Articles/...atabase-Design

Tables should be linked on primary key/foreign key fields. You have autonumber ID designated as primary key in several tables yet you link on other fields.

All of the relationships are 1-to-1.