472,958 Members | 2,342 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

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

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
2 3073
MMcCarthy
14,534 Expert Mod 8TB
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
Thanks very much. I will try this
Apr 16 '07 #3

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

Similar topics

1
by: Steven Stewart | last post by:
I have a user who has been using Excel for a while to keep statistics and print reports. She finds using it cumbersome because of long formulas and a lot of copying and pasting. I have designed...
3
by: Mads Petersen | last post by:
I'm stuck in this code. Hope you can and will help me. I launch it from excel. I have made the following code work, but not as i whant. I need the ranges to be working with something like xlDown....
6
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is...
9
by: dba123 | last post by:
I need some help and direction on what classes and an example or two (article) on how to read an Excel Worksheet and insert one column into a database table column. I am using .NET 2.0 only. What...
5
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
2
by: LittlePhil via AccessMonster.com | last post by:
Someone please help before i start to cry. I'm trying to export from Access to Excel, then create a new excel sheet with a pivot table to display the data held in columns A:P. I get the error...
5
by: =?Utf-8?B?c2NobWlkdGU=?= | last post by:
Hi How can I Export an HTML Table to excel? My goal is a button, and when the user clicks this button a popup appears asking the user to 'open' or 'save' the generated Excel file. Actually...
7
by: TG | last post by:
hi! I am trying to create a sql server table from an excel sheet. Here is the code I have: 'This procedure the xlsx file and dumps it to a table in SQL Server
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.