Problem with the "between" statement

  • Thread starter Thread starter Cliff McGinty
  • Start date Start date
C

Cliff McGinty

I have found that the "between" statement in Access 2000
does not work correctly when used with a computed date.
The database was converted from Access 97 and the
statement worked in the older database. Is there a fix
for this bug?
 
Here is the code. It is a statement in a query:

Enrolled Current: IIf([entry date] Between ([run date]-6)
And [run date],[Enrolled],0)


run date is a field on a table. The ([run date] - 6) only
picks up the first record that falls on that day.
 
You cannot assign a field value inside a field in a query! In this case,
[Enrolled] seems to be another field on the table to take the value if the
[entry date] is between [run date] and six days before.
What you can do is set the other field and then display the [enrolled] field
when the [entry date] is between the dates specified.
In a report you may use it to add (cummulative) if the field is true.
Enrolled Count:=Sum(Iif([entry date] Between [run date]-6 And [run
date],1,0))
In this case (on a report footer), you should get the total Count between
the dates. If you want to further filter those whose field [Enrolled] is
true, you may add:

Enrolled Count:=Sum(Iif([entry date] Between [run date]-6 And [run
date],Iif([Enrolled],1,0),0)

The second Iff( is in the 'true' place of the first Iif. Thus, it will be 1
only if between the dates and if Enrolled is true.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
Cliff McGinty said:
Here is the code. It is a statement in a query:

Enrolled Current: IIf([entry date] Between ([run date]-6)
And [run date],[Enrolled],0)


run date is a field on a table. The ([run date] - 6) only
picks up the first record that falls on that day.

-----Original Message-----
I have found that the "between" statement in Access 2000
does not work correctly when used with a computed date.
The database was converted from Access 97 and the
statement worked in the older database. Is there a fix
for this bug?
.
Click to expand...
Click to expand...
 
Cliff McGinty said:
Here is the code. It is a statement in a query:

Enrolled Current: IIf([entry date] Between ([run date]-6)
And [run date],[Enrolled],0)


run date is a field on a table. The ([run date] - 6) only
picks up the first record that falls on that day.
Click to expand...

[Run date] and Enrolled must be a field in the current query, it can't just
be a field in some table.
[Run date], enrolled and [entry date] will be evaluated for the current row
in the query, not the first record (row).
The first part of the IIF statement (entry date] Between ([run date]-6) And
[run date]) is evaluated and will either be true or false.

I don't know what you expect, nor do I know what 97 returned however it will
evaluate correctly in 2000 and if a different answer was found in 97 it was
wrong (Even if it was what you wanted)
 
Back
Top