Problem with query expression

[Martin Watts]

I'm being dense or just can't see the woods for the trees.

I have a table which holds details of the start and end dates of training
courses. I have a query which uses a parameter from a form to select
courses running in any chosen year and have an calculated field to determine
if the course is running in quarter 2 of that year but it's returning
incorrect results and I just cannot see what is wrong with the expression.

The fields in the query are

YRQTRST - The year and qtr in which the course starts e.g. 13 for courses
starting in the 3rd quarter of year 1
YRQTREND - As above but the year and quarter in which the course ends
YRSTART - The year in which the course starts
YREND - The year in which the course ends
CURRENT - Should return "Running" or "Not Running" if the course is running
in the second quarter of any selected year

YRQTRST and YRQTREND are calculated in an update query which 'feeds' the
table upon which this query is based. This same update query also contains
the parameters for selecting the year in which the course starts and ends
(YRSTART & YREND). The parameters are for YRSTART
<=[Forms]![frmSelectYr]![txtYear] and for YREND

=[Forms]![frmSelectYr]![txtYear] and these seem to be working fine.

The expression in the calculated field is as follows:

CURRENT: IIf([YRQTRST]<=[YRSTART] & 2 And [YRQTREND]>=[YREND] &
2,"RUNNING","NOT RUNNING")

However the query returns typical results such as below (in this case when
year "1" is selected)

YRSTART YREND YRQTRST YRQTREND CURRENT
1 2 12 21
NOT RUNNING
1 2 14 21
RUNNING

Some of the results returned are perfectly correct, but I'm sure there is
something wrong with the expression.

Help.

TIA

Martin Watts

 

[Guest]

I'm being dense or just can't see the woods for the trees.

I have a table which holds details of the start and end dates of training
courses. I have a query which uses a parameter from a form to select
courses running in any chosen year and have an calculated field to determine
if the course is running in quarter 2 of that year but it's returning
incorrect results and I just cannot see what is wrong with the expression.

The fields in the query are

YRQTRST - The year and qtr in which the course starts e.g. 13 for courses
starting in the 3rd quarter of year 1
YRQTREND - As above but the year and quarter in which the course ends
YRSTART - The year in which the course starts
YREND - The year in which the course ends
CURRENT - Should return "Running" or "Not Running" if the course is running
in the second quarter of any selected year

YRQTRST and YRQTREND are calculated in an update query which 'feeds' the
table upon which this query is based. This same update query also contains
the parameters for selecting the year in which the course starts and ends
(YRSTART & YREND). The parameters are for YRSTART
<=[Forms]![frmSelectYr]![txtYear] and for YREND

=[Forms]![frmSelectYr]![txtYear] and these seem to be working fine.

The expression in the calculated field is as follows:

CURRENT: IIf([YRQTRST]<=[YRSTART] & 2 And [YRQTREND]>=[YREND] &
2,"RUNNING","NOT RUNNING")

However the query returns typical results such as below (in this case when
year "1" is selected)

YRSTART YREND YRQTRST YRQTREND CURRENT
1 2 12 21
NOT RUNNING
1 2 14 21
RUNNING

Some of the results returned are perfectly correct, but I'm sure there is
something wrong with the expression.

Help.

TIA

Martin Watts

Martin,

It has to do with Order of Precedence. The Text concatenation operator (&)
has a higher Precedence than the Comparison operators ( = < > <= >= <>).

I'm not sure why you are concatenating a 2 in the formula (for the 2nd
quarter??), but if you substitute values in the formula, it is easier to
understand what is happening.

I'm going to use "C" for "CURRENT","R" for "Running" and "NR" for "Not
Running" (less typing).

The formula is:

C: IIf([YRQTRST]<=[YRSTART] & 2 And [YRQTREND]>=[YREND] & 2,"R","NR")

Substituting the values in your first data example line:

C: IIF( 12 <= 1 & 2 And 21 >= 2 & 2, "R","NR")

with no parentheses, it evaluates like this:
1) C: IIF( 12 <= (1 & 2) And 21 >= (2 & 2), "R","NR")
2) C: IIF( 12 <= 12 And 21 >= 22, "R", "NR")
3) C: IIF( True And False , "R", "NR")
4) C: IIF( False, "R","NR")
the result is "NR"

with parentheses around the comparisons, it evaluates like this:

5) C: IIF( (12 <= 1 ) & 2 And (21 >= 2) & 2, "R","NR")

where 0 = False and -1 = True

6) C: IIF( (0) & 2 And (-1) & 2, "R", "NR")
7) C: IIF( 02 And -12 , "R", "NR")
8) C: IIF( True, "R","NR")
the result is "R"

------
For your second data example (no parentheses) line substituting values:

9) C: IIF( 14 <= 1 & 2 And 21 >= 2 & 2, "R","NR")
10) C: IIF( 14 <= 12 And 21 >= 22, "R","NR")
11) C: IIF( False And False, "R","NR")
the result is "NR"

--
with parentheses around the comparisons, it evaluates like this:

12) C: IIF( (14 <= 1) & 2 And (21 >= 2) & 2, "R","NR")
13) C: IIF( (0) & 2 And (-1) & 2, "R","NR")
14) C: IIF( 02 And -12, "R","NR")
the result is "R"

-----

Why don't you just have a calculated QtrSt column in the query? Then your
formula could be

CURRENT: IIF( [QtrSt] = 2, "RUNNING", "NOT RUNNING")

because your first query uses these parameters

YRSTART <=[Forms]![frmSelectYr]![txtYear]
and for
YREND >=[Forms]![frmSelectYr]![txtYear]

to limit the year you are looking for.


Sorry this was so long - evaluating formulas manually is sometimes the only
way to determine the result is what you think it should be.

HTH

 

[Martin Watts]

in message

[Snip lots of good stuff]

Thanks for that Steve. Although it didn't exactly provide the answer, it
pointed me in the right direction and I have now solved the problem.

Martin Watts

 

[Guest]

[Snip lots of good stuff]

Thanks for that Steve. Although it didn't exactly provide the answer, it
pointed me in the right direction and I have now solved the problem.

Martin Watts

You're welcome. Sorry I didn't give the right answer, but at least you
solved your problem.