Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Days in Month Function

Function to Calculate Number of Days

The User-Defined Function DaysM() given below can be used in calculations that involves the number of days of a particular month. Copy and Paste the following Code into a Global Module and save it in your Project.

.
Public Function DaysM(ByVal varDate) As Integer
Dim intYear As Integer, intmonth As Integer
On Error GoTo DaysM_Err

If Nz(varDate) = 0 Then
    DaysM = 0    
    Exit Function
End If

intYear = Year(varDate)
intmonth = Month(varDate)

DaysM = Day(DateSerial(intYear, intmonth + 1, 1) - 1)

DaysM_Exit:
Exit Function
DaysM_Err:
MsgBox Err.Description, , "DaysM()"
DaysM = 0
Resume DaysM_Exit
End Function

Syntax: X = DaysM(varDate)

Replace the varDate parameter with a valid Date. The Number of Days for the Month will be returned in Variable X.

The Parameter value can be a valid Date, a Date in Text format like "15-02-2008" or in its corresponding numeric value 39493.

If you would like to re-write the Function differently by adding few extra lines of code then you may replace the expression DaysM = Day(DateSerial(intYear, intmonth + 1, 1) - 1) with the following lines of Code:

DaysM = Choose(intmonth, 31, 28 + IIf((intYear Mod 4) = 0, 1, 0), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

If intmonth=2 then
   Select Case (intYear Mod 400)
       Case 100, 200, 300
            DaysM = DaysM - 1
   End Select
End if

This Function can be used in VBA Routines, Queries or Text Controls in Forms or Reports where number of days of a particular month is involved in calculations.

Example: An Employee resumed duty after her vacation on 15-02-2008. To calculate the balance number of days, for her salary payment, one of the three Expressions given below can be used.

Dt = #02/15/2008#

BalDays = 1 + DateDiff("d", Dt, DateSerial(Year(Dt), Month(Dt) + 1, 1) - 1) 

or

BalDays =   1 + Day(DateSerial(Year(Dt), Month(Dt) + 1, 1) - 1) - Day(Dt) 

or

BalDays = 1 + DaysM(Dt) - Day(Dt)

The first two calculations are performed with Built-in Functions. With DaysM() Function, which uses the second expression for the main calculation, we could arrive at the same result with lesser number of characters in the last expression.

Finding Number of Days in a Month is not a big issue. We have learned simple rules to keep track of this, like 30 days in months 4, 6, 9, 11 (April, June, September & November) and 28 days for February. When it comes to February only we need calculations, like Year/4=0, to find whether to add or not to add one more day to 28 days in February.

But, this needs correction when we enter into Centuries. During the Year 2000 we took 29 Days in February for calculations. But this has not happened in the Year 1700, 1800 or 1900 and not going to happen in calculations that includes the Year 2100, 2200 & 2300 either.

We take approximately 365.25 days for a year, based on Earth's rotation time around the sun, and every 4th year is considered as leap year with 366 days, adding 1 more day to February. But, it is estimated that the Earth's rotation time around the sun is about 365 days 5 hours 48 minutes and 45.5 seconds (i.e. 365.2422 days) only. We take about 0.0078 days more every Year into our calculations and this value will add up to 3.12 extra days in about 400 years of time. To adjust this excess 3 days, all Century Years not evenly divisible by 400 are made common years, even though they are evenly divisible by 4. The remaining 0.12 value becomes 1.2 in about 4000 Years time and year 4000 is not leap year, even though it is evenly divisible by 400.

Who knows before that some other discovery will take us into re-working the whole thing again?

References: Microsoft Encarta Encyclopedia

History of Calendar

The Roman Calendar

The original Roman calendar, introduced about the 7th century BC, had 10 months with 304 days in a year that began with March. Two more months, January and February, were added later in the 7th century BC, but because the months were only 29 or 30 days long, an extra month had to be intercalated approximately every second year. The days of the month were designated by the awkward method of counting backwards from three dates: the calends, or first of the month; the ides, or middle of the month, falling on the 13th of some months and the 15th of others; and the nones, or 9th day before the ides. The Roman calendar became hopelessly confused when officials to whom the addition of days and months was entrusted abused their authority to prolong their terms of office or to hasten or delay elections.

In 45 BC Julius Caesar, on the advice of the Greek astronomer Sosigenes (flourished 1st century BC), decided to use a purely solar calendar. This calendar, known as the Julian calendar, fixed the normal year at 365 days, and the leap year, every fourth year, at 366 days. Leap year is so named because the extra day causes any date after February in a leap year to "leap" over one day in the week and to occur two days later in the week than it did in the previous year, rather than one day later, as in a normal year. The Julian calendar also established the order of the months and the days of the week as they exist in present-day calendars.

In 44 BC Julius Caesar changed the name of the month Quintilis to Julius (July), after himself. The month Sextilis was renamed Augustus (August) in honour of Caesar Augustus, who succeeded Julius Caesar. Some authorities maintain that Augustus established the lengths of the months we use today.

The Gregorian Calendar

The Julian year was 11 min and 14 sec longer than the solar year. This discrepancy accumulated until by 1582 the vernal equinox (see Ecliptic) occurred 10 days early and Church holidays did not occur in the appropriate seasons. To make the vernal equinox occur on or about March 21, as it had in AD 325, the year of the First Council of Nicaea, Pope Gregory XIII issued a decree dropping 10 days from the calendar. To prevent further displacement he instituted a calendar, known as the Gregorian calendar, that provided that century years divisible evenly by 400 should be leap years and that all other century years should be common years. Thus, 1600 was a leap year, but 1700 and 1800 were common years.

The Gregorian calendar, or New Style calendar, was slowly adopted throughout Europe. It is used today throughout most of the Western world and in parts of Asia. When the Gregorian calendar was adopted in Great Britain in 1752, a correction of 11 days was necessary; the day after September 2, 1752, became September 14. Britain also adopted January 1 as the day when a new year begins. The Soviet Union adopted the Gregorian calendar in 1918, and Greece adopted it in 1923 for civil purposes, but many countries affiliated with the Greek Church retain the Julian, or Old Style, calendar for the celebration of Church feasts.

The Gregorian calendar is also called the Christian calendar because it uses the birth of Jesus Christ as a starting date. Dates of the Christian era (see Chronology) are often designated AD (Latin anno domini, "in the year of our Lord") and BC (before Christ). Although the birth of Christ was originally given as December 25, 1 BC, modern scholars now place it about 4 BC.

Because the Gregorian calendar still entails months of unequal length, so that dates and days of the week vary through time, numerous proposals have been made for a more practical, reformed calendar. Such proposals include a fixed calendar of 13 equal months and a universal calendar of four identical quarterly periods.

Source:Microsoft Encarta Encyclopedia


Share:

Finding Consecutive Workdays with Query

How do we determine that the date values within a certain date-range are consecutive or intermittent? To bring some clarity into this problem and solution I will present an example below.

Assume that a Company hires some temporary employees on daily wages to complete a Project within 15 days time. The Company informs the temporary hands that if they work 8 hours a day and for 7 days continuously without taking week-end breaks they will be rewarded with special incentives besides their daily wages.

The employees joined for work on different dates and not all of them could work for 7 days without breaks. The attendance of the employees is logged date-wise into a Table with Employee Codes.

After completion of the Project the task was given to us to prepare the list of employees who worked continuously for 7 days for awarding their declared incentives.

I know it sounds like a Question in an Examination Hall. But, we are faced with difficult Questions very often, while considering data processing tasks and most of them we have to ask ourselves to find solutions.

This is not that hard to find. With three simple Queries we can solve this problem. Interested? Read on.

First we need a table with some sample data. Create a Table with the name Table1 with the following Table Structure and enter sample data into it from the image given below. You may add more employee Codes and Date Values on similar lines for more accurate testing. Define emp and workdate fields combined as Primary Key to avoid entry of duplicate date values.

Solution

  1. Query1: Create a Total Query Grouped by emp, take the minimum value from workdate to find the work-start date of each employee and create a new column adding 6 days to the work-start date to calculate and store the 7th Day Date of work for each employee. They must work from the work-start date to this date without break for eligibility of special incentive.

    Query1 SQL :

    SELECT Table1.emp,
     Min(Table1.workdate) AS min_date,
     Min([workdate])+6 AS Day7Date
    FROM Table1
    GROUP BY Table1.emp;
    
  2. Query2: Join Table1 with Query1 on emp field and select records using the Day7Date as criterion on workdate that falls on or before the Day7Date.

    Query2 SQL :

    SELECT Table1.* 
    FROM Query1 INNER JOIN Table1 ON Query1.emp = Table1.emp
    WHERE (((Table1.workdate)<= [Day7Date]));
    
  3. Query3: Create a Total Query using Query2 as source. Group on emp field, take Count of workdate and filter the output with workdate Count =7.

Query3 SQL:

SELECT Query2.emp,
 Count(Query2.workdate) AS [count]
FROM Query2
GROUP BY Query2.emp
HAVING (((Count(Query2.workdate))=7));

Note: The continuity of dates is checked at employee level based on their work-start dates. Not necessary that all of them should work within the same month or start work on a specific Date.

But when the data file contains large amount of records for various periods and records pertains to a specific period need to be subjected to this method then data filtering must be done. Create a Query using Table1 and use Start-Date and End-Date range as criteria to pick records within that period. Use that Query as source data for Query1 and Query2 above, instead of using Table1 directly.

As the saying goes - "There is always more than one way to skin a Cat" (did I say it correctly), we can take a different approach to solve this problem with slight deviation to the definition of the above Queries. The Query SQL strings of this new method are also given below. You may copy them and create Queries to try them out too.

Query 1_1 SQL

SELECT Table1.emp,
 Min(Table1.workdate) AS min_date,
 [min_date]+0 AS day1,
 [min_date]+1 AS day2,
 [min_date]+2 AS day3,
 [min_date]+3 AS day4,
 [min_date]+4 AS day5,
 [min_date]+5 AS day6,
 [min_date]+6 AS day7
FROM Table1
GROUP BY Table1.emp;

Query 2_1 SQL

SELECT Table1.emp,
 Table1.workdate
FROM Query1_1 INNER JOIN Table1 ON Query1_1.emp = Table1.emp
WHERE (((Table1.workdate) In ([day1],[day2],[day3],[day4],[day5],[day6],[day7])));

Query 3_1 SQL

SELECT Query2_1.emp,
 Count(Query2_1.workdate) AS CountOfworkdate
FROM Query2_1
GROUP BY Query2_1.emp
HAVING (((Count([Query1_2].[workdate]))=7));

I have not tested these methods extensively to eliminate side effects, if any. You may use them at your own risk.

You have different ideas? Please share it with me by posting them into the Comments Section.


Share:

Transparent Command Button

Command Buttons can be kept hidden on Forms by setting their Transparent or Visible Property to No and make them visible to click, when certain condition is met or when a particular User opens the Form.

For example, when a Form with Data is routed through Networks to different Users (in a Secured Database) for verification, Approval of the contents, different Command Buttons can be created and programmed for each user and make them visible only when the respective User opens the Form or subject to the verified status of Data at lower Rank of Users and so on.

The more popular method in practice, in the above situations, is to disable the Command Button and keep it visible all the time and enable it when required. Making a Button visible or invisible is another way of doing the same thing. We cannot set the Command button's Enabled or Visible Property to No, when the Button is active. We must shift the Focus from the Button to some other Control before setting the above properties to No through VBA Code.

The Transparent Property setting doesn't have this restriction. You can make the Command Button visible or invisible by setting the value to No (for Normal View) and Yes to make it disappear.

This method has some side effects. If the User knows exactly from where the Command Button disappeared then he can click on the same spot and the transparent Command Button will respond with its programmed action. But we can trick the User by changing the Button's invisible run time parking position to a different location by increasing or decreasing the Left Property value after saving the original value into a Module level Global Variable. When it is time to restore the Button to its normal shape on the original spot we can use the Left Property value saved in the Global Variable.

Here, we are going to use the Transparent Command Button differently for designing and running a Main Switchboard Menu.

  1. Design a Table using the Structure shown below and enter few sample records given in them.
  2. Design a Tabular Form using the above table using the Form Wizard and save it with the name DataFiles.
  3. Open the Form in Design view. Keep the ID and Desc fields and remove others. Keep Shift Key down and Click on both Fields to select them together. Or Click outside the Fields and drag the mouse over to select them both.
  4. Display the Property Sheet (View - - > Properties) and change the Following Property Values.
    • Enabled = No
    • Locked = Yes
    • Top = 0.0417"
    • Height = 0.1771"
    • Back Style = Normal
    • Back Color = -2147483633
    • Special Effect = Raised
    • Border Style = Solid
    • Border Color = 0
    • Border Width = Hairline
    • Fore Color = 0
  5. Select View Menu and remove check mark from Form Header/Footer Option. Select Yes to the Warning message to delete the Header/Footer Sections from the Form.
  6. Click on the top left corner of the Form to de-select all controls and select the Form. Display the Form Property Sheet (View - -> Properties) and change the following Form Property Values.
    • Form Width = 2.5417"
    • Default view = Continuous Forms
    • Views Allowed = Form
    • Allow Edits = No
    • Allow Additions = No
    • Allow Deletions = No
    • Record Selectors = No
    • Navigation Buttons = No
    • Dividing Lines = No
    • Scrollbars = No
    • Border Style = None
    • Control Box = No
    • Min Max Buttons = None
    • Close Button = No
    • Whats this Button = No
    • Allow Design Changes = Design View Only
  7. Draw a Command Button about the size of both Field widths put together somewhere below the Fields so that we can modify the properties of the Command Button and place it over both the Fields in transparent state.
  8. Select the Command Button and display the Property Sheet. Change the following Property Values:
    • Name = cmdMenu
    • Transparent = Yes
    • Height = 0.1771"
  9. Click on the On Click Property and select Event Procedure from the Drop down List and click on the Build (. . .) button to open the Form's VBA Module.

    Here we can write code to test the ID Value of the record clicked by the User and open the Form corresponding to that Number.

    The VBA Code for a simple method to test and Open the Form corresponding to the ID number of the record clicked is given below.

  10. Copy and paste the following Code into the VBA Module of the DataFiles Form and save the Form.
    Private Sub cmdMenu_Click()
    Dim IDNumber As Integer, strForm As String
    
    IDNumber = Me![ID]
    strForm = ""
    Select Case IDNumber
        Case 1
           strForm = "CRREQ_MASTER"
        Case 2
           strForm = "Dept_Codes"
        Case 3
           strForm = "Branch"
    End Select
    If Len(strForm) > 0 Then
       DoCmd.OpenForm strForm, acNormal
    End If
    End Sub
    

    You can use a better method, more powerful and re-usable one that I have explained with VBA Code in an earlier Article with the Title: Opening Access Forms. I recommend that you read that Article and the method explained there, which is easy to implement in any of your Projects. The sample table that we have seen at the beginning of this Article is taken from that example with additional fields (Forms, Macro & Type). If the Code given there is used, you can add any number of records in the above table and don't need to test each ID number as we did in the above code. Besides that you can open a Form directly or run a process, through macros, and at the end of the process open a Form to display the result by setting the value in the Type Field to 0 or 1, when a new record is added to the table.

  11. Drag the Transparent Command Button (or use Ctrl Key with Arrow Key combination in Office 2000, Arrow Key alone in Office 2003, after selecting it) and place it over ID and Desc Fields correctly as shown below.
  12. Click on the Detail Section of the Form and Display the Property Sheet. Change the Details Section height.
    • Detail Section Height = .25"

    The finished design will look like the image given below in normal view. This Form will be used as a Sub-Form on the Control Screen (Main Switchboard) for our Data Files Menu. If you don't want to show the Serial Numbers appearing at the left, you can set the Visible Property of that field to No.

    Open the Main Switchboard Form of your Project (or any Form to try it out) drag and place the above Form on it. I placed the Menu on the sample Form that we have used for the Colorful Command Buttons Article.

  13. Click on the Sub-Form, Display the Property Sheet and change the following Property Values:
  • Special Effect = Flat
  • Border Style = Transparent

The instances of the same Transparent Command Button now appears on top of each record in the Form and will respond to the Clicks on them. We can check the Value of the record on which the click received and open the Form corresponding to that record ID number.

When you add more records to the menu table they will automatically appear on the menu without any modifications to the design.

Try the Menu by adding Form Names already available in your Project replacing the ones that appears in the Code.

Share:

Colorfull Command Buttons

We have seen Double-Action Command Button in the earlier Post and Command Button Animation before and now it is time for adding some color to the Command Buttons. Take a look at some different Command Buttons in action on a Control Screen (Main Switchboard) of a sample Ms-Access Application.

Like to use them on your Application? You may be expecting some lengthy lectures from me about them. Or may be expecting about hundred or more lines of VBA Code to copy from the bottom of this Article to paste into your Project. Or you are getting prepared to download some MS-Access Add-Ons from Internet and install them into your machine. Probably one or two Library Files to link with your Project too, right?

No, you don't have to do any of those things to create and put these Command Buttons on your Project. You can create one Button in two minutes, well, may be 3 minutes when you create it for the first time. You can create the variants of the same button in less than one minute's time.

I know you waited for so long for me to come to the point. These Buttons are not created in MS-Access. We have to create them in MS-Word (if you prefer Excel, it's ok with me), copy and paste them into MS-Access Form, and make few changes in the Property Sheet of the Button. May be one or two lines of Code for Button Animation and you are ready to roll.

  1. Open your MS-Access Project and open a Form and keep it in Design View.
  2.  

  3. Open MS-Word and check whether the Drawing Toolbar is visible at the bottom above the Status Bar. If it is not there then select View - -> Toolbar - -> Drawing. If the Drawing Toolbar Button is visible on the Toolbar above, you can click on it to bring it up too.
  4. Click on the AutoShapes - -> Basic Shapes - -> Rounded Rectangle Tool.
  5. Draw a Rounded Rectangle.
  6.  

  7. Right-Click on the shape that you have just drawn and select Format AutoShape from the Shortcut Menu.
  8. Select Colors and Lines Tab on the displayed control and Click on the Color drop-down list under Fill values group.
  9. Click on the Yellow Color on the Color Pallette and then Click on the Fill Effects. . . Control.
  10. Select the Gradient Tab and select Vertical under Shading Styles and click on the right bottom corner shape under the Variants category. The selected shape appears under the Sample heading at the right.
  11. Click OK and OK again on the Format AutoShape control to paint the Rounded Rectangle with the selected gradient color.
  12. Right-Click on the shape and select Edit Text from the displayed Shortcut Menu.
  13. Type the Button Caption, say Data Files. Highlight the Text and use normal Word Formatting tools viz. Align Center, Bold and Font Color that have better visibility on the Gradient Backgroup.

    Note: Once you copy and paste the Button on MS-Access Form you cannot change the Caption or Formatting. You better save and keep the original Buttons in Word Document itself. If you need some changes on the Command Buttons later you can easily do that here, copy and paste it again with changes.

    You can make copies of this Button and follow Step 5 above onwards to change the Gradient Color and Caption, according to your needs.

    Tips:You can change the shape of the created Buttons by selecting Change AutoShape from the Draw Menu on the Drawing Toolbar and select the shape you like the most for a Button.

  14. Right-Click on the Button and select Copy from the Shortcut Menu.
  15. Minimize Word and open your Access Form that you have kept open in Design View.
  16. Right-Click on the Form Detail Section or Footer Section where you want to place the Command Button and select Paste. Drag and position the Button to the correct location.
  17. Right Click on the Button and select Properties from the Shortcut Menu.
  18. Keep the Property Sheet away from the Button (if it is overlapping the Button) and change the Size Mode property to different Modes from Clip to Zoom or Stretch and see how the Button behaves at different Modes.

    When the Zoom mode is selected the Button re-sizes itself maintaining the height and width proportions correctly based on the current frame size, but leaves some gap around the image. This background area we can fill up with the background color of the Form so that when the Form opens in Normal View this will not be visible.

  19. If the button is in the Footer Section of your Form then click at the Footer Section of the Form to select it and display the Property Sheet.
  20. Copy the Back Color Property Value of the Form.
  21. Click on the Button to display the Property Sheet of the Button.
  22. Paste the value in Back Color property copied from Form.
  23. Change the Special Effect Property to Raised.
  24. Change the Border Style Property to Transparent.
  25. Now you can program your Colorful Command Button like the normal MS-Access Command Button.

    If you look at the Property Sheet of this Command Button you can see that all the essential Properties and Events are available for Programming, using Event Procedures, Hyperlinks, Macros etc.

    I will give you one line of Code to put some life (Animation) into this Button so that it will respond when the mouse moves over it.

  26. Click on the Button's Mouse Move Property and select Event Procedure. Copy the following Code between the Empty Subroutine lines.

     

    
    Me.OLEUnbound257.SpecialEffect = 2
    
  27. Copy the correct name of the Button from its Name Property and paste it replacing OLEUnbound257. See that you are not deleting the dot separaters after the word Me. And at the beginning of .SpecialEffect while pasting the Button Name.
  28. Select the Form's Section (Detail or Footer or Header) where you have placed the Command Button and display the Property Sheet.
  29. Click on the Mouse Move Property and select Event Procedure, copy and paste the above line of Code with the Button Name change and paste between the empty Subroutine lines.
  30. Change the Value 2 to 1.

Initially the Button will be in a Raised Style. When the mouse is moved over the Button then it will be pushed in (Sunken). When the Mouse is moved out of the Button it will restore back to the Raised state. If you repeat this action in quick succession the Animation will be more evident.

We will learn some more tricks with Command Buttons.

 

What you think about them?

 

Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts