Option group values with Iif statement

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

Guest

I'm trying to run a query from a form where the user fills in various
parameters with option groups.

The table contains a several different values (ranging from 5.5 to 9) in a
particular field, and I want the query to group them in the query so that all
values from 5.5 to 7 come up in one instance, and all the values from 8 to 9
in another.

In the query grid, if I simply enter, "Between 5.5 And 7", the query runs as
expected and returns all records where the value is within those parameters.
However, if I use an IIf statement such as:

IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,Between 5.5 And
7,Between 8 And 9)

.... the query returns no records whatsoever.

All attempts at using different sorts of bracked, quotes, etc., have failed.
What am I doing wrong?

Kevin
 
BETWEEN IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1, 5.5, 7) AND
IIf(Forms!frmRunTrackModelQuery=1, 8, 9)
 
Almost, but not quite.

The statement returns values between 7 and 9 or between 5.5 and 9. I'm
looking to get the records from 5.5 to 7, or from 8 to 9. By changing the
statement slightly, I get better results but still not perfect:

Between IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,5.5,8) And
IIf([Forms]![frmRunTrackModelQuery]=1,7,9)

This returns the records between 8 and 9 correctly, but the True part
returns all records from 5.5 to 9.



Klatuu said:
BETWEEN IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1, 5.5, 7) AND
IIf(Forms!frmRunTrackModelQuery=1, 8, 9)

--
Dave Hargis, Microsoft Access MVP


Kevin said:
I'm trying to run a query from a form where the user fills in various
parameters with option groups.

The table contains a several different values (ranging from 5.5 to 9) in a
particular field, and I want the query to group them in the query so that all
values from 5.5 to 7 come up in one instance, and all the values from 8 to 9
in another.

In the query grid, if I simply enter, "Between 5.5 And 7", the query runs as
expected and returns all records where the value is within those parameters.
However, if I use an IIf statement such as:

IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,Between 5.5 And
7,Between 8 And 9)

... the query returns no records whatsoever.

All attempts at using different sorts of bracked, quotes, etc., have failed.
What am I doing wrong?

Kevin
Click to expand...
Click to expand...
 
Between IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,5.5,8) And
IIf([Forms]![frmRunTrackModelQuery]=1,7,9)

Shouldn't both comparisons be the same?

[Forms]![frmRunTrackModelQuery]![fraDistance]

[Forms]![frmRunTrackModelQuery]

--
Dave Hargis, Microsoft Access MVP


Kevin said:
Almost, but not quite.

The statement returns values between 7 and 9 or between 5.5 and 9. I'm
looking to get the records from 5.5 to 7, or from 8 to 9. By changing the
statement slightly, I get better results but still not perfect:

Between IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,5.5,8) And
IIf([Forms]![frmRunTrackModelQuery]=1,7,9)

This returns the records between 8 and 9 correctly, but the True part
returns all records from 5.5 to 9.



Klatuu said:
BETWEEN IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1, 5.5, 7) AND
IIf(Forms!frmRunTrackModelQuery=1, 8, 9)

--
Dave Hargis, Microsoft Access MVP


Kevin said:
I'm trying to run a query from a form where the user fills in various
parameters with option groups.

The table contains a several different values (ranging from 5.5 to 9) in a
particular field, and I want the query to group them in the query so that all
values from 5.5 to 7 come up in one instance, and all the values from 8 to 9
in another.

In the query grid, if I simply enter, "Between 5.5 And 7", the query runs as
expected and returns all records where the value is within those parameters.
However, if I use an IIf statement such as:

IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,Between 5.5 And
7,Between 8 And 9)

... the query returns no records whatsoever.

All attempts at using different sorts of bracked, quotes, etc., have failed.
What am I doing wrong?

Kevin
Click to expand...
Click to expand...
Click to expand...
 
Kevin,

If the field contains decimal values, then you could be missing quite a few
buy setting your ranges between 5.5 and 7 or between 8 and 9. What about the
values between 7 and 8?

Dale
--
Email address is not valid.
Please reply to newsgroup only.


Kevin said:
Almost, but not quite.

The statement returns values between 7 and 9 or between 5.5 and 9. I'm
looking to get the records from 5.5 to 7, or from 8 to 9. By changing the
statement slightly, I get better results but still not perfect:

Between IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,5.5,8) And
IIf([Forms]![frmRunTrackModelQuery]=1,7,9)

This returns the records between 8 and 9 correctly, but the True part
returns all records from 5.5 to 9.



