boolean on dlookup not working

Fordraiders
Fordraiders used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Anthony Berenguel

Commented:
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

Rey Obrero (Capricorn1)
Top Expert 2016

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?
Most Valuable Expert 2014
Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Anthony Berenguel

Commented:
But are you trying to determine if the field "Status" is "In Progress"?
Fordraiders

Author

Commented:
anthony, too many double quotes. by status
Rey Obrero (Capricorn1)
Top Expert 2016

Commented:
@fordraiders,

seen my post?
Nick67
Most Valuable Expert 2014

Commented:
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
Fordraiders

Author

Commented:
cap,

yes type mismatch error..

fordraiders
Rey Obrero (Capricorn1)
Top Expert 2016

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...


.
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
Rey Obrero (Capricorn1)
Top Expert 2016

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
Fordraiders

Author

Commented:
Ended Up going with Nick67 Route. Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial