Better code then dcount to check for entry in table

I am using the following code with a dcount to search if an entry in a table exists... I feel like there is a more efficient way then using dcount twice.

Suggestions are appreciated.

Function ReceiptCK() As Boolean 'Check for office service and PT payment entry
   
    If DCount("*", "tClaimHistory", "ClaimNum=" & [Forms]![FPEntryQuery]![FClaimQuery]![tClaimNum] & " AND Status = ""PNT - Paid""") = 0 Then
        MsgBox "Payment (PMNT) entry needed to create a receipt", vbInformation, "Receipt"
        ReceiptCK = False
    ElseIf DCount("*", "tClaimHistory", "ClaimNum=" & [Forms]![FPEntryQuery]![FClaimQuery]![tClaimNum] & " AND Status = ""Office Service""") = 0 Then
        MsgBox "Office service (OSrv) entry needed to create a receipt", vbInformation, "Receipt"
        ReceiptCK = False
    Else
        ReceiptCK = True
    End If

End Function

Open in new window

thandelAsked:
Who is Participating?
 
Bill PrewCommented:
I am using the following code with a DCOUNT to search if an entry in a table exists... I feel like there is a more efficient way then using DCOUNT twice.

Based on that statement I am assuming it is providing the results you desire, you were just looking for optimization potential.

But since you are actually checking the existence of two different records (different status values) I would say your approach is reasonable.  Since you want to display a different MsgBox depending on the existence of each status record, then you can't put both statuses into a single DCOUNT() using IN or OR in the WHERE clause.

Unless you are having performance problems I would let sleeping dogs lie and use what you have that is working.


»bp
0
 