Klatuu said:
BETWEEN IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1, 5.5, 7) AND
IIf(Forms!frmRunTrackModelQuery=1, 8, 9)

--
Dave Hargis, Microsoft Access MVP


Kevin said:
I'm trying to run a query from a form where the user fills in various
parameters with option groups.

The table contains a several different values (ranging from 5.5 to 9) in a
particular field, and I want the query to group them in the query so that all
values from 5.5 to 7 come up in one instance, and all the values from 8 to 9
in another.

In the query grid, if I simply enter, "Between 5.5 And 7", the query runs as
expected and returns all records where the value is within those parameters.
However, if I use an IIf statement such as:

IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,Between 5.5 And
7,Between 8 And 9)

... the query returns no records whatsoever.

All attempts at using different sorts of bracked, quotes, etc., have failed.
What am I doing wrong?

Kevin
Click to expand...
Click to expand...
Click to expand...
 
Yep. That's got it. Many thanks.

Kevin


Klatuu said:
Between IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,5.5,8) And
IIf([Forms]![frmRunTrackModelQuery]=1,7,9)

Shouldn't both comparisons be the same?

[Forms]![frmRunTrackModelQuery]![fraDistance]

[Forms]![frmRunTrackModelQuery]

--
Dave Hargis, Microsoft Access MVP


Kevin said:
Almost, but not quite.

The statement returns values between 7 and 9 or between 5.5 and 9. I'm
looking to get the records from 5.5 to 7, or from 8 to 9. By changing the
statement slightly, I get better results but still not perfect:

Between IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,5.5,8) And
IIf([Forms]![frmRunTrackModelQuery]=1,7,9)

This returns the records between 8 and 9 correctly, but the True part
returns all records from 5.5 to 9.



Klatuu said:
BETWEEN IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1, 5.5, 7) AND
IIf(Forms!frmRunTrackModelQuery=1, 8, 9)

--
Dave Hargis, Microsoft Access MVP


:

I'm trying to run a query from a form where the user fills in various
parameters with option groups.

The table contains a several different values (ranging from 5.5 to 9) in a
particular field, and I want the query to group them in the query so that all
values from 5.5 to 7 come up in one instance, and all the values from 8 to 9
in another.

In the query grid, if I simply enter, "Between 5.5 And 7", the query runs as
expected and returns all records where the value is within those parameters.
However, if I use an IIf statement such as:

IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,Between 5.5 And
7,Between 8 And 9)

... the query returns no records whatsoever.

All attempts at using different sorts of bracked, quotes, etc., have failed.
What am I doing wrong?

Kevin
Click to expand...
Click to expand...
Click to expand...
 
Good catch, Dale. Excellent question.
--
Dave Hargis, Microsoft Access MVP


Dale Fye said:
Kevin,

If the field contains decimal values, then you could be missing quite a few
buy setting your ranges between 5.5 and 7 or between 8 and 9. What about the
values between 7 and 8?

Dale
--
Email address is not valid.
Please reply to newsgroup only.


Kevin said:
Almost, but not quite.

The statement returns values between 7 and 9 or between 5.5 and 9. I'm
looking to get the records from 5.5 to 7, or from 8 to 9. By changing the
statement slightly, I get better results but still not perfect:

Between IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,5.5,8) And
IIf([Forms]![frmRunTrackModelQuery]=1,7,9)

This returns the records between 8 and 9 correctly, but the True part
returns all records from 5.5 to 9.



Klatuu said:
BETWEEN IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1, 5.5, 7) AND
IIf(Forms!frmRunTrackModelQuery=1, 8, 9)

--
Dave Hargis, Microsoft Access MVP


:

I'm trying to run a query from a form where the user fills in various
parameters with option groups.

The table contains a several different values (ranging from 5.5 to 9) in a
particular field, and I want the query to group them in the query so that all
values from 5.5 to 7 come up in one instance, and all the values from 8 to 9
in another.

In the query grid, if I simply enter, "Between 5.5 And 7", the query runs as
expected and returns all records where the value is within those parameters.
However, if I use an IIf statement such as:

IIf([Forms]![frmRunTrackModelQuery]![fraDistance]=1,Between 5.5 And
7,Between 8 And 9)

... the query returns no records whatsoever.

All attempts at using different sorts of bracked, quotes, etc., have failed.
What am I doing wrong?

Kevin
Click to expand...
Click to expand...
Click to expand...
 
Back
Top