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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Dale FyeOwner, Developing Solutions LLCCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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
Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.