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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Berenguel

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

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

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

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