Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    2

    Unanswered: Inserting Date Range in VBA

    My first post. Be gentle

    I have report with a text box that runs a piece of VBA code to calculate the median value of dollar amounts in the [LoanAmount] field of the underlying query. It works fine, except that now I want to use a switchboard with start and end dates to set the date range of the underlying queries of the report and subreports. If I do that, the median value text box craps out and produces an #error. It seems I have a choice: a VBA calculated median text box, or easily entered date ranges via a switchboard. But I want to have my cake and eat it too.

    Would somehow coding a lookup to my date range switchboard in my median VBA script keep the script and the query criteria from clashing, and if so how do I do this? I'm a novice at best at VBA.

    Thanks for any help y'all can give.

    Here's the code for the median calculation:

    Option Explicit
    Function Median(qryProgramUpdate As String, LoanAmount As String) As Single
    Dim MedianDB As DAO.Database
    Dim ssMedian As DAO.Recordset
    Dim RCount As Integer, i As Integer, x As Double, y As Double, _
    OffSet As Integer
    Set MedianDB = CurrentDb()
    Set ssMedian = MedianDB.OpenRecordset("SELECT [" & [LoanAmount] & _
    "] FROM [" & qryProgramUpdate & "] WHERE [" & [LoanAmount] & _
    "] IS NOT NULL ORDER BY [" & [LoanAmount] & "];")
    'NOTE: To include nulls when calculating the median value, omit
    'WHERE [" & fldName & "] IS NOT NULL from the example.
    ssMedian.MoveLast
    RCount% = ssMedian.RecordCount
    x = RCount Mod 2
    If x <> 0 Then
    OffSet = ((RCount + 1) / 2) - 2
    For i% = 0 To OffSet
    ssMedian.MovePrevious
    Next i
    Median = ssMedian([LoanAmount])
    Else
    OffSet = (RCount / 2) - 2
    For i = 0 To OffSet
    ssMedian.MovePrevious
    Next i
    x = ssMedian([LoanAmount])
    ssMedian.MovePrevious
    y = ssMedian([LoanAmount])
    Median = (x + y) / 2
    End If
    ssMedian.Close
    MedianDB.Close
    End Function

  2. #2
    Join Date
    Apr 2005
    Location
    Chicago, IL
    Posts
    12

    Hope this will help...

    Just a suggestion...why aren't you including the beginning and end dates from the switchboard in the Selet SQL used to open the recordset (i.e. "Select * FROM tblExample WHERE DateBeginning Between '" & [Form_frmSwitchboard].txtDateBeginning.Value & "' and '" & [Form_frmSwitchboard].txtDateEnding.Value & "'")?

    This is how I would go about getting the switchboard date values into the recordset. You can replace * with whatever field names you need. I am also not sure if it is a single quote ('01/01/2005'), # (#01/01/2005), or [ ([01/01/2005]) to offset the date...play with each and see what works (don't remember off of the top of my head).

    As a shortcut, I usually set up a query that gives me the results I'm looking for and then I switch to SQL view and copy the Select statement to use in my code for recordsets or recordsources. You usually have to do a little clean-up as VBA doesn't usually (that I remember) take all of the parentheses that SQL queries use.

    Hope this helps!!

  3. #3
    Join Date
    Sep 2005
    Posts
    2
    Thanks for the suggestion. I've tried it and a number of other permutations, but no joy. I'm thinking that what I really need to do is just set the date range using the code script that references the values the user types in the switchboard dialogue textboxes. That way, the code sets the date range on the query and performs the median calculation. Do you have any insights on how to set a date range in the VBA code I'm using?

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    When working with Dates in SQL format its

    #mm/dd/yy# USA format

    The where would be Like

    (((able.SDate)>=#1/1/2005# And Table.SDate)<=#2/1/2005#))

    so

    Function Median(qryProgramUpdate As String, LoanAmount As String,optional Date1 as Date,optional Date2 as date) As Single

    Dim NewWhereBit as string

    if not IsMissing(date1) and Not isMissing(date2) THEN
    NewwhereBit = " And (([" & qryProgramUpdate & "].DATEFEILD )>=#" & date1 & "# And ([" & qryProgramUpdate & "].DATEFEILD)<=#" & date1 & "#)"
    else
    NewwhereBit = ""
    end if
    just Need to check the ( ) are the right count this is off the top of my head
    could chnage the Ismissing() to IsDate()

    need to make that
    Set ssMedian = MedianDB.OpenRecordset(".... cleaner so we can read it

    So

    Set MedianDB = CurrentDb()
    Dim SQL as String
    SQL = ""
    SQL = SQL & "SELECT [" & LoanAmount & "]"
    SQL = SQL & " FROM [" & qryProgramUpdate & "]"
    SQL = SQL & " WHERE [" & [LoanAmount] & "] IS NOT NULL "
    SQL = SQL & NewwhereBit
    SQL = SQL & " ORDER BY [" & [LoanAmount] & "];"

    Set ssMedian = MedianDB.OpenRecordset(SQL)

    .....

    ....
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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