Neil has brought this to us :
> On 8/15/2021 12:03 PM, Ron Weiner wrote:
>> It happens that Neil formulated :
>>> On 8/15/2021 10:54 AM, Neil wrote:
>>>> If I understand it, the year is not a factor, just the day and month. If
>>>> so, the search could be structured:
>>>>
>>>> WHERE Day >= 7 AND Month >= 11 AND Day <= 14 AND Month <= 1*
>>>>
>>>> *This allows for Month <= 3, etc.
>>>>
>>>> Neil
>>>>
>>> OOPS! Wrote this before finishing my coffee!
>>>
>>> The structure should be:
>>> WHERE Day >= 7 AND Month >= 11 OR Day <= 14 AND Month <= 1*
>>>
>>> -- best regards,
>>>
>>> Neil
>>
>> Sorry Neil, I am afraid you are going to need more coffee. The following
>> Sql (I believe) uses the Where Clause you suggested.
>>
>> SELECT RegDate, VIN
>> FROM tblVehicle
>> WHERE (((Month([regdate]))>=11) AND ((Day([regDate]))>=7))
>> OR (((Month([regdate]))<=1) AND ((Day([regDate]))<=14));
>>
>> Returns:
>>
>> RegDate        VIN
>> 11/8/2001    235
>> 12/12/2001    321
>> 12/21/2019    665
>> 1/12/2011    215
>>
>> The expected result is:
>>
>> RegDate        VIN
>> 11/8/2001    235
>> 12/3/2017    332
>> 12/12/2001    321
>> 12/21/2019    665
>> 1/12/2011    215
>>
>> Rdub
>
> Since #332, falling in March, doesn't fit the parameters why is that expected
> to be included in the inquiry?
>
> --
> best regards,
>
> Neil
I live in the land of Feet, Inches, Pounds and Ounces, and we do dates
here in the form of Month / Day / Year. So #332 is Dec 3rd (in my
world) which does fit into the parameters. Sorry for the confusion.

I guess this is just another example of why a significant percentage of
the people living in USA are totally F'n NUTS. I don’t even want to
get into COVID vaccination hesitancy, universal healthcare, or voting
rights. These days it's really getting hard to live here. Very sad!

Rdub

Ron Weiner was thinking very hard :

OK Here is the “Least Crappy” solution I was able to conjure that ought
to work in every case. It involves a temp table that will get cleared
and filled each time you run the query. In my example I created a new
table “tblTempMonthDay” with just one column “Monthday” of a text(4)
type. I also made the “Monthday” column the Primary key

Then I created a the following VBA sub:

Public Sub BuildSql(dteStart As Date, dteEnd As Date)
Dim theDate As Date
CurrentDb.Execute "Delete * from tblTempMonthDay", dbFailOnError
theDate = dteStart
Do While theDate <= dteEnd
CurrentDb.Execute "Insert into tblTempMonthDay (Monthday)
Values('" & Format(theDate, "mmdd") & "')", dbFailOnError
theDate = DateAdd("d", 1, theDate)
Loop
End Sub

Then the query becomes a simple select with an In() clause like this:

SELECT RegDate, VIN
FROM tblVehicle
WHERE Format([regdate],"mmdd") In (Select Monthday from
tblTempMonthDay);

To make it all go, first call the sub with the Start and End Dates,
then run the query.

As I said this is a semi crappy way of doing this, but it should
supply the correct result under all conditions.

Rdub

Ron

I think this is getting close even with your qualifications.

I am fascinated by the amount of interest in what appears to be a deceptively simple problem.

Thank you for you contributions. I have yet to finalise a solution but will do so soon. It was not a test where I already knew the answer.

