Query Question

[Ted]

Why would the number of records in a table affect the
ability of a query to run? I have the following expression
in a query that runs with a small table (less then 600
records) but returns an error if the table exceeds the 600
record barrier. The error tells me that the expression is
to complex to calculate.

Expression:
Aging Marker: Switch(CDate([Enter Date])-[Net Date]
<31,"Current",CDate([Enter Date])-[Net Date]<61,"Over
30",CDate([Enter Date])-[Net Date]<91,"Over 60",CDate
([Enter Date])-[Net Date]>90,"Over 90")

Any ideas?

Thanks...

 

[John Vinson]

Why would the number of records in a table affect the
ability of a query to run? I have the following expression
in a query that runs with a small table (less then 600
records) but returns an error if the table exceeds the 600
record barrier. The error tells me that the expression is
to complex to calculate.

Expression:
Aging Marker: Switch(CDate([Enter Date])-[Net Date]
<31,"Current",CDate([Enter Date])-[Net Date]<61,"Over
30",CDate([Enter Date])-[Net Date]<91,"Over 60",CDate
([Enter Date])-[Net Date]>90,"Over 90")

Very odd! I don't know why the query would sometimes work and
sometimes not.

What you might want to try is a different approach: create a small
table Range, with three fields - Low, High and Label with rows

0; 31; "Current"
31; 61; "Over 30"
<etc>

You can then include this table in a Query with *no* join line;
instead put a criterion on Low of

= DateDiff("d", [Net Date], [Enter Date])

and on High of

< DateDiff("d", [Net Date], [Enter Date])

 

[Ted]

-----Original Message-----

Why would the number of records in a table affect the
ability of a query to run? I have the following expression
in a query that runs with a small table (less then 600
records) but returns an error if the table exceeds the 600
record barrier. The error tells me that the expression is
to complex to calculate.

Expression:
Aging Marker: Switch(CDate([Enter Date])-[Net Date]
<31,"Current",CDate([Enter Date])-[Net Date]<61,"Over
30",CDate([Enter Date])-[Net Date]<91,"Over 60",CDate
([Enter Date])-[Net Date]>90,"Over 90")

Very odd! I don't know why the query would sometimes work and
sometimes not.

What you might want to try is a different approach: create a small
table Range, with three fields - Low, High and Label with rows

0; 31; "Current"
31; 61; "Over 30"
<etc>

You can then include this table in a Query with *no* join line;
instead put a criterion on Low of

= DateDiff("d", [Net Date], [Enter Date])

and on High of

< DateDiff("d", [Net Date], [Enter Date])



.
John,

I appreciate your taking time to reply and also the
confirmation that it a strange situation, however I don't
know enough about Access to understand how to apply your
answer. I will have to do some research and see if I can
learn what your appraoch means.

Again, thanks for your time.