Results 1 to 11 of 11

Thread: Date range VBA

  1. #1
    Join Date
    Jan 2013
    Posts
    53

    Unanswered: Date range VBA

    Hello All,

    Would like to see if there was a sample VBA code I could see that essentially retrieves a specific date based on a date paramenter.

    For example: 1/1/2014-2/1/2014 would equal 3/1/2014 and so on.

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by VBAnewbie View Post
    a sample VBA code I could see that essentially retrieves a specific date based on a date paramenter
    Do you mean SQL? VBA is a programming language, not a query language and it does not retrieve anything.
    Quote Originally Posted by VBAnewbie View Post
    For example: 1/1/2014-2/1/2014 would equal 3/1/2014
    This does not make sense to me. Can you elaborate ?
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    In your query you can either put
    between [date1] and [date2]
    and it will prompt the user for dates.

    or

    use a form for user to input the dates and use the form to get the dates
    between forms!myForm!date1 and forms!myForm!date2

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I woudl strongly recommend AGAINST using unvalidated user input for parameters, but especially for dates, they are too easy to screw up
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2013
    Posts
    53
    Quote Originally Posted by Sinndho View Post
    Do you mean SQL? VBA is a programming language, not a query language and it does not retrieve anything.

    This does not make sense to me. Can you elaborate ?
    I should have been a bit more specific. Am looking for a public function which can derive a date based on a range of dates. Am thinking something similar to Select Case but unsure how to code a range of dates. Perhaps something similar to this?

    Code:
    Public Function getEffectiveDate(vHireDate As String) As String
    
        Select Case vHireDate
            Case "1/1/2014 - 2/1/2014"
                getEffectiveDate = "3/1/2014"
        Select Case vHireDate
            Case "2/2/2014 - 3/1/2014"
                getEffectiveDate = "4/1/2014"
        Select Case vHireDate
            Case "3/2/2014 - 4/1/2014"
                getEffectiveDate = "5/1/2014"
        Select Case vHireDate
            Case "5/2/2014 - 6/1/2014"
                getEffectiveDate = "6/1/2014"
            Case Else
                getEffectiveDate = vHireDate
        End Select
    
    End Function

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    perhaps if you gave a better business rule we may be able to help. what I think you want is a table which defines a hire date

    creating a function such as the one you propose is is going to be an on going maintenance task the function will need extending each and every time you, especially if you use stings as the parameter type AND return datatype, when the obvious type to choose wopld be date

    you coudl use a function to say return a specific day in the month, but its not clear from your example of what you are trying to achieve
    that could be
    the first weekday in the specified range


    in short you can do whatever the heck you want, however tortuous with code and or data but you need to have a clear view of what you are trying to do. and if you are looking for help here you also need to be able to express that clear intention here as well
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2013
    Posts
    53
    A function that returns a specific DATE based on a date range is what I'm looking for. Not sure how else to explain it.

    Business rule would be as follows. Any date which falls between the date ranges listed below would derive the equal to date:

    09/03/2014 - 10/03/2014 = 11/1/2014
    10/04/2014 - 11/02/2014 = 12/1/2014
    11/03/2014 - 12/03/2014 = 1/1/2015

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so create a table.... NOT a function
    as a table it all based on data so there are no programming changes required as time advances
    an added bonus:-
    the data is under the control of the user(s) so if its wrong its their lookout not yours
    rather than have a start and ending date just haveone, arguably the period start date
    mydatestable
    PeriodStart Date PK
    MagicDate Date

    then to find you r magic date for the specified date

    Code:
    select top 1 magicdate from mydatestable where periodstart <= #2014/09/21#
    order by periodstart
    or use a dlookup
    Code:
    newmagicdate = datetime
    newmagicdate = dlookup("MagicDate","MyDatesTable","periodStart > #2014/09/21#)
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jan 2013
    Posts
    53
    Ok, so I created a tbl [_EnrollWindow_Others] with the fields [HireDateStart],[HireDateEnd],[EffDate].

    Am trying to pull the [EffDate], which falls between [HireDateStart] and [HireDateEnd]. The tbl [_00_Cenus_CIGH].[Last Hire Date] field would determine which [EffDate]. All dates in both tbl's are in a Date/Time format

    Here is my DLookUp:
    Expr4: DLookUp("[EffDate]","[_EnrollWindow_Others] = '" & "' And [HireDateStart] <= #" & [Last Hire Date] & "# AND [HireDateEnd] >= #" & [Last Hire Date] & "#")
    Not sure what I'm missing here, I get #Error. Any ideas?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The DLookUp() function needs 3 parameters: Column Name, Table Name, Criteria. One is missing in your expression, probably the table name if EffDate is the name of a column.
    Have a nice day!

  11. #11
    Join Date
    Jan 2013
    Posts
    53
    This worked:

    Expr4: DLookUp("First([EffDate])","[_EnrollWindow_Others]","#" & [Last Hire Date] & "# Between [HireDateStart] and [HireDateEnd]")

Posting Permissions

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