On Sunday, 15 August 2021 at 19:48:02 UTC+1, Ron Weiner wrote:
> Ron Weiner was thinking very hard :
>
> OK Here is the “Least Crappy” solution I was able to conjure that ought
> to work in every case. It involves a temp table that will get cleared
> and filled each time you run the query. In my example I created a new
> table “tblTempMonthDay” with just one column “Monthday” of a text(4)
> type. I also made the “Monthday” column the Primary key
>
> Then I created a the following VBA sub:
>
> Public Sub BuildSql(dteStart As Date, dteEnd As Date)
> Dim theDate As Date
> CurrentDb.Execute "Delete * from tblTempMonthDay", dbFailOnError
> theDate = dteStart
> Do While theDate <= dteEnd
> CurrentDb.Execute "Insert into tblTempMonthDay (Monthday)
> Values('" & Format(theDate, "mmdd") & "')", dbFailOnError
> theDate = DateAdd("d", 1, theDate)
> Loop
> End Sub
>
> Then the query becomes a simple select with an In() clause like this:
> SELECT RegDate, VIN
> FROM tblVehicle
> WHERE Format([regdate],"mmdd") In (Select Monthday from
> tblTempMonthDay);
>
> To make it all go, first call the sub with the Start and End Dates,
> then run the query.
>
> As I said this is a semi crappy way of doing this, but it should
> supply the correct result under all conditions.
>
> Rdub

On 8/15/2021 1:23 PM, Ron Weiner wrote:
> Neil has brought this to us :
>> On 8/15/2021 12:03 PM, Ron Weiner wrote:
>>> It happens that Neil formulated :
>>>> On 8/15/2021 10:54 AM, Neil wrote:
>>>>> If I understand it, the year is not a factor, just the day and
>>>>> month. If so, the search could be structured:
>>>>>
>>>>> WHERE Day >= 7 AND Month >= 11 AND Day <= 14 AND Month <= 1*
>>>>>
>>>>> *This allows for Month <= 3, etc.
>>>>>
>>>>> Neil
>>>>>
>>>> OOPS! Wrote this before finishing my coffee!
>>>>
>>>> The structure should be:
>>>> WHERE Day >= 7 AND Month >= 11 OR Day <= 14 AND Month <= 1*
>>>>
>>>> -- best regards,
>>>>
>>>> Neil
>>>
>>> Sorry Neil, I am afraid you are going to need more coffee. The
>>> following Sql (I believe) uses the Where Clause you suggested.
>>>
>>> SELECT RegDate, VIN
>>> FROM tblVehicle
>>> WHERE (((Month([regdate]))>=11) AND ((Day([regDate]))>=7))
>>> OR (((Month([regdate]))<=1) AND ((Day([regDate]))<=14));
>>>
>>> Returns:
>>>
>>> RegDate        VIN
>>> 11/8/2001    235
>>> 12/12/2001    321
>>> 12/21/2019    665
>>> 1/12/2011    215
>>>
>>> The expected result is:
>>>
>>> RegDate        VIN
>>> 11/8/2001    235
>>> 12/3/2017    332
>>> 12/12/2001    321
>>> 12/21/2019    665
>>> 1/12/2011    215
>>>
>>> Rdub
>>
>> Since #332, falling in March, doesn't fit the parameters why is that
>> expected to be included in the inquiry?
>>
>> --
>> best regards,
>>
>> Neil
> I live in the land of Feet, Inches, Pounds and Ounces, and we do dates
> here in the form of Month / Day / Year.  So #332 is Dec 3rd (in my
> world) which does fit into the parameters.  Sorry for the confusion.
>
> I guess this is just another example of why a significant percentage of
> the people living in USA are totally F'n NUTS.  I don’t even want to get
> into COVID vaccination hesitancy, universal healthcare, or voting
> rights.  These days it's really getting hard to live here.  Very sad!
>
> Rdub

I, too, live in the land of unique measurements and dates. The giveaway
for me is that there isn't a month greater than 12 in anyone's calendar.

--
best regards,

Neil

