Days of Week Calc to AutoInsert into table then export to excel preformat sheet

[tasmontique]

I am working on an access 2002 flight schedule database. I am new to access but have some basic understanding of sql and vb6 code.

I have learned a lot from this website. Thanks much

Hopefully you can help me with this one.

This database handles a varying number of flights for a varying number of days for a varying number of months.

I have a number of tables as follows. I tried to normalize to the best of my ability.

tblflightadhoc
PK FlightADID
AirlineCode
FlightNuminout
ArrivalTime
DepartureTime
RouteFrom
RouteTo
Equipment
Program
Remarks

tblflightcharter
PK FlightCHID
AirlineCode
FlightNuminout
ArrivalTime
DepartureTime
RouteFrom
RouteTo
Equipment
Program
Remarks

tblflightservice
PKFlightServiceID
FKFlightADID
FKFlightCHID
FlightService

These are the three main tables. However I have also constructed tables
for each month of the year with 5 weeks eg.....

tblJandays

PKJanID
FKFlightADID
FKFlightCHID
Monthname-January'default'
Program-tells me which flightprogram eg adhoc,charter.
Week1
M
T
W
T
F
S
S
Week 2 the weeks continue to 5 weeks consisting of 7 days.

The dates are very random based on the dates and the year a flight can operate. eg a flight might operate on Monday in Jan 2 times because that particular Monday based on year only consists of 2 Mondays or vice versa based on random dates and days a flight can operate for 5 Mondays in January.

My one of many issues is. The data entry for this becomes very time consuming. That is, If FlightAdhoc operates on Sundays between April 2 to August 19th. My current design only allows data entry by manually entering theses dates to be stored in the monthdays tables or if FlightCharter operates on Sunday/Thursday/Saturday then manual entry is required.

I am currently using a calendar so that it pops up by the textbox and
autopopulates

QUESTION Is there a way in access or maybe by means of an external program that communicates with forms built in access to autopopulate these fields so that the data entry can be automatically stored in table.

txtbox frequencyday=Mon,Thurs,Wed
txbox frequencymonthstart= March 27 2007
txtbox frequencymonthend = October 23 2007

I am aware that the DatePart() function is able to return DAY of week based on value entered.

Can You point me in the right direction for a way in VB to design a function that
checks for day of week
month of year
number of days in month of year eg the number of Saturdays, Mondays in the month.

Then after checking for these values I can use an sql statement to insert into tblJandays based on month' number daysof week in month ,FlightID (I am trying to change PK to this but having problems concatenating string airlinecode/flightnum) automatically.

Finally, I know this post is very long, export the data captured in access to a preformatted excel file.

[MMcCarthy]

Ok, this may need some adapting. If the date passed is a Friday it should return the number of Fridays in that month.

Function checkDays (chkDate As Date)
' Function to check the number of days in the month corresponding to this date
Dim tmpDay As Integer
Dim firstDayMth As Integer
Dim tmpMth As Integer
Dim mthDays As Integer
Dim tmpYr As Integer
Dim numDays As Integer
Dim i As Integer
 
   tmpDay = Weekday(chkDate)
   tmpMth = Month(chkDate)
   tmpYr = Year(chkDate)
 
   firstDayMth = Weekday(DateSerial(tmpYr, tmpMth, 1)) ' what day does first day of the month fall on
   If firstDayMth = tmpDay Then i = 1
   ElseIf firstDayMth < tmpDay Then i = tmpDay-firstDayMth
   Else 
      i = firstDayMth-tmpDay
   End If
 
   ' find total number of days in the month 
   If tmpMth IN (4,6,9,11) Then mthDays = 30
   ElseIf tmpMth IN (1,3,5,7,8,10,12) Then mthDays = 31
   ElseIf tmpMth = 2 Then mthDays = DatePart("d", DateSerial(tmpYr, 3, 1)-1)
 
   Do While i <= mthDays
      numDays = numDays + 1
      i = i + 7
   Loop
 
   checkDays = numDays
 
End Function
 

Mary

[tasmontique]

Thanks very much. I will try this