D Functions

[Endre]

Well, you learn something every day. I have been developing Access since v1.1 and constantly learn new things, but this is a new one for me. Yes it's the "D" Functions - but for an interesting use.

For some reason I have stayed away from the them thinking that I can just open up a recordset and do the same things - but I have just found out their powerful use within queries and would like to share the following with you:-

I always have had a "problem" looping around recordsets and, when a field value changes in between records, to perform a particular function. For example: suppose I want to output a list of application forms to separate text files. And I want each object to appear on a different page. And each property of the object I want line separators to split them apart.

Or replicating the Reports function that allows you to "suppress duplicate values for consecutive data" - a variation on the attached will produce same effect.

It's not too difficult to do with VBA - you just need to be careful in your logic when you get to the last record, or when dealing with the first record - else you have to duplicate code or write multiple functions for this. And you are always comparing the next value with the current value (or previous value with current) and having to store your variables and reset them each time. It's just a schlepp that's all (Afrikaans slang used for "Pain in the @$!&")

However, I have just found that using "D"Functions in my queries eliminates all of this.

And I have managed to write a single query that "knows" when the field value in the next record is going to be altered.

In the above example the query can now state:

"For this record - this is the last Object in my form. SO close the text file I am writing to for this form and open up another text file please so we can start looking at the next set of objects on the new form and start writing those"

Or: "This property is the last property (but not the last object), so write separators and start a new page for the next object."

The main advantage of this is that this is a single query that is run once only. You just go down through the records till the end. And it is updateable too.

View attachment DFunctions.mdb

Attached is a Table with 4 fields (All 4 fields combined set the primary Key), and a single query that does all the above. I have not accumulated query formula as I would normally do - as it is currently shown makes it easier to understand.

I don't know why this is such a revelation to me - does anyone know of a better way to do this without VBA? (It runs a little slow with 10,000 records...)

 

[The_Doc_Man]

Re: Look Ma! No VBA! Detecting data changes between successive records by Query only

At my site, I am not allowed to download databases. (Military paranoia about foreign code and macros and such...)

The D functions work perfectly well to do what you suggest provided you also did your homework on table design.

If you know you want to determine the correct order of a record, you define a good primary key and then you can know record order. Just remember, despite your success, it is because you supplied a method to define record order, not because the records were inherently ordered.

On the other hand, if you are able to write VBA comfortably, doing what you described is often easier. But it is all a matter of comfort.

 

[Endre]

Re: Look Ma! No VBA! Detecting data changes between successive records by Query only

Thanks Doc_Man, am very comfortable with VBA and have been for many many years. Just so you can see the output as attached. Or are you not allowed to even see jpg's? What about .zip or .doc or .xls?

Am just so used to writing code this was a bit of a revelation for me.

See attached also for a new "look" to Northwind - my current "project". This "front-end" hooks automatically into your DB and generates it all for you. Am working on a generic interface that works for all backends. Works great so far - am looking for more complex back-ends to test it on, so if you have any......

 

[Guus2005]

Looks impressive!

You might want to consider replacing your D-functions for T-functions because the D-functions are quite slow.

Thanks for your sample database!

Option Compare Database   'Use database order for string comparisons
Option Explicit

' Fast Replacement Functions for DLookup, DCount & DSum , DMax & DMin
'
' Notes:
' Any spaces in field names or table names will probably result in an error
' If this is the case then provide the brackets yourselfs, e.g.
' tLookup("My field","My table name with spaces in") will blow big time
' tLookup("[My field]","[My table name with spaces in]") will be ok
' These functions will not bracket the field/table names for you so as to
' remain as flexible as possible, e.g. you can call tSum() to add or multiply or
' whatever along the way, e.g. tSum("Price * Qty","Table","criteria") or if you're
' feeling adventurous, specify joins and the like in the table name.
'
' See tLookup function for changes from last version
'
' Uses DAO
'
' VB Users
' Get rid of tLookupParam() and the case in the error trapping
' of tLookup() that calls it, this uses a function built-in to
' MS-Access.

Public Enum tLookupReset
    tLookupDoNothing = 0
    tLookupRefreshDb = 1
    tLookupSetToNothing = 2
End Enum

Public Declare Function GetTickCount Lib "kernel32" () As Long

