Avatar of Fordraiders
Fordraiders
Flag for United States of America asked on

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
Microsoft AccessVBA

Avatar of undefined
Last Comment
Fordraiders

8/22/2022 - Mon
Anthony Berenguel

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)

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?
ASKER CERTIFIED SOLUTION
Nick67

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Anthony Berenguel

But are you trying to determine if the field "Status" is "In Progress"?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Fordraiders

ASKER
anthony, too many double quotes. by status
Rey Obrero (Capricorn1)

@fordraiders,

seen my post?
Nick67

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Fordraiders

ASKER
cap,

yes type mismatch error..

fordraiders
Rey Obrero (Capricorn1)

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 Brock

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Rey Obrero (Capricorn1)

@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

ASKER
Ended Up going with Nick67 Route. Thanks