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:
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.
RCount% = ssMedian.RecordCount
x = RCount Mod 2
If x <> 0 Then
OffSet = ((RCount + 1) / 2) - 2
For i% = 0 To OffSet
Median = ssMedian([LoanAmount])
OffSet = (RCount / 2) - 2
For i = 0 To OffSet
x = ssMedian([LoanAmount])
y = ssMedian([LoanAmount])
Median = (x + y) / 2
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.
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?
(((able.SDate)>=#1/1/2005# And Table.SDate)<=#2/1/2005#))
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 & "#)"
NewwhereBit = ""
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
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
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.