Showing posts with label Function. Show all posts
Showing posts with label Function. Show all posts

4/4/11

SQL - Function - Month Name

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Fn_MonthName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Fn_MonthName]
go
Create function Fn_MonthName (@MonthCode varchar(2))
Returns Varchar (15)
As
/*------------------------------------------------------------------------------------------------------------------------------------------
** SQLVersion : SQL 2000
** Function    : Fn_FinMonth
** Author     : Kartik M Kumar
** DateTime    : 29 January 2011 21:27
** Version    : 1.1
** Purpose    : To get the Month Name from the respective calander month
** ToCheck    :
** Changes    :
------------------------------------------------------------------------------------------------------------------------------------------*/
/*
-- Start of Debugging Stuff
    Declare @MonthCode varchar(2)
    Set @MonthCode = '03' -- Pass the Month Number as Input
-- End of Debugging Stuff
*/
Begin
    Declare @MonthName varchar(15)
    DECLARE @IMonthCd as int
    set @IMonthCd = cast(@MonthCode as Int)
    Select @MonthName = case @IMonthCd
        when 1 then 'January'
        when 2 then 'Feburary'
        when 3 then 'March'
        when 4 then 'April'
        when 5 then 'May'
        when 6 then 'June'
        when 7 then 'July'
        when 8 then 'August'
        when 9 then 'September'
        when 10 then 'October'
        when 11 then 'November'
        when 12 then 'December'
                end
Return Isnull(@MonthName, 'InvalidMonth')
end
go

http://www.sqlservercentral.com/scripts/Month+Name/72408/

1/24/11

Holiday Functions

Often it is useful to return the date of a holiday for a given year, perhaps for a schedule application. Neither Excel nor VBA have any built in functions for working with holidays -- you have to create your own. Holidays can be thought of as being either Fixed or Floating. Fixed holidays are those that occur on the same day each year, such as Christmas. Floating holidays are those which occur on different days in different years. For example, Thanksgiving (in the US) occurs on the fourth Thursday of November. Therefore, we need a function that will calculate the fourth Thursday. We'll generalize that function, in both VBA and worksheet function form, to return the Nth day-of-week for any month and year.

Other floating holidays are a bit harder to calculate. For example, in the US, Memorial Day occurs on the last Monday of May. Depending on the year, this may be either the 4th or 5th Monday. So we need a function to calculate the number of Mondays in May. We'll generalize this to compute the number of any day-of-week in any month and year.

Finally, there is Easter, whose actual date is some bizarre result of the phases of the moon. I don't claim credit for the formulas for calculating Easter shown below, nor do I claim to understand why the work, but they do.



Fixed Holidays
For fixed holidays, such as Christmas, this is simple since the date of the holiday does not change from year to year. For example, use the following to return the date of Christmas in the current year:

=DATE(YEAR(NOW()),12,25)


Floating Holidays
Other holidays, however, are not assigned to a specific date. For example, Thanksgiving Day is defined to be the 4th Thursday of November. Therefore its exact date will change from year to year. For Thanksgiving, we have an explicit VBA function:

Public Function ThanksgivingDate(Yr As Integer) As Date
ThanksgivingDate = DateSerial(Yr, 11, 29 - _
WeekDay(DateSerial(Yr, 11, 1), vbFriday))
End Function

We can generalize this to holidays that are defined as the Nth Day of some month, such as Martin Luther King's birthday, celebrated on the 3rd Monday of January. The following function will return the Nth DayOfWeek for a given month and year:

Public Function NDow(Y As Integer, M As Integer, _
N As Integer, DOW As Integer) As Date

NDow = DateSerial(Y, M, (8 - WeekDay(DateSerial(Y, M, 1), _
(DOW + 1) Mod 8)) + ((N - 1) * 7))
End Function

To return the date of the 3rd Monday in January of 1998, use
=NDow (1998, 1, 3, 2)


