Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010

    Unanswered: SQL Function Continuous Recalculation upon Criteria Application

    I wrote a function which I call via a simple SQL SELECT statement. The SQL passes the field info to the function it calculates correctly and returns the result to a new query field.

    The problem arises however when I attempt to apply criteria (with WHERE) to the newly calculated field (query field). It seems that field argument of the function is again passed to the function -- this time based on the field data of the newly criteria applied recordset, causing it to recalculate -- and to return different results (as the calculation is dependent on the records passed into the function via the field argument of the function) (though correctly calculated they are not what is expected or desired in this case).

    As you might be able to surmise, I am working with time series data, hence the importance of the records passed, and their order.

    I know I could tinker with this programatically, with arrays, and achieve the result I am looking for, but it seems like it should be so simple to do with SQL. Could someone fill me in on what I am missing. Why can i not just rerference the field that has just been calculated, and filter its results down (without in so doing causing it to recalculate)? And if one can, how?

    Many thanks in advance for any help and or thoughts.
    Last edited by accro; 11-28-10 at 18:51. Reason: Better Title

  2. #2
    Join Date
    Nov 2010

    Dlookup doesnt work either

    Incidentally, I have been trying to trick Access out by using a Dlookup function.

    First I run the qurey withoug criteria, get correct fucntion calculated field.
    Next I generate a new query, which applies the desired criteria to the caluclated field from the query in the first step, but doesn not return the calculated field, only the identifier.
    Last, I generate a thrid query which pulls the unique ID from the second query and generates a Dlookup field which looks up the desired field from the first query, based on its specified unique ID from the criteria applied Second Query.

    Well guess what, it doesnt work. It actually returns blank data for the Dlookup. I do step through the code, and for some crazy reason, the Dlookup calls the function again (why doesnt it just reference the values??), moreover, the function returns the correct calculations (i see in the code), but I cant see where they go, they dont wind up in the Dlookup results -- its blank, they just get dropped for no apparent reason. Also no errors are generated.

    Ive been on this for days, honestly, Im ashamed to admit it. I've used SQL before for simple databse construction but I guess I really dont know what is going on. Can anybody help? There has got to be a way to return a calculated field that is not perpetually volatile.

    many thanks in advance for any suggestions or point me in the right direction -- i can post code if it would help -- though its rather simple, both the SELECT querys and criteria appliction as well as the function code. Thanks again

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    I will utilize functions within a query to do some calculations.

    To do this, my function will look like this:

    Function retCalcValue(varSomeVariablePassedToFunction as Variant) as Variant
    ...calculating here...
    retCalcValue = someresulthere
    End Function

    and then in my query, my expression will look like this...

    MyExpression: retCalcValue([MyFieldName])

    This would then return the value for that record where MyFieldName is the field name I want to calculate on.

    If you have a lot of records, consider doing this versus the Dlookup in your expression. This would be faster. But in your function, you have to be careful that your SQL statement to open a recordset doesn't conflict with the query itself.

    ex (using ADO):
    Function retCalcValue(varSomeVariablePassedToFunction as Variant) as Variant
    dim strSQL as string
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    strSQL = "Select * from MyTableName where MyIDField = " & varSomeVariablePassedToFunction & "" strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    retCalcValue = null
    retCaclValue = rs!SomeField * 3 (for example)
    end if
    set rs = nothing
    End Function

    Note that the above calculation would be more complicated in a real world since it would be easier with the above example to just simply calculate the value in the query itself (ie. MyExpression: [MyFieldName] * 3)

    Otherwise, to use the dlookup as an expression within the query, you may need to put criteria within the dlookup.

    MyExpression: Dlookup("[SomeField]","MyTableOrQueryName","[IDField] = " & [MyIDFieldInQuery] & "")

    I hope this helps.
    Last edited by pkstormy; 11-28-10 at 16:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2010

    SQL Function Continuous recalculation upon Criteria Application

    Thank you very much for your reply and thoughts,

    I probably should have pasted in some code, to show how far along I am, and to demonstrate the problem specifics.

    Basically, I use exactly your method (w correct sytnax) to call the function, this part calculates correctly, here is the SQL

    SELECT dailyreturn([AAP].[PRICE],[AAP].[DATE]) AS Return, AAP.Date, [Return] AS Return2
    WHERE (((AAP.Price) Is Not Null));

    The function is simple, it takes 2 variables (but really only uses 1) and returns the calculated field:

    Function dailyreturn(return2, Date2 As Date) As Double
    On Error GoTo Handler
    Dim DB As Database
    If IsNull(return2) Then
    showdate = Date2
    dailyreturn = log(return2 / savedreturn)
    savedreturn = return2
    Exit Function
    End If
    dailyreturn = 0
    savedreturn = return2
    End Function

    Thus the query returns the desired calculated field et al.

    It is when I try to apply criteria to the 'Return' calculated field that everythign goes wrong. If I apply it with a simple WHERE, on that column (Return), the function is called again, but now on a smaller recordset, hence changing the calculation).

    In my code, as you can see, I do not explicityl create a recordset for the function as I would do in a SUB, my undefstanding was that the function was passed info 1 record at a time and hence you could not retrieve the whole recordset with one call to the function. In any evernt, shouldnt my method be exactly the same, as all we re interested in is the calculated field to be returned and to do no further manipulations on the data?

    Regarding the dlookup, my syntax and methodology once again is just as yours below (ive spent a lot of time reading these boards trying to get it right)
    Hence my attempt to 'trick out' Access and to just simply "LOKK UP" the correctly calculated results of the very first query, hence I did this with 2 additional queriezs, and here is the dlookup field of the final, expecteantly correct (but invariably blank) query field:
    Return3: DLookUp("[Return]","Query1","[date] =" & [Query2].[Date])

    (once again Query 2 was where I simply applied a filer to the 'Return' filed, just for purposes of getting the correct criteria applied date.)

    So Once again, thanks very much for your consideration. This is probably so simple and Im just missing something I have no idea exists, perhaps youll tell me i need to use the recordset in my function, though i cant see exactly why that would be neccessary Thanks!

Tags for this Thread

Posting Permissions

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