Results 1 to 8 of 8
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: How can I make this simple procedure reuseable

    How can I use this same procedure to calculate the ending date without rewriting the same code?

    Private Sub ConvToDate()
    'This procedure is to convert from the quarter to the date format
    ' to find the starting date and ending date and concatenate w/ the year.
    ' eg. 1Q 1999 to 01/01/1999

    Dim sqtr As String
    Dim syr As String
    Dim startdate As String
    sqtr = cboSQtr
    syr = cboSYear

    '1Q = "01/01/"
    '2Q = "04/01/"
    '3Q = "07/01/"
    '4Q = "10/01/"
    'cboSQtr is a combo box that stores the value for the quarter selected
    'cboSYear is a combo box that contains the value for the year selected.

    If sqtr = "1Q" Then
    sqtr = "01/01/"

    Else
    If sqtr = "2Q" Then
    sqtr = "04/01/"

    Else
    If sqtr = "3Q" Then
    sqtr = "07/01/"

    Else
    sqtr = "10/01/"

    End If

    End If

    End If
    startdate = sqtr & syr
    'Debug.Print startdate
    End Sub

  2. #2
    Join Date
    Jan 2002
    Location
    UK
    Posts
    67
    Just define the sub as :

    Public Function ConvToDate() as Date

    ............
    ............
    ............


    In the fag end specify as:

    ConvToDate = sqtr & syr

    End Function


    Shud work.

    Let me know if u have any more problems...

  3. #3
    Join Date
    Nov 2001
    Posts
    336
    Try:

    Public Enum Quarters
    Quarter_1 = 1
    Quarter_2 = 2
    Quarter_3 = 3
    Quarter_4 = 4
    End Enum

    Public Function ConvToDate(ByVal Quarter As Quarters, ByVal intYear As Integer) As Date

    Select Case Quarter
    Case Quarter_1
    ConvToDate = CDate("01/01" & Str(intYear))
    Case Quarter_2
    ConvToDate = CDate("04/01" & Str(intYear))
    Case Quarter_3
    ConvToDate = CDate("07/01" & Str(intYear))
    Case Quarter_4
    ConvToDate = CDate("10/01" & Str(intYear))
    End Select

    End Function


    HTH, Igor

  4. #4
    Join Date
    May 2002
    Posts
    395
    Originally posted by xzone
    Just define the sub as :

    Public Function ConvToDate() as Date

    ............
    ............
    ............


    In the fag end specify as:

    ConvToDate = sqtr & syr

    End Function


    Shud work.

    Let me know if u have any more problems...



    Thanks much!!

  5. #5
    Join Date
    May 2002
    Posts
    395
    Originally posted by IGelin
    Try:

    Public Enum Quarters
    Quarter_1 = 1
    Quarter_2 = 2
    Quarter_3 = 3
    Quarter_4 = 4
    End Enum

    Public Function ConvToDate(ByVal Quarter As Quarters, ByVal intYear As Integer) As Date

    Select Case Quarter
    Case Quarter_1
    ConvToDate = CDate("01/01" & Str(intYear))
    Case Quarter_2
    ConvToDate = CDate("04/01" & Str(intYear))
    Case Quarter_3
    ConvToDate = CDate("07/01" & Str(intYear))
    Case Quarter_4
    ConvToDate = CDate("10/01" & Str(intYear))
    End Select

    End Function


    HTH, Igor



    Thanks much!!!

  6. #6
    Join Date
    May 2002
    Posts
    395

    "user-defined type not defined" message

    I am trying to use to Select Case statements as you suggested instead of the If/Else statements. I received an error message

    "user-defined type not defined" at the
    Public Function ConvToDate(ByVal Quarter As Quarters, ByVal intYear As Integer) As Date

    Here is the code:

    Public Function ConvToDate(ByVal Quarter As Quarters, ByVal intYear As Integer) As Date

    Public Enum Quarters
    Quarter_1 = 1
    Quarter_2 = 2
    Quarter_3 = 3
    Quarter_4 = 4
    End Enum

    Dim ConvDate As Date


    Select Case Quarter
    Case Quarter_1
    ConvDate = CDate("01/01" & Str(intYear))
    Case Quarter_2
    ConvDate = CDate("04/01" & Str(intYear))
    Case Quarter_3
    ConvDate = CDate("07/01" & Str(intYear))
    Case Quarter_4
    ConvDate = CDate("10/01" & Str(intYear))
    End Select

    End Function


    Private Sub PassingEndDate()
    Dim eqtr As String
    Dim eyr As String
    Dim enddate As String
    eqtr = cboEQtr
    eyr = cboEYear

    Call ConvToDate(eqtr, eyr)

    enddate = eqtr & eyr
    enddate = Format(DateAdd("m", 2, enddate), "mm/dd/yyyy")

    Debug.Print enddate

    End Sub

    Am I missing something in the Reference Library?

    Thanks!!
    ]

  7. #7
    Join Date
    Jan 2002
    Location
    UK
    Posts
    67
    Define the Data Type outside the function in the Declarations: That might help./

  8. #8
    Join Date
    May 2002
    Posts
    395
    Originally posted by xzone
    Define the Data Type outside the function in the Declarations: That might help./
    Yes! That is it.
    Thanks very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •