Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA code for row difference (A2K2)

    I have succesfully use the code from MSKB Q209839. For ease it is pasted below. It affords row totals to replicate EXCEL in an Access query for bosses wedded to "it looked this way in Excel."

    However, one feature I cannot figure out is how to do a month to month DIFFERENCE from the next to last month to the last month in the query.

    have failed to figure it out on my own. I do not know how to select the last vararg and the next to last vararg

    Any help would be appreciated on writing the code for a difference for the last two month in the report.

    TIA.



    Option Compare Database

    '************************************************* ************
    'Declarations section of the module.
    '************************************************* ************

    Option Explicit


    Function RSum(ParamArray FieldValues()) As Variant
    '--------------------------------------------------
    ' Function RSum() adds all the arguments passed to it.
    ' If all arguments do not contain any data, RSum will return a
    ' null value.
    '--------------------------------------------------
    Dim dblTotal As Double, blnValid As Boolean
    Dim varArg As Variant
    For Each varArg In FieldValues
    If IsNumeric(varArg) Then
    blnValid = True
    dblTotal = dblTotal + varArg
    End If
    Next
    If blnValid Then ' One of the arguments was a number.
    RSum = dblTotal
    Else ' Noo valid points to add.
    RSum = Null
    End If
    End Function

    Function RCount(ParamArray FieldValues()) As Variant
    '-------------------------------------------------
    ' Function RCount() will accept a variable number of arguments,
    ' and returns a count of arguments containing numbers.
    '-------------------------------------------------
    Dim lngCount As Long
    Dim varArg As Variant
    For Each varArg In FieldValues
    If IsNumeric(varArg) Then
    lngCount = lngCount + 1
    End If
    Next
    RCount = lngCount
    End Function

    Function RAvg(ParamArray FieldValues()) As Variant
    '----------------------------------------------------
    ' Function RAvg() will average all the numeric arguments passed to
    ' the function. If none of the arguments are numeric, it will
    ' return a null value.
    '-----------------------------------------------------
    Dim dblTotal As Double
    Dim lngCount As Long
    Dim varArg As Variant
    For Each varArg In FieldValues
    If IsNumeric(varArg) Then
    dblTotal = dblTotal + varArg
    lngCount = lngCount + 1
    End If
    Next
    If lngCount > 0 Then
    RAvg = dblTotal / lngCount
    Else
    RAvg = Null
    End If
    End Function

    Function RStDev(ParamArray FieldValues()) As Variant
    '---------------------------------------------------------
    ' Function RStDev() calculates the Standard Deviation of
    ' sample data passed as arguments. NOTE: The standard deviation
    ' of sample data is only valid if more than one argument is
    ' numeric. If only one of the arguments passed to the function
    ' contains a numeric value, the function will correctly return
    ' a null value.
    '---------------------------------------------------------
    Dim dblSum As Double, dblSumOfSq As Double
    Dim n As Long
    Dim varArg As Variant
    For Each varArg In FieldValues
    If IsNumeric(varArg) Then
    dblSum = dblSum + varArg
    dblSumOfSq = dblSumOfSq + varArg * varArg
    n = n + 1
    End If
    Next
    If n > 1 Then ' Variance/StDev applies if more than a single point
    RStDev = Sqr((n * dblSumOfSq - dblSum * dblSum) _
    / (n * (n - 1)))
    Else
    RStDev = Null
    End If
    End Function

    Function RStDevP(ParamArray FieldValues()) As Variant
    '-----------------------------------------------
    ' Function RStDevP() returns the Standard Deviation of the
    ' Population for all the arguments passed to it. The standard
    ' deviation of the population is only valid for one or more
    ' numeric values. If none of the arguments passed to
    ' the function contains a numeric value, the function will return
    ' a null.
    '-----------------------------------------------
    Dim dblSum As Double, dblSumOfSq As Double
    Dim n As Long
    Dim varArg As Variant
    For Each varArg In FieldValues
    If IsNumeric(varArg) Then
    dblSum = dblSum + varArg
    dblSumOfSq = dblSumOfSq + varArg * varArg
    n = n + 1
    End If
    Next
    If n > 0 Then 'only applies if points available
    RStDevP = Sqr((n * dblSumOfSq - dblSum * dblSum) / n / n)
    Else
    RStDevP = Null
    End If

    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code for row difference (A2K2)

    The functions from MSKB article 209839 are for working with values in different columns (fields) in the same row (record) in a query. If I understand you correctly, you want to compare the value in a certain column to the value in the same column but in the previous row. That is something quite different. There is an KSKB article on that: ACC2000: Referring to a Field in the Previous Record or Next Record. Post back if that is not what you want.

  3. #3
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for row difference (A2K2)

    No,

    I simlp want to creat a row total function identical to the one's in the article but one that will take the difference between the next to last and last column for each row.

    The article refers to them as row totals. That is why I call them row totals.


    There is no previous or next row involved.

    TIA.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code for row difference (A2K2)

    If your query has fixed columns, you can simply use [NameOfLastMonth]-[NameOfNextToLastMonth], with the appropriate names subsituted.

    If your query is a crosstab query, where the names of the columns can change, you would need VBA code to do this - you need to loop through the Fields collection of the QueryDef to find out what the names of the last and next to last columns are.

  5. #5
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for row difference (A2K2)

    The columns are not fixed. The last column and next to last column vary from month to month.

    Hence, the rub.

    The other functions just add or multiply ALL COLUMNS that are NOT null.

    I need to be able to do it for two columns that are different from month to month.

    In Feb subtract Feb from Jan
    In March subtract March from Feb
    In April substract April from Feb
    etc.

    Thanks again.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code for row difference (A2K2)

    This is related to the problem of dynamic column headings in reports based on crosstabs. There have been several discussions about this in the Access forum. If you do a search with keywords dynamic crosstab, you will find them. For example, <post#=35485>post 35485</post#> and <post#=134439>post 134439</post#> and <post#=145832>post 145832</post#> have demos attached. Perhaps you can adapt one of these to your needs.

  7. #7
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for row difference (A2K2)

    Thanks again Hans. I am in fact attempting to implement dynamic crosstab queries based on the MSKB article 328320.

    My row total article solution is an interim resoultuino before I have success with 328320.

    I can see how that can be related to reaching my solution.

    However, the report I am working on is not a dynamic query. It is statis using "In Name of Month" to create a cou,mn for each of the twelve months. Then over the course of the year the months columns are populated.

    Thanks again.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA code for row difference (A2K2)

    I'm getting very confused. You say you're trying to implement dynamic crosstab queries but that your report doesn't use a dynamic query. Then you say you use the name of month to create columns! That sounds dynamic to me. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> And if the columns are only populated over the course of a year, how are you defining the "last" and "next to last" column?
    Charlotte

  9. #9
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for row difference (A2K2)

    What I attempting to do is descreibed in MSKB 209839

    It results in a query returning totals avgs stdev in columns at the right of the query like a spreadsheet. I need a difference. A difference for the last two months in the query no matter which two months they are.

    In the query there are twelve fields defined as Jan Feb Mar Apr etc. to give a count using a sum(iif [DATE FIELD],In Jan Feb Mar Apr etc. There are the fields that give totals avgs stdev.

    Hans mentioned links to dynamic queries. For me that is a separate task where I am also coming up short. FOr the time being I am simply trying to get a difference per the task in MSKB209839. It does seem however, that there will be some sort of dynamic element to whatever solution occurs. So Hans is pointing things in the right direction.


    Thanks.

Posting Permissions

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