Neil pretended :
> On 8/15/2021 1:23 PM, Ron Weiner wrote:
>> Neil has brought this to us :
>>> On 8/15/2021 12:03 PM, Ron Weiner wrote:
>>>> It happens that Neil formulated :
>>>>> On 8/15/2021 10:54 AM, Neil wrote:
>>>>>> If I understand it, the year is not a factor, just the day and month.
>>>>>> If so, the search could be structured:
>>>>>>
>>>>>> WHERE Day >= 7 AND Month >= 11 AND Day <= 14 AND Month <= 1*
>>>>>>
>>>>>> *This allows for Month <= 3, etc.
>>>>>>
>>>>>> Neil
>>>>>>
>>>>> OOPS! Wrote this before finishing my coffee!
>>>>>
>>>>> The structure should be:
>>>>> WHERE Day >= 7 AND Month >= 11 OR Day <= 14 AND Month <= 1*
>>>>>
>>>>> -- best regards,
>>>>>
>>>>> Neil
>>>>
>>>> Sorry Neil, I am afraid you are going to need more coffee. The following
>>>> Sql (I believe) uses the Where Clause you suggested.
>>>>
>>>> SELECT RegDate, VIN
>>>> FROM tblVehicle
>>>> WHERE (((Month([regdate]))>=11) AND ((Day([regDate]))>=7))
>>>> OR (((Month([regdate]))<=1) AND ((Day([regDate]))<=14));
>>>>
>>>> Returns:
>>>>
>>>> RegDate        VIN
>>>> 11/8/2001    235
>>>> 12/12/2001    321
>>>> 12/21/2019    665
>>>> 1/12/2011    215
>>>>
>>>> The expected result is:
>>>>
>>>> RegDate        VIN
>>>> 11/8/2001    235
>>>> 12/3/2017    332
>>>> 12/12/2001    321
>>>> 12/21/2019    665
>>>> 1/12/2011    215
>>>>
>>>> Rdub
>>>
>>> Since #332, falling in March, doesn't fit the parameters why is that
>>> expected to be included in the inquiry?
>>>
>>> -- best regards,
>>>
>>> Neil
>> I live in the land of Feet, Inches, Pounds and Ounces, and we do dates here
>> in the form of Month / Day / Year.  So #332 is Dec 3rd (in my world) which
>> does fit into the parameters.  Sorry for the confusion.
>>
>> I guess this is just another example of why a significant percentage of the
>> people living in USA are totally F'n NUTS.  I don’t even want to get into
>> COVID vaccination hesitancy, universal healthcare, or voting rights.  These
>> days it's really getting hard to live here.  Very sad!
>>
>> Rdub
>
> I, too, live in the land of unique measurements and dates. The giveaway for
> me is that there isn't a month greater than 12 in anyone's calendar.
>
> --
> best regards,
>
> Neil

RE: there isn't a month greater than 12 in anyone's calendar.

Ummm... That that is not exactly 100% true. There are several
calendars that have or periodically add a 13 month to keep it in sync
with planetary bodies. The Ethiopian and Hebrew calendars are
examples.

Rdub

On 8/15/2021 5:13 PM, Ron Weiner wrote:
> Neil pretended :
>>
>> I, too, live in the land of unique measurements and dates. The
>> giveaway for me is that there isn't a month greater than 12 in
>> anyone's calendar.
>>
>> --
>> best regards,
>>
>> Neil
>
> RE: there isn't a month greater than 12 in anyone's calendar.
>
> Ummm... That that is not exactly 100% true.  There are several calendars
> that have or periodically add a 13 month to keep it in sync with
> planetary bodies.  The Ethiopian and Hebrew calendars are examples.
>
> Rdub
>
Thanks for the reminder... fortunately, my SELECT approach would handle
those, too!

--
best regards,

Neil

Neil

I thought I had replied to your post earlier but cannot find it.

You propose the condition

The structure should be:
WHERE Day >= 7 AND Month >= 11 OR Day <= 14 AND Month <= 1

This would not select the first 6 days of December.

