I have a query that runs normally in a fraction of a second.
One of the (many) fields is
DaysWaiting: Date()-DateValue(FirstBookedDogs([ID]))
Admittedly quite complicated but when run, say returns 10 records in 1/4 of a second.
So all the DaysWaiting numbers are show instantly so I assume calculated extremely fast.
If I put say, <30 in the criteria, it takes about 20 seconds to run, with the correct results etc.
(same if I use a me.filter)
I cannot see why this would be he case, never come across this sort of thing before. Would prefer not to make a table and also would like to understand why.
LenStay: [Outdate]-[Indate]
Nowhere near as complicated but is instant.
I assumed it filtered on the date range (all indexed) first and then on the DaysWaiting etc, I now wonder if it goes through the whole table calculating on every record rather than just the date range, but even then their is only 2800 fairly simple records.
I struggle with subquerys & try to avoid too many nested.
is the whole query
SELECT DISTINCTROW Dogs.Key, Dogs.Names, Dogs.Indate, Dogs.Outdate, Dogs.Comment, Dogs.Code, FirstBookedDogs([ID]) AS AskedDate, IfCancC([Comment]) AS CopyRight, Dogs.Link, Dogs.RegID, Dogs.RegBreed, Dogs.MessageID, Dogs.EmailAddr, [Forms]![DogsWantedRange]![StartDate]-[Indate] AS PreDays, [Outdate]-[Forms]![DogsWantedRange]![EndDate] AS PostDays, Date()-DateValue(FirstBookedDogs([ID])) AS DaysWaiting, IIf(InStr([Comment],"¬")>0,Left([Comment],1),"") AS Expr1, [PreDays]-[PostDays] AS MinDif, [Outdate]-[Indate] AS LenStay
FROM Dogs
WHERE (((Dogs.Indate)<=[Forms]![DogsWantedRange]![EndDate]) AND ((Dogs.Outdate)>=[Forms]![DogsWantedRange]![StartDate]) AND ((Dogs.Code)="C")) OR (((Dogs.Indate)<=[Forms]![DogsWantedRange]![EndDate]) AND ((Dogs.Outdate)>=[Forms]![DogsWantedRange]![StartDate]) AND ((IfCancC([Comment]))>0));
One of the (many) fields is
DaysWaiting: Date()-DateValue(FirstBookedDogs([ID]))
Admittedly quite complicated but when run, say returns 10 records in 1/4 of a second.
So all the DaysWaiting numbers are show instantly so I assume calculated extremely fast.
If I put say, <30 in the criteria, it takes about 20 seconds to run, with the correct results etc.
(same if I use a me.filter)
I cannot see why this would be he case, never come across this sort of thing before. Would prefer not to make a table and also would like to understand why.
LenStay: [Outdate]-[Indate]
Nowhere near as complicated but is instant.
I assumed it filtered on the date range (all indexed) first and then on the DaysWaiting etc, I now wonder if it goes through the whole table calculating on every record rather than just the date range, but even then their is only 2800 fairly simple records.
I struggle with subquerys & try to avoid too many nested.
is the whole query
SELECT DISTINCTROW Dogs.Key, Dogs.Names, Dogs.Indate, Dogs.Outdate, Dogs.Comment, Dogs.Code, FirstBookedDogs([ID]) AS AskedDate, IfCancC([Comment]) AS CopyRight, Dogs.Link, Dogs.RegID, Dogs.RegBreed, Dogs.MessageID, Dogs.EmailAddr, [Forms]![DogsWantedRange]![StartDate]-[Indate] AS PreDays, [Outdate]-[Forms]![DogsWantedRange]![EndDate] AS PostDays, Date()-DateValue(FirstBookedDogs([ID])) AS DaysWaiting, IIf(InStr([Comment],"¬")>0,Left([Comment],1),"") AS Expr1, [PreDays]-[PostDays] AS MinDif, [Outdate]-[Indate] AS LenStay
FROM Dogs
WHERE (((Dogs.Indate)<=[Forms]![DogsWantedRange]![EndDate]) AND ((Dogs.Outdate)>=[Forms]![DogsWantedRange]![StartDate]) AND ((Dogs.Code)="C")) OR (((Dogs.Indate)<=[Forms]![DogsWantedRange]![EndDate]) AND ((Dogs.Outdate)>=[Forms]![DogsWantedRange]![StartDate]) AND ((IfCancC([Comment]))>0));