Function TLookup(pstrField As String, pstrTable As String, Optional pstrCriteria As String, Optional pdb As Database, Optional pLookupReset As tLookupReset = tLookupDoNothing) As Variant
    On Error GoTo tLookup_Err
    
    ' Function  tLookup
    ' Purpose:  Replace DLookup, which is slow on attached tables
    '           For where you can't use TbtLookup() if there's more
    '           than one field in the criteria or field is not indexed.
    ' Created:  9 Jan 1996 T.Best
    ' Mod       1 Feb 1996 T.Best
    '   Error Trapping brought in line with this procurement system.
    
    ' Mod       13 Apr 1999 T.Best
    '   Lookups to ODBC datasource use the gdbSQL database object.
    
    ' Mod       14 Apr 1999 T.Best
    '   gdbSQL object no good if doing lookup on a local table, DOH!
    
    ' Mod       11 Jan 2002 G.Hughes
    '   Removed gdbSQL as it was slowing tLookup Down.!!!!!!!!!
    
    ' Mod       Unlogged
    '   Someone put gdbSQL back in
    
    ' Mod       27 Jan 2003 T. Best
    '   Optimise gdbSQL to use Pass-through, it wickedly fast
    
    ' mod       13 Mar 2003
    '   Taken out gdbSQL for redistribution and replaced
    '   the DbEngine with CurrentDB to avoid the now well
    '   documented (in CDMA) DbEngine reference bug.
    '   Added tLookupReset Parameter which does the following
    '   tLookupDoNothing    Do nothing
    '   tLookupRefreshDb    Refreshes collections on the db
    '   tLookupCloseDb      Sets the db to nothing
    '   Also added a db parameter so programmer can call it using
    '   their own db variable, which may be something they opened
    '   elsewhere (Idea by D.Fenton in CDMA).
    
    Static dbLookup As DAO.Database
    Dim rstLookup As DAO.Recordset
    Dim varvalue As Variant
    Dim strSql As String
    
    ' if calling function sends a db then we'll use that
    If Not pdb Is Nothing Then
        Set dbLookup = pdb
    Else
        ' If our db vari is not initialised or the calling
        ' process wants the db objects refreshed then we'll
        ' set the db var using CurrentDb()
        If dbLookup Is Nothing Or pLookupReset = tLookupRefreshDb Then
            If Not dbLookup Is Nothing Then
                Set dbLookup = Nothing
            End If
            Set dbLookup = CurrentDb()
        End If
    End If
    
    
    ' If no criteria specified then we don't even want to get as far
    ' as putting the word "where" in there
    If Len(pstrCriteria) = 0 Then
        strSql = "Select " & pstrField & " From " & pstrTable
    Else
        ' handle those instances where you call tLookup using a field
        ' on a form but can't be bothered to check whether it's null
        ' first before calling, e.g. =tLookup("col1","table","col2=" & txtWhatever)
        ' if txtWhatever was null it would cause an error, this way if there's
        ' nothing after the "=" sign then we assume it was null so we'll make
        ' it look for one.
        ' You may want to handle this differently and avoid looking up
        ' data where the criteria field is null and just always return a
        ' null in which case you'd need to add code to avoid doing the
        ' lookup altogether or just change the criteria to " = Null" as
        ' nothing will ever match with " = Null" so the function would
        ' return null.
        If right$(RTrim$(pstrCriteria), 1) = "=" Then
            pstrCriteria = RTrim$(pstrCriteria)
            pstrCriteria = Left$(pstrCriteria, Len(pstrCriteria) - 1) & " is Null"
        End If
        
        ' build our SQL string
        strSql = "Select " & pstrField & " From " & pstrTable & " Where " & pstrCriteria
    End If
    
    ' now open a recordset based on our SQL
    Set rstLookup = dbLookup.OpenRecordset(strSql, dbOpenSnapshot, dbReadOnly)
    
    ' check if we returned anything at all
    If Not rstLookup.BOF Then
        ' return the value returned in the query
        varvalue = rstLookup(0)
    Else
        ' no records matched, return a null
        varvalue = Null
    End If
    TLookup = varvalue

tLookup_Exit:
    On Error Resume Next
    rstLookup.Close
    Set rstLookup = Nothing
    Exit Function

tLookup_Err:
    Select Case Err
        Case 3061
            ' Error 3061 - Too Few Parameters - Expected x, you know those programmers
            ' should really parse out those form object references for themselves but
            ' we can try to retrieve the situation here by evaluating any parameters
            ' we find in the SQL string.
            TLookup = TLookupParam(strSql, dbLookup)
        Case Else
            ErrorProc Err, Error$, "Tlookup", "modLookup"