For example consider 2 December. 2 is not >=7 so that part fails; and 12 (December) is not <=1 so that part fails

On Sunday, 15 August 2021 at 16:17:59 UTC+1, Neil wrote:
> On 8/15/2021 10:54 AM, Neil wrote:
> > If I understand it, the year is not a factor, just the day and month. If
> > so, the search could be structured:
> >
> > WHERE Day >= 7 AND Month >= 11 AND Day <= 14 AND Month <= 1*
> >
> > *This allows for Month <= 3, etc.
> >
> > Neil
> >
> OOPS! Wrote this before finishing my coffee!
>
> The structure should be:
> WHERE Day >= 7 AND Month >= 11 OR Day <= 14 AND Month <= 1*
>
> --
> best regards,
>
> Neil

On 8/16/2021 2:16 PM, Keith Tizzard wrote:
> Neil
>
> I thought I had replied to your post earlier but cannot find it.
>
> You propose the condition
>
> The structure should be:
> WHERE Day >= 7 AND Month >= 11 OR Day <= 14 AND Month <= 1
>
> This would not select the first 6 days of December.
>
> For example consider 2 December. 2 is not >=7 so that part fails; and 12 (December) is not <=1 so that part fails
>
>
[...]

I was responding to the parameters you laid out on Aug. 15:

"I have a table of vehicle details
VehicleID, RegDate

123, 2/11/2009
234, 4/11/2014
235, 8/11/2001

332, 3/12/2017
321, 12/12/2001
665, 21/12/2019
215, 12/1/2011
762, 17/1/2003
392, 23/1/2005"

and a follow-up post on the same day:

"I want to find those vehicles whose RegDate anniversary (not the
Regdate itself) falls between 7/11/2020 and 14/1/2021"

Your date format is Day, Month, Year, defined by records 665 and 762,
and 392. Therefore, record 332 is December 3rd.

Since December is month 12, it is ">=" November, month 11. If you are
not getting the correct results when running the query, some other
factor is intervening in the process.

--
best regards,

Neil

On 8/16/2021 3:05 PM, Neil wrote:
> On 8/16/2021 2:16 PM, Keith Tizzard wrote:
>> Neil
>>
>> I thought I had replied to your post earlier but cannot find it.
>>
>> You propose the condition
>>
>> The structure should be:
>> WHERE Day >= 7 AND Month >= 11 OR Day <= 14 AND Month <= 1
>>
>> This would not select the first 6 days of December.
>>
>> For example consider 2 December.  2 is not >=7  so that part fails;
>> and 12 (December) is not <=1  so that part fails
>>
>>
> [...]
>
> I was responding to the parameters you laid out on Aug. 15:
>
> "I have a table of vehicle details
> VehicleID, RegDate
>
> 123, 2/11/2009
> 234, 4/11/2014
> 235, 8/11/2001
>
>
> 332, 3/12/2017
> 321, 12/12/2001
> 665, 21/12/2019
> 215, 12/1/2011
> 762, 17/1/2003
> 392, 23/1/2005"
>
> and a follow-up post on the same day:
>
> "I want to find those vehicles whose RegDate anniversary (not the
> Regdate itself) falls between  7/11/2020 and 14/1/2021"
>
> Your date format is Day, Month, Year, defined by records 665 and 762,
> and 392. Therefore, record 332 is December 3rd.
>
> Since December is month 12, it is ">=" November, month 11. If you are
> not getting the correct results when running the query, some other
> factor is intervening in the process.
>
Forget all I wrote above!

The problem with my suggested query is that the Day parameter (>= 7)
will not correctly select any records with days less than 7, regardless
of month. So, it needs to be expanded:

WHERE Day >= 7 AND Month = 11
OR Month > 11
OR Day <= 14 AND Month = 1
OR Month < 1

Again, if you are looking for records in March, for example, the above
would work.

--
best regards,

Neil

