WHEN REPLACED DSUM WITH TSUM - how to use expression instead of the name of the field

Posted on 2007-10-17
Last Modified: 2013-11-27
Hi, I have such problem: I have to create an application with MS Access, and I found that using the DSUM function is very slow, in order to make the process of data faster I red about replacing DSUM with other function - you called it TSUM. Anyway the TSUM function uses three parameters ,the first one is the name of the field, but in my case it is not a field,but expression and the function doesn't work.Do you have any idea how this problem can be solved? in my case:
tIZRAZ_FRZ = DLookup("[FRZ]", "ZAPD")
Question by:katia_ibs
    LVL 19

    Expert Comment

    TSUM is not a valid Access function, create a recordset instead of the DSUM
    LVL 19

    Accepted Solution

        Dim tIZRAZ_FRZ As String 'whatever data type
        Dim tFRZ As Double 'whatever data type
        Dim d As DAO.Database, r As DAO.Recordset, sql As String, rs As DAO.Recordset      
        Set d = CurrentDb
        '... whatever code to get rs!nomer
        Set rs = d.OpenRecordset("whatever sql")    '....then go to whatever record
        tIZRAZ_FRZ = DLookup("[FRZ]", "ZAPD") 'you are missing an argument here, the criteria    
        sql = "select sum(" & tIZRAZ_FRZ & ") as mySUM from zapnew where [nomer] = " & rs!nomer 'whatever
        Set r = d.OpenRecordset(sql)
        If r.EOF Then
            MsgBox "record " & rs!nomer & " does not exist!", vbExclamation
            tFRZ = r!mySum
            MsgBox "value is " & tFRZ
        End If

        Set d = Nothing

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now