Make Table Query type incorrect

[El-d]

Hi All,

I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.
However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.

I've added an extra button and Make Table query to the example.

As far as I can tell from the code (i'm not very good at it) , the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.

Probably something simple (hopefully)but how do I make the created Table use Number Type for my calculated moving average?

Thanks
El-d
PS Using Access 2013

 

[Isskint]

Hi El-d

I suspect your problem lies in a couple of possible areas.
First the function declaration does not specify what variable type to return. Change it to

Function RunAvgQ(QueryName As String, KeyName As String, KeyValue, _
                 FieldNameToGet As String, RunLength As Integer) as Single

if your returning a single. Without it you are returning a Variant type variable.

The second part is where you calculate the sum. i would add in the Val() function, so

CalcAvg = CalcAvg + Val(RS(FieldNameToGet))

 

[El-d]

Thanks for the response. Apart from requiring

RunAvgQ = "0"
instead of
RunAvgQ = ""

due to a mismatch it works perfectly in the test file.

Annoyingly on my actual data, I'm not getting my running average. Its using the RunAVgQ 0 error value.

What I don't get is that I'm trying to debug print recordcount, runlength values etc to determine why its not calculating but my debug print doesn't work. It works up to a point in the code but then nothing. See code below for where it stops.
It also works in the Err_RunAvgQ:
Any idea why I can't debug print?

When I test in the demo example, I can print anywhere but in my actual DB it stops. The function is a direct copy so I'm utterly confused as to why it can print in one but not the other.

Thanks,
El-d

Function RunAvgQ(QueryName As String, KeyName As String, KeyValue, _
                 FieldNameToGet As String, RunLength As Integer) As Double
                 
    '*************************************************************
    ' FUNCTION: RunAvgQ()
    ' Note: Much of this code is from the PrevRecVal function from the Microsoft KB.
    ' PURPOSE: Calculate a running average within a saved, non-dynamic query,
    '          based on a given field and number of records to work back through.
    ' PARAMETERS:
    '    QueryName      - The name of the saved query with the running average.
    '    KeyName        - The name of the query's unique key field.
    '    KeyValue       - The current record's key value.
    '    FieldNameToGet - The name of the field in the previous
    '                     record from which to retrieve the values.
    '    RunLength      - The length of the running average
    ' RETURNS: The value in the field FieldNameToGet from the
    '          previous query record.
    ' EXAMPLE:
    '    =RunAvgQ("qryRunAvgMileage","ID",[ID],"OdometerReading",4)
    '**************************************************************
  
    Dim RS As Recordset
    Dim db As Database
    Dim qdef As QueryDef
    Dim i As Integer, CalcAvg As Single, strSQL As String, DataLineID As Integer
    Dim SampWt As Single, Wt As Single
    
    On Error GoTo Err_RunAvgQ

    Set db = CurrentDb()
    
'   Grab the SQL for the query, so we can create a duplicate recordset
    Set qdef = db.QueryDefs(QueryName)
    strSQL = RemoveFormReference(qdef.SQL)

[B][COLOR="Red"]--DEBUG PRINT WORKS UP TO HERE[/COLOR][/B]

'   Open a duplicate recordset to that of the query calling this function
    Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)
'   The following original line of code doesn't work if you reference a form to filter to
'   a certain set of records (which I do).
'   It responds with a wildly misleading error that says you have used too few arguments.
'   Set RS = db.OpenRecordset("Run Avg rpt query", dbOpenDynaset)

    ' Find the current record.
    Select Case RS.Fields(KeyName).Type
    ' Find using numeric data type key value?
    Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, DB_DOUBLE, DB_BYTE
        RS.FindFirst "[" & KeyName & "] = " & KeyValue
    ' Find using date data type key value?
    Case DB_DATE
        RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
    ' Find using text data type key value?
    Case DB_TEXT
        RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
    Case Else
        MsgBox "ERROR: Invalid key field data type!"
        Exit Function
    End Select

'   Running Average code.  The On Error will cause the returning value to be
'   blank if the current record is lower than the length of the running average.
    
'   First, check to see if the query has at least enough records to do a
'   running average.
    If RS.RecordCount > RunLength Then
        CalcAvg = 0
        For i = 1 To RunLength
'           Add the previous values
            CalcAvg = CalcAvg + Val(RS(FieldNameToGet))
'           Move to the previous record.
            RS.MovePrevious
        Next i
        
'       Divide by the run length for the average of the accumulated values
        CalcAvg = CalcAvg / RunLength
'       Return the result.
        RunAvgQ = CalcAvg
    End If
            
    RS.Close

Bye_RunAvgQ:
    
    Exit Function

Err_RunAvgQ:
    
'   This function is designed to return a blank value for any record number
'   lower than the length of the running average.  So, for a running average
'   of 4, the first three records in the query will have a blank for the
'   running average.
    RunAvgQ = "0"
    Resume Bye_RunAvgQ

End Function

 

[El-d]

Hi Isskint,

I'm obviously doing something really stupid as I just can't get this to work.

I've added a stripped down version of my data.
The form has 2 buttons - one just runs the query which works and the other makes a new table using the same query which fails.

Would really appreciate if you could take a look as its driving me mad.
Thanks
El-d

 

[spikepl]

RunAvgQ = "0" does not jive with your function returning a Double. It should be
RunAvgQ = 0

 

[El-d]

That moment when I wanted to slap myself.....but I just tried that and it still doesn't work.
I'm assuming that as the field is specified as a double that using "0" does actually enter as a number rather than a string which is what I assume you meant.

:banghead:

El-d


PS. like the use of Jive...not heard it used in that sort of context in a long time

 

[El-d]

Found out my problem. Schoolboy error probably .

I was re-targeting my query so instead of targeting Mavg30 query, I'd changed it to the make table and it didn't work.

RunAvg: RunAvgQ("MAvg30","SMAID",[SMAID],"AdjustedHistory",30)

When I kept the query and created a make table, it worked.

Really don't get enough time to spend on this

All good now, until I hit my next problem

EL-d

 

[El-d]

Back again

Quick additional question hopefully.

I've changed my function to return As Single but it returns as double.
As a test, I changed it to return As Integer and that works correctly but I can't get it to return as single.

Any thoughts?

Cheers,
El-d