IIF(Dcount...syntax problem

[Guest]

HELP! I am trying to create a query criteria that returns specific records
based on specific criteria IF there are records to display meeting that
criteria - otherwise display ALL records without any criteria

I have a table called tblproperties - one field is {County] - when I query
for a specific county, for example "OCEAN" then I want to see all records
that satisfy that criteria...but if there are NO records with OCEAN in the
[County] Field, then I want to see ALL RECORDS regardless of the county.

Here's what I have in the Query on the Criteria row for the County field :
IIf((DCount("[County]","tblProperties","[County]='Ocean'")>0),"Ocean",Like
"*")

Something is wrong with the Like "*" expression - can you assist and
correct my syntax?

 

[fredg]

HELP! I am trying to create a query criteria that returns specific records
based on specific criteria IF there are records to display meeting that
criteria - otherwise display ALL records without any criteria

I have a table called tblproperties - one field is {County] - when I query
for a specific county, for example "OCEAN" then I want to see all records
that satisfy that criteria...but if there are NO records with OCEAN in the
[County] Field, then I want to see ALL RECORDS regardless of the county.

Here's what I have in the Query on the Criteria row for the County field :
IIf((DCount("[County]","tblProperties","[County]='Ocean'")>0),"Ocean",Like
"*")

Something is wrong with the Like "*" expression - can you assist and
correct my syntax?

Like comes first. And you can use the * in the DCount as you are
counting [County] = 'Ocean' and not trying to return the data in the
County field.

Like IIf((DCount("*","tblProperties","[County]='Ocean'")>0), "Ocean",
"*")

 

[Guest]

Hi MaureenKLM,

This syntax works for me.
Try this.

Like
(IIf((DCount("[County]","tblProperties","[County]='Ocean'")>0),"Ocean","*"))))

 

[Guest]

Many Thanks! It worked!

HELP! I am trying to create a query criteria that returns specific records
based on specific criteria IF there are records to display meeting that
criteria - otherwise display ALL records without any criteria

I have a table called tblproperties - one field is {County] - when I query
for a specific county, for example "OCEAN" then I want to see all records
that satisfy that criteria...but if there are NO records with OCEAN in the
[County] Field, then I want to see ALL RECORDS regardless of the county.

Here's what I have in the Query on the Criteria row for the County field :
IIf((DCount("[County]","tblProperties","[County]='Ocean'")>0),"Ocean",Like
"*")

Something is wrong with the Like "*" expression - can you assist and
correct my syntax?

Like comes first. And you can use the * in the DCount as you are
counting [County] = 'Ocean' and not trying to return the data in the
County field.

Like IIf((DCount("*","tblProperties","[County]='Ocean'")>0), "Ocean",
"*")