By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,873 Members | 1,034 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,873 IT Pros & Developers. It's quick & easy.

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

P: 20
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.
Apr 12 '07 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Ok, this may need some adapting. If the date passed is a Friday it should return the number of Fridays in that month.

Expand|Select|Wrap|Line Numbers
  1. Function checkDays (chkDate As Date)
  2. ' Function to check the number of days in the month corresponding to this date
  3. Dim tmpDay As Integer
  4. Dim firstDayMth As Integer
  5. Dim tmpMth As Integer
  6. Dim mthDays As Integer
  7. Dim tmpYr As Integer
  8. Dim numDays As Integer
  9. Dim i As Integer
  10.  
  11.    tmpDay = Weekday(chkDate)
  12.    tmpMth = Month(chkDate)
  13.    tmpYr = Year(chkDate)
  14.  
  15.    firstDayMth = Weekday(DateSerial(tmpYr, tmpMth, 1)) ' what day does first day of the month fall on
  16.    If firstDayMth = tmpDay Then i = 1
  17.    ElseIf firstDayMth < tmpDay Then i = tmpDay-firstDayMth
  18.    Else 
  19.       i = firstDayMth-tmpDay
  20.    End If
  21.  
  22.    ' find total number of days in the month 
  23.    If tmpMth IN (4,6,9,11) Then mthDays = 30
  24.    ElseIf tmpMth IN (1,3,5,7,8,10,12) Then mthDays = 31
  25.    ElseIf tmpMth = 2 Then mthDays = DatePart("d", DateSerial(tmpYr, 3, 1)-1)
  26.  
  27.    Do While i <= mthDays
  28.       numDays = numDays + 1
  29.       i = i + 7
  30.    Loop
  31.  
  32.    checkDays = numDays
  33.  
  34. End Function
  35.  
Mary
Apr 13 '07 #2

P: 20
Thanks very much. I will try this
Apr 16 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.