The NDow function can also be written as a worksheet formula:
=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)
+(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr,Mon, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.


This will work fine for most floating holidays. However, Memorial Day is celebrated on the Last Monday of May. To compute this date, we first need a function to tell us how many Mondays there are in the month.

Public Function DOWsInMonth(Yr As Integer, M As Integer, _
DOW As Integer) As Integer

On Error GoTo EndFunction

Dim I As Integer
Dim Lim As Integer
Lim = Day(DateSerial(Yr, M + 1, 0))
DOWsInMonth = 0
For I = 1 To Lim
If WeekDay(DateSerial(Yr, M, I)) = DOW Then
DOWsInMonth = DOWsInMonth + 1
End If
Next I

Exit Function
EndFunction:
DOWsInMonth = 0
End Function


Calling this function will tell us how many Mondays there are in May, 1998.
=DOWsInMonth(1998, 5, 2)

The DOWsInMonth can also be written as a worksheet function with the following array formula. Note that it will not work properly unless you press CTRL+SHIFT+ENTER instead of just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces {}.


=SUM((WEEKDAY(DATE(B3,C3,(ROW(INDIRECT
("1:"&DAY(DATE(B3,C3+1,0)))))))=D3)*1)

where B3 is the year, C3 is the month, and D3 is the day of week (1=Sunday, 2=Monday, ..., 7=Saturday)

There are 4 Mondays in May, 1998. The we can pass this into the NDOW function, to return 25-May-1998, the date of Memorial Day. Therefore, the formula to return the date of Memorial Day in 1999 would be

=NDow(1999,5,DowsInMonth(1999,5,2),2)

Many organizations recognize holiday dates on dates different than those of the actual date. Typically, this is done when the day of week of the holiday falls on a weekend and holidays are observed to make a three-day weekend. The general rule is that if the holiday falls on a Saturday, it is observed on the Friday before the holiday. If the holiday falls on a Sunday, it is observed on the following Monday. The following formula will return a Friday if the holiday falls on a Saturday, a Monday if the holiday falls on a Sunday, or the date itself if it falls on a weekday.=IF(WEEKDAY(A2, 1)=1,A2+1,IF(WEEKDAY(A2,1)=7,A2-1,A2)) where A2 is the date of the holiday. In VBA, you can use the following function:

Function Observed(TheDate As Date) As Date
If Weekday(TheDate, vbSunday) = 1 Then
Observed = TheDate + 1
ElseIf Weekday(TheDate, vbSunday) = 7 Then
Observed = TheDate - 1
Else
Observed = TheDate
End If
End Function

where TheDate is the date if the holiday.


Easter

Worksheet Formula For Easter
You can calculate the date of Easter with formula below. It is accurate for the years from 1900 to 2368. The formula is:

=FLOOR("5/"&DAY(MINUTE(YYYY/38)/2+56)&"/"&YYYY,7)-34

where YYYY is a four digit year between 1900 and 2368. The formula returns an incorrect for some years past 2369.


VBA Function For Easter
The date of Easter can be computed in VBA. The algorithm below is from the United States Naval Observatory, at Computing The Date Of Easter.

Public Function EasterUSNO(YYYY As Long) As Long
Dim C As Long
Dim N As Long
Dim K As Long
Dim I As Long
Dim J As Long
Dim L As Long
Dim M As Long
Dim D As Long

C = YYYY \ 100
N = YYYY - 19 * (YYYY \ 19)
K = (C - 17) \ 25
I = C - C \ 4 - (C - K) \ 3 + 19 * N + 15
I = I - 30 * (I \ 30)
I = I - (I \ 28) * (1 - (I \ 28) * (29 \ (I + 1)) * ((21 - N) \ 11))
J = YYYY + YYYY \ 4 + I + 2 - C + C \ 4
J = J - 7 * (J \ 7)
L = I - J
M = 3 + (L + 40) \ 44
D = L + 28 - 31 * (M \ 4)
EasterUSNO = DateSerial(YYYY, M, D)
End Function
You can call this from a worksheet cell with a formula like

=EasterUSNO(YYYY)

where YYYY is a four digit year.

http://www.cpearson.com/excel/holidays.htm
http://www.cpearson.com/excel/Easter.aspx

1/17/11

Delete Duplicate Records

Sub DeleteDuplicateRecords(strTableName As String)
' Deletes exact duplicates from the specified table.
' No user confirmation is required. Use with caution.
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
Dim varBookmark As Variant

Set tdf = DBEngine(0)(0).TableDefs(strTableName)
strSQL = "SELECT * FROM " & strTableName & " ORDER BY "
' Build a sort string to make sure duplicate records are
' adjacent. Can't sort on OLE or Memo fields,though.
For Each fld In tdf.Fields
If (fld.Type <> dbMemo) And (fld.Type <> dbLongBinary) Then
strSQL = strSQL & fld.Name & ", "
End If
Next fld
' Remove the extra comma and space from the SQL
strSQL = Left(strSQL, Len(strSQL) - 2)
Set tdf = Nothing

Set rst = CurrentDb.OpenRecordset(strSQL)
Set rst2 = rst.Clone
rst.MoveNext
Do Until rst.EOF
varBookmark = rst.Bookmark
For Each fld In rst.Fields
If fld.Value <> rst2.Fields(fld.Name).Value Then
GoTo NextRecord
End If
Next fld
MsgBox "delete"
rst.Delete
GoTo SkipBookmark
NextRecord:
rst2.Bookmark = varBookmark
SkipBookmark:
rst.MoveNext
Loop
End Sub

http://www.databasejournal.com/features/msaccess/article.php/3077791/Delete-Duplicate-Records-From-Access-Tables.htm

Mouse Pointer Hover Function

These are the standard Access cursors and can be set in code. The problem with this is once set the cursor remains this way until reset to another value

Screen.MousePointer = 1 'Standard Cursor
Screen.MousePointer = 3 ' I Beam
Screen.MousePointer = 7 'Double Arrow Vertical
Screen.MousePointer = 9 'Double Arrow Horizontal
Screen.MousePointer = 11 'Hour Glass

The function below allows you to change a cursor only when it hovers over a label or command button. It resets th e cursor when it leaves the control. Goto Modules in the Objects Dialog box and click on NEW. Then enter this code:


‘*********************** Code Starts Here **********************************
Declare Function SetClassLong Lib "user32" Alias "SetClassLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

'=====================================================================
' Globals for cursor handling
Global Const GCL_HCURSOR = (-12)
Global hSwapCursor As Long
Global hAniCursor As Long

'=====================================================================

Public Const IDC_ARROW = 32512&
Public Const IDC_IBEAM = 32513&
Public Const IDC_WAIT = 32514&
Public Const IDC_CROSS = 32515&
Public Const IDC_UPARROW = 32516&
Public Const IDC_ICON = 32641&
Public Const IDC_SIZENWSE = 32642&
Public Const IDC_SIZENESW = 32643&
Public Const IDC_SIZEWE = 32644&
Public Const IDC_SIZENS = 32645&
Public Const IDC_SIZEALL = 32646&
Public Const IDC_NO = 32648&
Public Const IDC_HAND = 32649&
Public Const IDC_APPSTARTING = 32650&

Declare Function LoadCursorBynum Lib "user32" Alias "LoadCursorA" _
(ByVal hInstance As Long, ByVal lpCursorName As Long) As Long

Declare Function LoadCursorFromFile Lib "user32" Alias _
"LoadCursorFromFileA" (ByVal lpFileName As String) As Long

Declare Function SetCursor Lib "user32" _
(ByVal hCursor As Long) As Long
'
Public Function Arrow_Pointer()
Screen.MousePointer = 1
End Function

Function ChangeCursor(strPathToCursor As String)

On Error GoTo Error_On_ChangeCursor

' Example :
' ChangeCursor ("C:\Program Files\Microsoft Office\Office\Hand.cur")

If Dir(strPathToCursor) <> "" Then
Dim lngRet As Long
lngRet = LoadCursorFromFile(strPathToCursor)
lngRet = SetCursor(lngRet)
End If

Exit_ChangeCursor:

Exit Function

Error_On_ChangeCursor:

Resume Exit_ChangeCursor

End Function

Public Function Default_Pointer()
Screen.MousePointer = 0
End Function

Public Function IBeam_Pointer()
Screen.MousePointer = 3
End Function

Function MouseCursor(CursorType As Long)

' Example: =MouseCursor(32512) ' using Public Constants from above

Dim lngRet As Long
lngRet = LoadCursorBynum(0&, CursorType)
lngRet = SetCursor(lngRet)
End Function

Public Function Replace_Cursor(PathToFile As String)

' Return handle from animated cursor

' Original - hAniCursor = LoadCursorFromFile("C:\WINDOWS\CURSORS\GLOBE.ANI")

hAniCursor = LoadCursorFromFile(PathToFile)
' Swap current mouse pointer with new animated cursor :
hSwapCursor = SetClassLong(Screen.ActiveForm.hWnd, GCL_HCURSOR, hAniCursor)


End Function

Public Function Restore_Cursor()

' Remove animated cursorand replace with saved index :

hSwapCursor = SetClassLong(Screen.ActiveForm.hWnd, GCL_HCURSOR, hSwapCursor)

End Function
‘*********************** Code End Here **********************************

When Prompted name the module modMousePointers

To change a pointer when the cursor moves over a control, goto the control’s

Properties – Events – On Mouse Move and in the MouseMove Property enter

=MouseCursor(xxxxx)

where xxxxx is the number without the ampersand

Public Const IDC_ARROW = 32512&
Public Const IDC_IBEAM = 32513&
Public Const IDC_WAIT = 32514&
Public Const IDC_CROSS = 32515&
Public Const IDC_UPARROW = 32516&
Public Const IDC_ICON = 32641&
Public Const IDC_SIZENWSE = 32642&
Public Const IDC_SIZENESW = 32643&
Public Const IDC_SIZEWE = 32644&
Public Const IDC_SIZENS = 32645&
Public Const IDC_SIZEALL = 32646&
Public Const IDC_NO = 32648&
Public Const IDC_HAND = 32649&
Public Const IDC_APPSTARTING = 32650&


http://bytes.com/topic/access/answers/662035-changing-mouse-cursor

10/24/10

Excel Tip: Count the Number of Occurrences of a Single Character in One Cell

I am currently helping a partner with a conversion from QuickBooks to Dynamics GP--specifically to migrate QuickBooks transaction Class data into GP Analytical Accounting transactions. For various reasons, standard, and even non-standard migration tools have been unable to convert the QuickBooks data, so we've been forced to export the data and import it into GP.

The eConnect integration I developed works great, but one of the annoyances has been preparing a QuickBooks export file for import to GP. If you are familiar with QuickBooks exports, you know that when data is exported to Excel, it typically looks quite presentable, but is not exactly import-friendly.

While trying to map the QuickBooks chart of accounts to the GP chart of accounts, I had to determine how many "levels" the QuickBooks account had. So for instance, there might be Account: Sub-Account: Department: Location: Expense: Sub-Expense. Out of the pile of QB accounts, I needed to determine the maximum number of "levels" the accounts had. Since there is a colon between each level, I just needed to count the number of colons in the cell, and then add one. Simple!

But I am pretty sure I've never had to count the number of occurrences of a value in a single cell. I've used the SEARCH function to determine whether a value exists at all, but never had to count the occurrences of that value.

With Google to the rescue, I stumbled across a Microsoft Support article that provided several examples of how to count the occurrences of a string in an Excel file.

http://support.microsoft.com/kb/213889

And here is the formula for counting the number of occurrences of a character in a cell:

=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,"a",""))