The condition needs to work for any StartDate and EndDate. An earlier suggestion worked when they were both in the same year but not in different years.

I just gave one possible example of 7 Nov and 14 January where your condition works. However is does not work when the dates are in the same year.

For example 10 April 2020 to 21 September 2020, your condition becomes

Day >=10 And Month = 4
Or Month >4
Or Day <=21 And Month=9
Or Month<9

Month>4 Or Month<9 covers the whole year !

Tricky isn't it.

On Monday, 16 August 2021 at 20:46:07 UTC+1, Neil wrote:
> On 8/16/2021 3:05 PM, Neil wrote:
> > On 8/16/2021 2:16 PM, Keith Tizzard wrote:
> >> Neil
> >>
> >> I thought I had replied to your post earlier but cannot find it.
> >>
> >> You propose the condition
> >>
> >> The structure should be:
> >> WHERE Day >= 7 AND Month >= 11 OR Day <= 14 AND Month <= 1
> >>
> >> This would not select the first 6 days of December.
> >>
> >> For example consider 2 December. 2 is not >=7 so that part fails;
> >> and 12 (December) is not <=1 so that part fails
> >>
> >>
> > [...]
> >
> > I was responding to the parameters you laid out on Aug. 15:
> >
> > "I have a table of vehicle details
> > VehicleID, RegDate
> >
> > 123, 2/11/2009
> > 234, 4/11/2014
> > 235, 8/11/2001
> >
> >
> > 332, 3/12/2017
> > 321, 12/12/2001
> > 665, 21/12/2019
> > 215, 12/1/2011
> > 762, 17/1/2003
> > 392, 23/1/2005"
> >
> > and a follow-up post on the same day:
> >
> > "I want to find those vehicles whose RegDate anniversary (not the
> > Regdate itself) falls between 7/11/2020 and 14/1/2021"
> >
> > Your date format is Day, Month, Year, defined by records 665 and 762,
> > and 392. Therefore, record 332 is December 3rd.
> >
> > Since December is month 12, it is ">=" November, month 11. If you are
> > not getting the correct results when running the query, some other
> > factor is intervening in the process.
> >
> Forget all I wrote above!
>
> The problem with my suggested query is that the Day parameter (>= 7)
> will not correctly select any records with days less than 7, regardless
> of month. So, it needs to be expanded:
> WHERE Day >= 7 AND Month = 11
> OR Month > 11
> OR Day <= 14 AND Month = 1
> OR Month < 1
>
> Again, if you are looking for records in March, for example, the above
> would work.
>
> --
> best regards,
>
> Neil

On 8/16/2021 5:51 PM, Keith Tizzard wrote:
> The condition needs to work for any StartDate and EndDate. An earlier suggestion worked when they were both in the same year but not in different years.
>
> I just gave one possible example of 7 Nov and 14 January where your condition works. However is does not work when the dates are in the same year.
>
> For example 10 April 2020 to 21 September 2020, your condition becomes
>
> Day >=10 And Month = 4
> Or Month >4
> Or Day <=21 And Month=9
> Or Month<9
>
> Month>4 Or Month<9 covers the whole year !
>
> Tricky isn't it.
>
[...]

Good point. Parameterize the date ranges just to keep the query
relatively simple. For example:

StartDate = Day >= 10 and Month = 4
EndDate = 21 and Month = 9
MonthRange = Month > 4 AND Month < 9

SELECT *
WHERE RecDate = StartDate
OR RecDate = MonthRange
OR RecDate = EndDate

--
best regards,

Neil