PatHartmanCommented:
Is this what you are trying to do?
Function ReceiptCK() As Boolean 'Check for office service and PT payment entry
    Dim sStatus as String   
    sStatus = DLookup("Status", "tClaimHistory", "ClaimNum=" & [Forms]![FPEntryQuery]![FClaimQuery]![tClaimNum] 
    Select Case sStatus 
        case "PNT - Paid" 
            MsgBox "Payment (PMNT) entry needed to create a receipt", vbInformation, "Receipt"
            ReceiptCK = False
        Case "Office Service"
            MsgBox "Office service (OSrv) entry needed to create a receipt", vbInformation, "Receipt"
            ReceiptCK = False
        Case Else
            ReceiptOK = True
    End Select
End Function

Open in new window

1
 
Pallavi GodseSr. Digital Marketing ExecutiveCommented:
Try using DLookup instead of dcount.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Exactly how would DLookup be more beneficial than DCount? Just curious, as they would perform the same task in this example.

I don't really see a problem with what you're doing, assuming you'll only have 2 items to work with. You're only executing one of those DCount statements at best, so the number of those statements in code is irrelevant.

The SELECT CASE statement Pat suggested seems "cleaner", and is easier to read (i.e. more manageable).
0
 
Dale FyeCommented:
The advantage of DLOOKUP is that it will return the first value found which matches your criteria, rather than searching the entire recordset to get the count, so it is probably a little bit faster than DCOUNT.

If you only have one record in your table (tClaimHistory) for each [ClaimNum], then Pat's SELECT Case with a single DLOOKUP( ) would be more efficient.  

But if this table contains a history of status values, with dates, then the dcount method would work.  As would creating a recordset filtered on the ClaimNum, then use the rs.FindFirst method to search for the records with particular status values.  This method might be quicker, but would involve more coding and "quicker" is relative.  The following is air code (not tested)

Dim rs as DAO.Recordset
Dim arrSearch() as string
Dim intLoop as integer 
Dim strSQL as string
arrSearch() = Split("PNT - PAID,Office Service", ",")

'set default value
ReceiptCK = true

strSQL = "SELECT Status FROM tCliamHistory WHERE [ClaimNum] = " & [Forms]![FPEntryQuery]![FClaimQuery]![tClaimNum]
set rs = currentdb.openrecordset(strSQL, dbopendynaset)
with .rs
    For intLoop = lbound(arrSearch) to ubound(arrSearch)
        .FindFirst "Status = '" & arrSearch(intLoop) & "'"
        if .nomatch AND intLoop = 1  
            MsgBox "Payment (PMNT) entry needed to create a receipt", vbInformation, "Receipt"
            ReceiptCK = False
            Exit For
        elseif .nomatch and intLoop = 2 then
            MsgBox "Office service (OSrv) entry needed to create a receipt", vbInformation, "Receipt"
            ReceiptCK = False
        end if
    Next
End with
rs.close
set rs = nothing

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't think you're right about DCOUNT being less efficient than DLOOKUP, especially when using DCOUNT on an indexed field (or just using *, which tells Access to use the "best" index). COUNT functions - at least in SQL Server - are significantly faster than any data lookup functions. I assume they're similar in Jet/ACE as well.
0
 
mbizupCommented:
Here's another variation, untested...  (but I think performance -wise, these all might be splitting hairs):

Function ReceiptCK() As Boolean 

Dim s as string
dim sCrit as string

dim rs as dao.recordset

On Error Goto PROC_ERR

sCrit =  "ClaimNum=" & [Forms]![FPEntryQuery]![FClaimQuery]![tClaimNum]
s =  "SELECT SUM(Status = 'PNT - PAID') AS Paid,  SUM(Status = 'Office Service')  AS OfficeService FROM tClaimHistory WHERE " & scrit

set rs = currentdb.openrecordset (s, dbopendynaset, dbseechanges)

ReceiptCK = (rs!Paid Or rs!OfficeService )

if rs!Paid = False then 
    MsgBox "Payment (PMNT) entry needed to create a receipt", vbInformation, "Receipt"
    goto PROC_EXIT
end if

if rs!OfficeService  = False then 
    MsgBox "Office service (OSrv) entry needed to create a receipt", vbInformation, "Receipt"
    goto PROC_EXIT
end if

PROC_EXIT:
      rs.Close
      set rs = nothing
      Exit Function
PROC_ERR:
    msgbox "ERROR " & err.number & ": " & Err.description
end function

Open in new window

1
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I posted in another group regarding DCount and DLookup, but I'll post here for clarity:

I created a test database, with a table that contains 2 fields: ID (AN field) and Field1 (a Text field), and created and index on Field1. I added 500,000 records. I then ran code to test for both DCount and DLookup:

cnt = DLookup(“ID”, “Table1”, “Field1=’200000’”)

cnt = DCount(“*”, “Table1”,  “Field1=’200000’”)

Neither was better at performance than the other. They both report nearly identical return values. Interestingly, DCount consistently reported the same time (3 milliseconds), while DLookup occasionally returned 2, 3, or 4 milliseconds.

Note that prior to indexing the field, DCount was noticeably faster, by a factor of 5 or more, when dealing with values that were not close to the front of the recordset (records in the 10,000+ mark). Inside of the first 10,000 records, the values were pretty much the same.

Moral of the story is: Neither is better. Use what makes the most sense to your application.
1
 
Bill PrewCommented:
Is ClaimNum unique in tClaimHistory, or can there be multiple rows for the same ClaimNum with different Status values?


»bp
0
 
thandelAuthor Commented:
ClaimNum can have multiple rows
0
 
thandelAuthor Commented:
Thanks PatHartman, Love the case statement but its not 100% correct.... I'm basically looking for missing entries... so I believe your code will hit only if it finds an entry which is just the opposite.
0
 
PatHartmanCommented:
ClaimNum can have multiple rows
 The case statement wouldn't work anyway if there were multiple rows.  Please understand any code I wrote was a complete guess since I had no idea what you wanted to do.  And I still don't know what you are looking for since you haven't said anything except that what you were doing wasn't what you wanted.
0
 
Dale FyeCommented:
Several MVP's have been discussing this post off-line and doing some testing.

If the table you are searching is indexed on ClaimNum and Status, then it is likely that the DLookup will be faster (as much as 2-3 times) then DCount (but keep in mind we are talking milli-seconds).  But if the fields being searched on are not both indexed, the difference between the two decreases significantly,
0
 
thandelAuthor Commented:
OK thanks for the information from all.... not sure the best comment to select so let me know if I'm not correct in my selection.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.