Option group values with Iif statement

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
 
G

Guest

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

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ACCESS - how do I apply a IIF = statement without parameter value prompt. The two column comparison is count 1
passing value from a form with IIF statement in a query 1
Including Null values in query design 4
IIF Statement? 7
Bad IIf 1
IIf Statement in Query Criteria 1
IIf statement not working 4
Option Group Type Mismatch 1

Top