On 8/16/2021 8:11 PM, Neil wrote:
> On 8/16/2021 5:51 PM, Keith Tizzard wrote:
>> The condition needs to work for any StartDate and EndDate.  An earlier
>> suggestion worked when they were both in the same year but not in
>> different years.
>>
>> I just gave one possible example of 7 Nov and 14 January where your
>> condition works.  However is does not work when the dates are in the
>> same year.
>>
>> For example 10 April 2020 to 21 September 2020, your condition becomes
>>
>> Day >=10 And Month = 4
>> Or Month >4
>> Or Day <=21 And Month=9
>> Or Month<9
>>
>> Month>4 Or Month<9 covers the whole year !
>>
>> Tricky isn't it.
>>
> [...]
>
> Good point. Parameterize the date ranges just to keep the query
> relatively simple. For example:
>
> StartDate = Day >= 10 and Month = 4
> EndDate = 21 and Month = 9
> MonthRange = Month > 4 AND Month < 9
>
> SELECT *
> WHERE RecDate = StartDate
> OR RecDate = MonthRange
> OR RecDate = EndDate
>
>
Or just modify the SQL to:

WHERE
Day >=10 AND Month = 4
OR Month >4 AND Month <9
OR Day <=21 AND Month=9

For dates across years:

WHERE
Day >=7 AND Month = 11
OR Month >11 AND Month <1
OR Day <=14 AND Month=1

--
best regards,

Neil

Thanks to all for putting up with my "thinking out loud" about this!

I think a modification to the parameterized version may work (obviously,
this is about the concept, not the specific code).

(example within same year)
StartDate = Day >= 10 and Month = 4
EndDate = Day <= 21 and Month = 9

(example across years)
StartDate = Day >= 7 and Month = 11
EndDate = Day <= 14 and Month = 1

IF Month(StartDate) < Month(EndDate) THEN
MonthRange = > Month(StartDate) AND < Month(EndDate)
ELSE
MonthRange = > Month(StartDate) OR < Month(EndDate)
END IF

SELECT *
WHERE RecDate = StartDate
OR RecDate = MonthRange
OR RecDate = EndDate

Going back to bed now...

--
best regards,

Neil

Il giorno Sun 15 Aug 2021 09:35:17p, *Keith Tizzard* ha inviato su
comp.databases.ms-access il messaggio
news:d4c943f9-430b-46cb-aaf2-767cec45dcf8n@googlegroups.com. Vediamo
cosa ha scritto:

>
> I am fascinated by the amount of interest in what appears to be a
> deceptively simple problem.
>
> Thank you for you contributions.

I asked about your problem also on an italian access newsgroup

Subject: Re: filtrare date senza tenere conto dell'anno
Newsgroups: it.comp.appl.access

the "final" query they suggested is like this:

*remove* year from date and convert day/month into a single number, i.e.
month * 100 + day

if start-month > end-month then

select from start-date to 31/dec
*union*
select from 01/jan to end-date

else
normal select between start-date and end-date
end if

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
............ [ al lavoro ] ...........

Have you thought about a User Defined Function to determine the anniversary of the registration date?
Something like:

Function GetAnniversary(varRegDate As Variant)
Dim bytCounter As Byte
Dim datAnniversary As Date
If IsNull(varRegDate) Then Exit Function
If Not IsDate(varRegDate) Then Exit Function
If varRegDate >= Date Then GetAnniversary = varRegDate: Exit Function
datAnniversary = varRegDate
For bytCounter = 1 To 99
datAnniversary = DateAdd("yyyy", 1, datAnniversary)
If datAnniversary >= Date Then GetAnniversary = datAnniversary: Exit Function
Next bytCounter
MsgBox "Subscript out of range."
End Function

Then the where clause in the SQL would be
where GetAnniversary(RegDate) between [startdate] and [enddate]
using startdate and enddate parameters

Mike P
19/8/21

Ron Weiner submitted this idea :
I am sure that by now you are sicka and tired of my solutions to your
non trivial problem. But hang in there with me One More Time. Finally
here is an ALL SQL way of doing this without any VBA code at all.

The only catch is that you need to add one additional permanent table
to your database, a Nums Table. This is a table with only one column,
Num that contains an integer number from 0 to the highest number that
you are ever likely to need. You would make this table just one time,
AND you can make it in a New York second with the following make table
query that makes use of the Union and Cartiesan Product Join.

