boolean on dlookup not working

Access vba 2010.

I'm trying to run this dlookup and if the field value in "Status" = "In Progress"  then i need a msgbox to pop up ?

If Nz(DLookup("Status", "qry_StatusOpen", Status = "In Progress"), False) = True Then
MsgBox ("You Can Not Have More than One Open Status")
End If

This is not working ?

thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
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.

Anthony BerenguelCommented:
try this
If Nz(DLookup("Status", "qry_StatusOpen", "Status = 'In Progress'"), False) = True Then
MsgBox ("You Can Not Have More than One Open Status")
End If

Open in new window

0
Rey Obrero (Capricorn1)Commented:
try this

If DLookup("Status", "qry_StatusOpen", "Status = 'In Progress'") Then
 MsgBox "You Can Not Have More than One Open Status"
 End If

are you sure there no more other criteria to be consider as part of the Dlookup() statement?
0
Nick67Commented:
You are assuming that
Nz(DLookup("Status", "qry_StatusOpen", Status = "In Progress"), False) = True
Can only return true or false.

And I take it that you believe that it should return false some of the time, and it's not.
It's an odd construction -- and DLookup is evil.
This
DLookup("Status", "qry_StatusOpen", Status = "In Progress")
Is going to return "In Progress" or NULL
and not TRUE

Do it right

Dim db as database
Dim rs as recordset
Set db = Currentdb
Set rs = db.openrecordset(Select Status from qry_StatusOpen where Status = " & chr(34) & "In Progress" & chr(34),dbOpendynaset,dbseechanges )
if rs.recordcount > 1 then
MsgBox ("You Can Not Have More than One Open Status")
end if

rs,close
set rs = nothing
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
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.

Anthony BerenguelCommented:
But are you trying to determine if the field "Status" is "In Progress"?
0
FordraidersAuthor Commented:
anthony, too many double quotes. by status
0
Rey Obrero (Capricorn1)Commented:
@fordraiders,

seen my post?
0
Nick67Commented:
DLookup("Status", "qry_StatusOpen", Status = "In Progress")

I am surprised that compiles
Dlookup needed three string arguments, and this is not good syntax
Status = "In Progress"

Deep-six DLookup.
It's a performance dog

and from your MsgBox I take it you are looking for how many "In Progess"s there are--and there can only be one.

That'd be DCount() -- which is also a dog
0
FordraidersAuthor Commented:
cap,

yes type mismatch error..

fordraiders
0
Rey Obrero (Capricorn1)Commented:
so, the "status" field must be Boolean ?


like I said above


 are you sure there no more other criteria to be consider as part of the Dlookup() statement?

give more info about your data...


.
0
Gustav BrockCIOCommented:
You probably mean:

If Not IsNull(DLookup("Status", "qry_StatusOpen", "Status = 'In Progress'")) Then
    MsgBox "You Can Not Have More than One Open Status"
End If

/gustav
0
Rey Obrero (Capricorn1)Commented:
@fordraiders

can you try this

if dcount("*", "qry_StatusOpen", "Status = 'In Progress'") > 0 then
  msgbox  dcount("*", "qry_StatusOpen", "Status = 'In Progress'")

  else

  msgbox "0"
end if


post back the result
0
FordraidersAuthor Commented:
Ended Up going with Nick67 Route. Thanks
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.

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.