'            MsgBox Err.Description, 16, "Error " & Err & " in tLookup() on table " & pstrTable & vbCr & vbCr & "SQL=" & strSQL
    End Select
    Resume tLookup_Exit
    Resume

End Function

Function TCount(pstrField As String, pstrTable As String, Optional pstrCriteria As String, Optional pdb As Database, Optional pLookupReset As tLookupReset = tLookupDoNothing) As Long
    
    ' Function tCount
    ' Purpose: Replace DCount, which is slow on attached tables
    ' Created: 1 Feb 1996 T.Best

    ' TB 28 Jan 2003
    ' Make this call TLookup() so we'll only need concentrate on
    ' one set of error handling code
    TCount = TLookup("count(" & pstrField & ")", pstrTable, pstrCriteria, pdb, pLookupReset)
    
End Function
Function TMax(pstrField As String, pstrTable As String, Optional pstrCriteria As String, Optional pdb As Database, Optional pLookupReset As tLookupReset = tLookupDoNothing) As Variant
    
    ' Function tMax
    ' Purpose: Replace DMax, which is slow on attached tables
    ' Created: 1 Feb 1996 T.Best

    ' TB 28 Jan 2003
    ' Make this call TLookup() so we'll only need concentrate on
    ' one set of error handling code
    TMax = TLookup("max(" & pstrField & ")", pstrTable, pstrCriteria, pdb, pLookupReset)
    Exit Function
End Function

Function TMin(pstrField As String, pstrTable As String, Optional pstrCriteria As String, Optional pdb As Database, Optional pLookupReset As tLookupReset = tLookupDoNothing) As Variant
    
    ' Function tMin
    ' Purpose: Replace DMin, which is slow on attached tables
    ' Created: 1 Feb 1996 T.Best
    
    ' TB 28 Jan 2003
    ' Make this call TLookup() so we'll only need concentrate on
    ' one set of error handling code
    TMin = TLookup("min(" & pstrField & ")", pstrTable, pstrCriteria, pdb, pLookupReset)
    
End Function

Function TSum(pstrField As String, pstrTable As String, Optional pstrCriteria As String, Optional pdb As Database, Optional pLookupReset As tLookupReset = tLookupDoNothing) As Double
    
    ' Function tSum
    ' Purpose: Replace DSum, which is slow on attached tables
    ' Created: 1 Feb 1996 T.Best

    ' TB 28 Jan 2003
    ' Make this call TLookup() so we'll only need concentrate on
    ' one set of error handling code
    TSum = Nz(TLookup("sum(" & pstrField & ")", pstrTable, pstrCriteria, pdb, pLookupReset), 0)

End Function

Function TLookupParam(pstrSQL As String, pdb As Database) As Variant
    ' Called when tLookup, tCount, tMax, tMin or tSum have bombed out
    ' with an expected parameter error, will go and create a querydef
    ' and then attempt to evaluate the parameters
    ' Error Trapped: 12/02/1999 10:21:24 Admin
    On Error GoTo tCountParam_Err
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim prm As DAO.Parameter
    Dim strMsg As String
    Dim i As Long
    
    Set qdf = pdb.CreateQueryDef("", pstrSQL)
    strMsg = vbCr & vbCr & "SQL=" & pstrSQL & vbCr & vbCr
    For i = 0 To qdf.Parameters.Count - 1 ' Each prm In qdf.Parameters
        Set prm = qdf.Parameters(i)
        strMsg = strMsg & "Param=" & prm.Name & vbCr
'        Debug.Print prm.Name
        prm.Value = Eval(prm.Name)
        Set prm = Nothing
    Next
    Set rst = qdf.OpenRecordset()
    rst.MoveFirst
    TLookupParam = rst(0)
    
tCountParam_Exit:
    On Error Resume Next
    Set prm = Nothing
    rst.Close
    Set rst = Nothing
    qdf.Close
    Set qdf = Nothing
    Exit Function
    
tCountParam_Err:
    Select Case Err
        Case Else
            MsgBox Err.Description & strMsg, 16, "Error #" & Err & " In tLookupParam()"
    End Select
    Resume tCountParam_Exit
    Resume
End Function