IIF(Dcount...syntax problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
Click to expand...

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",
"*")
 
Hi MaureenKLM,

This syntax works for me.
Try this.

Like
(IIf((DCount("[County]","tblProperties","[County]='Ocean'")>0),"Ocean","*"))))
 
Many Thanks! It worked!

fredg said:
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?
Click to expand...

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",
"*")
Click to expand...
 
Back
Top