Compare and warn "not there"

[Bob Quintal]

=?Utf-8?B?TmV0d29ya1RyYWRl?=

Have a macro with the MsgBox action, and enter an appropriate
message to display. In the Condition
DCount("*","YourTable","[LastName]='" & [LastName] & "'")>0

With the form's ComboBox (sourced on a query) this works fine
to warn "if there" with this macro in the BeforeUpdate event.

But have tried to do same thing on a similar ComboBox to warn
if "not there" by changing the > to an =:
DCount("*","YourTable","[LastName]='" & [LastName] & "'")=0

Not getting triggered......not sure why not....

dCount, like all the aggregate functions ignore nulls in their
operation. You can test for this condition with
=Isnull(dcount("*","YourTable","[LastName]='" & [LastName] &
"'")

A sometimes faster way of determining the presence or absence is
to
change to the dLookup() function with the same where clause,
reason being that DLookup stops looking when it hits the first
matching row, DCount has to do every row. If it's not there
DLookup is as slow as DCount.

 

[NetworkTrade]

Have a macro with the MsgBox action, and enter an appropriate message to
display. In the Condition
DCount("*","YourTable","[LastName]='" & [LastName] & "'")>0

With the form's ComboBox (sourced on a query) this works fine to warn "if
there" with this macro in the BeforeUpdate event.

But have tried to do same thing on a similar ComboBox to warn if "not there"
by changing the > to an =:
DCount("*","YourTable","[LastName]='" & [LastName] & "'")=0

Not getting triggered......not sure why not....

 

[NetworkTrade]

Thanks for input. Have stumbled on Nulls more than once.

Yet when I tried - got the "Can not parse" error when I would attempt to
save the Macro (even before it being used in action)....normally I mess up
the various '" ....but in this case it worked ok in the <0 syntax and all I
was doing was dropping that and adding your suggested IsNull in front - - so
it didn't seem like a syntax error....

I guessed maybe the issue is that this not a dcount of a table but of a
query result....but that is just a guess. Since this worked in the <0 mode
it doesn't seem like the correct guess but that's about all I could come up
with at this point......

In any case - I thought of a work around. The Combobox is used to find the
record that form displays and one of the fields in the form has identical
info (assuming the record is found)....so it occurred to me that if the
combobox entry isn't found then it's value will not match the value in the
form's field....and so I wrote a simple if/then to compare those two......

--
NTC

=?Utf-8?B?TmV0d29ya1RyYWRl?=

Have a macro with the MsgBox action, and enter an appropriate
message to display. In the Condition
DCount("*","YourTable","[LastName]='" & [LastName] & "'")>0

With the form's ComboBox (sourced on a query) this works fine
to warn "if there" with this macro in the BeforeUpdate event.

But have tried to do same thing on a similar ComboBox to warn
if "not there" by changing the > to an =:
DCount("*","YourTable","[LastName]='" & [LastName] & "'")=0

Not getting triggered......not sure why not....

dCount, like all the aggregate functions ignore nulls in their
operation. You can test for this condition with
=Isnull(dcount("*","YourTable","[LastName]='" & [LastName] &
"'")

A sometimes faster way of determining the presence or absence is
to
change to the dLookup() function with the same where clause,
reason being that DLookup stops looking when it hits the first
matching row, DCount has to do every row. If it's not there
DLookup is as slow as DCount.