[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Using DLookup and multiple criteria to return value

Posted on 2006-05-24
Medium Priority
Last Modified: 2008-02-01
Whenever I run this code, it brings back an error.  I have tried all kinds of changes and nothing seems to work.  Any ideas?

Code is as follows:

Public Function CheckACCT_ADD(ByVal sCASEID As Integer) As Boolean
On Error GoTo ErrorHandling
    Dim bOutput As Boolean
    Dim varX As Variant
    Dim myrecordset As DAO.Recordset
    Dim myDatabase As DAO.Database
    Set myDatabase = CurrentDb
    Set myrecordset = myDatabase.OpenRecordset("CPTYCASE_STS", dbOpenDynaset)
    varX = Nz(DLookup("[CPTYCASE_ID]", "CPTYCASE_STS", "[CPTYCASE_ID] = " & Me!txtCaseID And "[STS_ID] = 54"))
    If varX = Me.txtCaseID.Value Then
            bOutput = True
            bOutput = False
        End If
    Set myrecordset = Nothing
    Set myDatabase = Nothing

    CheckACCT_ADD = bOutput
    Exit Function
    MsgBox Err.Description
    Resume ExitHere
    Exit Function

End Function
Question by:jcpeve
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16756710
This any better?

varX = Nz(DLookup("[CPTYCASE_ID]", "CPTYCASE_STS", "[CPTYCASE_ID] = " & Me!txtCaseID & " And [STS_ID] = 54"))
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16756713
Hmm - but why do you open a recordset?  :-S
LVL 19

Accepted Solution

Eric Sherman earned 2000 total points
ID: 16756774
Is [CPTYCASE_ID] Text or Numeric???

Try this ...

varX = Nz(DLookup("[CPTYCASE_ID]", "CPTYCASE_STS", "[CPTYCASE_ID] = '" & Me!txtCaseID & "' And [STS_ID] = 54"))


Expert Comment

ID: 16757001
Have you tried tagging out the errorhandler and seeing which line is causing the problem?
LVL 65

Expert Comment

ID: 16759041
just a thought

u pass in a integer variable

Public Function CheckACCT_ADD(ByVal sCASEID As Integer) As Boolean

Now this function is public, I would assume this is in a module? There is no real need to create public procedures in forms, unless u want another form to access it.
incidentally, prefixing with s usually refers to a string, prefix with i e.g. iCaseID

If this is in a module and u want to check the variable passed in, then the code has to use that i.e.  Me!txtCaseID refers to a txtbox on the form and this assumes to be form code then

varX = Nz(DLookup("CPTYCASE_ID", "CPTYCASE_STS", "CPTYCASE_ID = " & sCaseID & " And STS_ID = 54")

Note, u do not need square brackets, thats only useful if your fields has spaces in them

another thing, DLOOKUP returns null if no records found, but you have wrapped with NZ so there is no need to define it as a variant
Dim varX As Variant

Also like Leigh says, why have u defined a recordset if you dont plan on using it?

You can clean up your code, either use a recordset to find it
in that case the open call is

Set myrecordset = myDatabase.OpenRecordset("SELECT CPTYCASE_ID FROM CPTYCASE_STS WHERE CPTYCASE_ID = " & sCaseID & " AND STS_ID= 54", dbOpenDynaset)

or simply use DLOOKUP

Now it seems all you doing is checking the record exists, so u could just do this

IF IsNull(DLookup("CPTYCASE_ID", "CPTYCASE_STS", "CPTYCASE_ID = " & sCaseID & " And STS_ID = 54")) Then
    bOutput = False
    bOutput = True
end if

or if form code, replace sCaseID with txtCaseID.value


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

872 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