Criteria problem in query

[Paul Doree]

Hi,

I have a field in a query which returns the number of days since a
transaction i.e.

DaysOutstanding: (DateDiff("d",[TransactionDate],Date())

I'm trying to give the user the option to enter a range e.g. 0 to 60 days by
getting access to throw up the parameter boxes i.e.

Between [Enter minimum days outstanding] And [Enter maximum days
outstanding]


I am getting all sorts of numbers of days returned - certainly not the range
I'm asking for.

Anyone know why?

Pual

 

[Allen Browne]

Access may be misunderstanding the data types here.

The most efficient solution will be to put this expression in the Criteria
row under the TransactionDate field:
Between CVDate(Date() - [Enter maximum days outstanding])
And CVDate(Date() - [Enter minimum days outstanding])

Then declare the parameters, by choosing Parameters on the Query menu, and
entering two lines:
[Enter minimum days outstanding] Long
[Enter maximum days outstanding] Long

Now:
- the data types are unambiguous,
- the 2 calculations (to get the 2 dates) are performed once only for the
entire query,
- Access can use the index on the TransactionDate field to select the right
records instantly.
You can add the calculated field to the query as well if you want. Access
will execute the WHERE first, and so the calculated field will only be done
on the results, not on all records, so it's still efficient. Wrapping the
calculation in CLng() may help, esp. if you see it left-aligning (a clue
that Access is treating the results as text, not number.)

For more info on problems with data types, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html