Pretty darn smart. Maybe, on a good day, with enough sleep, I could have come up with something that elegant, but I am pretty sure that article saved me alot of time.

6/15/10

Printing the name of the Report and/or the name of the database on a Report

Sometimes you may want to print a report that has information such as the name of the Report, or the name of the Database, in the Report's footer, to identify the print out. To do this, you need to add a Textbox to the relevant location on the Report, and then set the ControlSource to:

=[Name]

For the name of the Database, you would instead set the ControlSource to:

=Dir([CurrentDb].[Name],16)

Note that we cannot use the VBA constant vbDirectory, and so must instead use the numeric value, which is 16.

http://www.applecore99.com/rpt/rpt018.asp

10/28/09

VBA function to count # of days between two dates

Function DayCount(DateBeg As Date, DateEnd As Date, Optional D1 As Integer, Optional D2 As Integer, Optional D3 As Integer, Optional D4 As Integer, Optional D5 As Integer, Optional D6 As Integer, Optional D7 As Integer)
For i = 0 To DateEnd - DateBeg If Weekday(DateBeg + i) = D1 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D2 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D3 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D4 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D5 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D6 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D7 Then Cnt = Cnt + 1 Else End If
Next i
DayCount = Cnt
End Function

8/10/09

A Better NETWORKDAYS