SELECT (Ones.N+Tens.N+Huns.N+Thous.N) AS Num INTO tblNums
FROM (Select 0 as N From MSysQueries
Union Select 1 From MSysQueries
Union Select 2 From MSysQueries
Union Select 3 From MSysQueries
Union Select 4 From MSysQueries
Union Select 5 From MSysQueries
Union Select 6 From MSysQueries
Union Select 7 From MSysQueries
Union Select 8 From MSysQueries
Union Select 9 From MSysQueries) AS Ones, (Select 0 as N From
MSysQueries
Union Select 10 From MSysQueries
Union Select 20 From MSysQueries
Union Select 30 From MSysQueries
Union Select 40 From MSysQueries
Union Select 50 From MSysQueries
Union Select 60 From MSysQueries
Union Select 70 From MSysQueries
Union Select 80 From MSysQueries
Union Select 90 From MSysQueries) AS Tens, (Select 0 as N From
MSysQueries
Union Select 100 From MSysQueries
Union Select 200 From MSysQueries
Union Select 300 From MSysQueries
Union Select 400 From MSysQueries
Union Select 500 From MSysQueries
Union Select 600 From MSysQueries
Union Select 700 From MSysQueries
Union Select 800 From MSysQueries
Union Select 900 From MSysQueries) AS Huns, (Select 0 as N From
MSysQueries
Union Select 1000 From MSysQueries
Union Select 2000 From MSysQueries
Union Select 3000 From MSysQueries
Union Select 4000 From MSysQueries
Union Select 5000 From MSysQueries
Union Select 6000 From MSysQueries
Union Select 7000 From MSysQueries
Union Select 8000 From MSysQueries
Union Select 9000 From MSysQueries) AS Thous;

Te above sql will create a table "tblNums" and populate it with 10,000
rows 0 to 9999 in a split second.

Once this table has been added to your database then this simple query
will produce your result lickety split every time by just using the
Start and End dates a parameters.

SELECT VIN
FROM tblVehicle
WHERE Format([regdate],"mmdd") In
(
SELECT Format(DateAdd("d",[Num],#[StartDate]#),"mmdd") AS Dates
FROM tblNums
WHERE (((tblNums.Num)<DateDiff("d",#[StartDate]#,#[EndDate]#)+1))
ORDER BY tblNums.Num
);

I haven’t often found use for a nums table in my Access Applications,
but when you need to pull a number of rows out of thin air this is a
great technique. Sorry I did not think about this sooner.

I promise to leave you alone now that I have this out of my system.

Ron W

> Ron Weiner was thinking very hard :
>
> OK Here is the “Least Crappy” solution I was able to conjure that ought to
> work in every case. It involves a temp table that will get cleared and
> filled each time you run the query. In my example I created a new table
> “tblTempMonthDay” with just one column “Monthday” of a text(4) type. I also
> made the “Monthday” column the Primary key
>
> Then I created a the following VBA sub:
>
> Public Sub BuildSql(dteStart As Date, dteEnd As Date)
> Dim theDate As Date
> CurrentDb.Execute "Delete * from tblTempMonthDay", dbFailOnError
> theDate = dteStart
> Do While theDate <= dteEnd
> CurrentDb.Execute "Insert into tblTempMonthDay (Monthday) Values('" &
> Format(theDate, "mmdd") & "')", dbFailOnError
> theDate = DateAdd("d", 1, theDate)
> Loop
> End Sub
>
> Then the query becomes a simple select with an In() clause like this:
>
> SELECT RegDate, VIN
> FROM tblVehicle
> WHERE Format([regdate],"mmdd") In (Select Monthday from tblTempMonthDay);
>
> To make it all go, first call the sub with the Start and End Dates, then run
> the query.
>
> As I said this is a semi crappy way of doing this, but it should supply the
> correct result under all conditions.
>
> Rdub