how to make calender for 365 days

I am a beginner in MS Access and using MS Access 2000. I have slowly and consistently learning the correct ways from the people like you. Now, for one of my database on Workers/Staffs Attendance, I need to fill one of a field with date from 1 Jan XXXX (now 2017) to 31 Dec XXXX.
3-jan-2017 on..

I need help to create query to automatically fill that filed with continuous date and next field with weekdays (sunday, monday..etc).
Please help me with sample coding and if any other easy steps are there, then let me know.

arul mozhiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  Usually pre-filling a table with records is a bad idea.   If your tracking attendance, you would simply record what days they attended.  Then when reporting, figure out if they had a day for that record or not.

Rey Obrero (Capricorn1)Commented:
see this sample db, it fills the table tblDates by running the sub populateTableDate
I agree with Jim.  It doesn't make any sense to prefill a table with empty attendance records.  it is especially bad when you know that at least 25% of them will NEVER be used.  Why put in a record for every day of the month when people only work 5 days a week.

The example Rey posted was simplistic and it just adds one record per day for a whole year.  What you are asking to do I believe would be to generate a month's worth of records for EACH worker.  That is still poor practice.  However, if you have a schedule table so you can identify which week days a person generally works, there is some logic (but not much) to pre-generating those records.  So if Sallie works mon-fri, you could generate an empty attendance record for those five days for all occurrences in a month.  Or Sam works weekends and holidays so the code would generate Sat and Sun records for Sam for a month but there would also need to be a holiday table available to include holidays and by extension, exclude them from the standard record generation.

In general, it is far better to add the records as needed.  That way you don't clutter up the database with records that are never relevant.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Gustav BrockCIOCommented:
You can automatically add records when the form loads to always have records, say, a week ahead:

Private Sub Form_Load()

    Const DaysToAdd As Integer = 7  ' Adjust as needed.
    Dim rs          As DAO.Recordset
    Dim NextDate    As Date
    Set rs = Me.RecordsetClone
    rs.Sort = "NameOfYourDateField"
    Set rs = rs.OpenRecordset()
    NextDate = rs!NameOfYourDateField.Value
    While NextDate < DateAdd("d", DaysToAdd, Date)
        NextDate = DateAdd("d", 1, NextDate)
            rs!NameOfYourDateField.Value = NextDate
            ' Assign values to mandatory fields if any (not AutoNumber):
            ' rs!SomeField.Value = SomeValue
            ' rs!SomeOtherField.Value = SomeOtherValue       
    Set rs = Nothing
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Question was fully answered.
Swell.  Every time Suzie, Sam, and the other 50 staff members login, they'll all add the same useless records; probably multiple times each day if this is a form they go to multiple times.  Hope this user knows enough to add a primary key or unique index to the table or it's going to grow like wildfire.  But the question was "fully" answered.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.