A Better NETWORKDAYS

The following formula does everything that the traditional NETWORKDAYS function does, plus it allows you to select as many days of the week as you want to exclude from the calculations. There are two flavors of the formula. The first version does not allow a list of holidays to exclude from the count. The second version does allow a list of holidays to exclude. Both formulas require a range named ExcludeDaysOfWeek that lists the day of week numbers (1 = Sunday, 2 = Monday, ... 7 = Saturday) to exclude from the calculation.

Formula Without Holidays

=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),ExcludeDaysOfWeek,0)),1,0))

In this formula, StartDate is the data at which counting will begin. EndDate is the last date of the period to count. ExcludeDaysOfWeek is a range of up to 7 cells indicating the day-of-week numbers (1 = Sunday, 2 = Monday, ... 7 = Saturday) to exclude from the count. You may, if you choose to, replace the range reference of ExcludeDaysOfWeek to a hard-coded list of day numbers. For example,

=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),{1,6,7},0)),1,0))

Note that the days of the week are enclosed in curly braces { }, not parentheses.

Formula Supports Holidays

The formula below works must like the previous formula except that it allows you to enter holidays in a range of cells, and those holidays will be excluded from the count. You must create a named range of cells named Holidays that lists the list of holidays to exclude from the result.

=IF(OR(StartDate<=0,EndDate<=0,StartDate>EndDate,ISNUMBER(StartDate)=FALSE,
ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),
ExcludeDaysOfWeek,0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))

The formula is split into multiple lines here for the sake of readability. In practice, though, the entire formula should be on a single line. The parameters in this formula have the same meaning as they did in the previous formula, with the addition of the Holidays list. Holidays should be a range of cells that contains dates to exclude from the count. It does not matter if a holiday falls on a day of week that is listed in the ExcludeDaysOfWeek.