Unstable result

[Jan T.]

Using MS Access 2000
I have made a query that results in 37 records.
However, if I open the query in Design mode and change
the sort order from descending to ascending, it then would
return 111 records when I open the query?

If I then select a column and sort again, only 37 records are
showing again. I can then save it and I will return 37 records
again.

Now I wonder if I can trust this query?
Why is my query so unstable? Here is how I build it:

SELECT tblPeriods.PeriodID, tblPeriods.From, tblPeriods.To,
tblPeriods.NumOfDays,
[tblPeriods].[From]+[tblNum].[intNumbers] AS ActualDates
FROM tblPeriods, tblNum, tblHolidays
WHERE ((([tblPeriods].[From]+[tblNum].[intNumbers])<=[tblPeriods].[From]
+[tblPeriods].[NumOfDays] And ([tblPeriods].[From]+[tblNum].[intNumbers])
Not In (SELECT [tblHolidays].[Holiday] FROM [tblHolidays])))
ORDER BY tblPeriods.PeriodID;

Table_1:
tblPeriods
PeriodID
From
To
NumOfDays

Table_2:
tblNum
intNumbers --> from 0 to 366

Table_3:
tblHolidays
Holiday

Any help or tip is very much appriciated.

Regards Jan

 

[KARL DEWEY]

UNTESTED - Try this --
SELECT tblPeriods.PeriodID, tblPeriods.From, tblPeriods.To,
tblPeriods.NumOfDays,
[tblPeriods].[From]+[tblNum].[intNumbers] AS ActualDates
FROM tblPeriods, tblNum, tblHolidays
WHERE ((([tblPeriods].[From]+[tblNum].[intNumbers]) Between
[tblPeriods].[From]And [tblPeriods].[To]) And
(([tblPeriods].[From]+[tblNum].[intNumbers])
Not In (SELECT [tblHolidays].[Holiday] FROM [tblHolidays])))
ORDER BY tblPeriods.PeriodID;

 

[Jan T.]

Thanks a lot. I will try this.
Thank you very, very much for your suggestion.

Regards Jan.

UNTESTED - Try this --
SELECT tblPeriods.PeriodID, tblPeriods.From, tblPeriods.To,
tblPeriods.NumOfDays,
[tblPeriods].[From]+[tblNum].[intNumbers] AS ActualDates
FROM tblPeriods, tblNum, tblHolidays
WHERE ((([tblPeriods].[From]+[tblNum].[intNumbers]) Between
[tblPeriods].[From]And [tblPeriods].[To]) And
(([tblPeriods].[From]+[tblNum].[intNumbers])
Not In (SELECT [tblHolidays].[Holiday] FROM [tblHolidays])))
ORDER BY tblPeriods.PeriodID;

Using MS Access 2000
I have made a query that results in 37 records.
However, if I open the query in Design mode and change
the sort order from descending to ascending, it then would
return 111 records when I open the query?

If I then select a column and sort again, only 37 records are
showing again. I can then save it and I will return 37 records
again.

Now I wonder if I can trust this query?
Why is my query so unstable? Here is how I build it:

SELECT tblPeriods.PeriodID, tblPeriods.From, tblPeriods.To,
tblPeriods.NumOfDays,
[tblPeriods].[From]+[tblNum].[intNumbers] AS ActualDates
FROM tblPeriods, tblNum, tblHolidays
WHERE ((([tblPeriods].[From]+[tblNum].[intNumbers])<=[tblPeriods].[From]
+[tblPeriods].[NumOfDays] And ([tblPeriods].[From]+[tblNum].[intNumbers])
Not In (SELECT [tblHolidays].[Holiday] FROM [tblHolidays])))
ORDER BY tblPeriods.PeriodID;

Table_1:
tblPeriods
PeriodID
From
To
NumOfDays

Table_2:
tblNum
intNumbers --> from 0 to 366

Table_3:
tblHolidays
Holiday

Any help or tip is very much appriciated.

Regards Jan