Need some SQL

[Keith Tizzard]

I have a table of vehicle registration dates with the relevant fields:
VehicleID, RegDate

Work needs to be carried out on a vehicle on the anniversary of its RegDate.

I need to find all the vehicles whose anniversary falls between two given dates. The date range may start in one year (say in November) and end in the next (say in February), or it may be solely in a single year.

I may end up with:
223, 12/11/2008
334, 13/11/2013
345, 13/11/2020
444, 14/11/2017
...
393, 05/01/2009
998, 07/01/2003

Is there a simple SQL to achieve this?

[Ammammata]

Il giorno Thu 12 Aug 2021 01:27:22p, *Keith Tizzard* ha inviato su
comp.databases.ms-access il messaggio
news:f1f90ec7-f445-4424...@googlegroups.com. Vediamo
cosa ha scritto:

> I need to find all the vehicles whose anniversary falls between two
> given dates. The date range may start in one year (say in November)
> and end in the next (say in February), or it may be solely in a single
> year.
>
> I may end up with:
> 223, 12/11/2008
> 334, 13/11/2013
> 345, 13/11/2020
> 444, 14/11/2017
> ...
> 393, 05/01/2009
> 998, 07/01/2003
>
> Is there a simple SQL to achieve this?
>

sorry, in just 10 minutes I couldn't do better than this, that's NOT
simple as you requested:

declare @fd as int -- from day
declare @fm as int -- from month

declare @td as int -- to day
declare @tm as int -- to month

declare @fr as int -- from range
declare @tr as int -- to range

set @fd = 15 -- i.e. from 15/10
set @fm = 10

set @td = 24 -- i.e. to 24/2
set @tm = 2

set @fr = @fm * 50 + @fd -- convert month and day into a single number
set @tr =
case -- case 'to' is lower then 'from' -> falls in next year
when @tm * 50 + @td < @fr then @tm * 50 + @td + 600
else @tm * 50 + @td
end

print @tr
print @fr

select DocNum, DocDate -- using ORDR SAP table ;)
from ORDR

where
case
when @tm * 50 + @td < @fr
and DATEPART(month, DocDate) * 50 + DATEPART(day, DocDate) < @fr
then DATEPART(month, DocDate) * 50 + DATEPART(day, DocDate) + 600
else DATEPART(month, DocDate) * 50 + DATEPART(day, DocDate)
End >= @fr
and
case
when @tm * 50 + @td < @fr
and DATEPART(month, DocDate) * 50 + DATEPART(day, DocDate) < @fr
then DATEPART(month, DocDate) * 50 + DATEPART(day, DocDate) + 600
else DATEPART(month, DocDate) * 50 + DATEPART(day, DocDate)
end <= @tr

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

[Ammammata]

Il giorno Thu 12 Aug 2021 03:11:07p, *Ammammata* ha inviato su
comp.databases.ms-access il messaggio
news:XnsAD849A7C6C706am...@127.0.0.1. Vediamo cosa ha
scritto:

> print @tr
> print @fr
>

remove ;)

[Ron Paii]

PARAMETERS DateStart DateTime, DateEnd DateTime;
SELECT tblReg.ID, tblReg.RegDate
FROM tblReg
WHERE tblReg.RegDate>=[DateStart] And tblReg.RegDate<DateAdd("d",1,[DateEnd]);

[Ron Paii]

Add DateValue to end date

PARAMETERS DateStart DateTime, DateEnd DateTime;
SELECT tblReg.ID, tblReg.RegDate
FROM tblReg

WHERE tblReg.RegDate>=[DateStart] And tblReg.RegDate<DateAdd("d",1,DateValue([DateEnd]));

[Keith Tizzard]

Ron, that selects Reg dates between start and end. I need the anniversary dates to be in the date range. The reg dates will be a number of years earlier

[Ron Paii]

DateStart and DateEnd are date parameters. You would supply say #12/11/2008# and #13/11/2017# when the query is run. Using typed parameters allows for almost any date format and error if no date is entered. If you want a fixed range, delete the parameters and enter the dates.

[Keith Tizzard]

I have no problem with DateStart and DateEnd nor with parameters. You are selecting on the RegDate being within that range. I need the Anniversary of the RegDate

In your example I would want to be able to select a RegDate of 12/12/2001 because its anniversary falls on 12/12/2008 and again on 12/12/2009 etc

On Thursday, 12 August 2021 at 18:37:12 UTC+1, Ron Paii wrote:
> On Thursday, August 12, 2021 at 10:20:06 AM UTC-5, Keith Tizzard wrote:
> > Ron, that selects Reg dates between start and end. I need the anniversary dates to be in the date range. The reg dates will be a number of years earlier
> > On Thursday, 12 August 2021 at 16:13:32 UTC+1, Ron Paii wrote:
> > > On Thursday, August 12, 2021 at 10:11:37 AM UTC-5, Ron Paii wrote:
> > > > On Thursday, August 12, 2021 at 6:27:25 AM UTC-5, Keith Tizzard wrote:
> > > > > I have a table of vehicle registration dates with the relevant fields:
> > > > > VehicleID, RegDate
> > > > >
> > > > > Work needs to be carried out on a vehicle on the anniversary of its RegDate.
> > > > >
> > > > > I need to find all the vehicles whose anniversary falls between two given dates. The date range may start in one year (say in November) and end in the next (say in February), or it may be solely in a single year.

In your example I would want to include a RegDate of

[Ron Paii]

Now I understand, you are only looking a the month and day of the date range.
Try the following, Excluded null dates to prevent error in Month and Day functions.

PARAMETERS DateStart DateTime, DateEnd DateTime;
SELECT tblReg.ID, tblReg.RegDate
FROM tblReg

WHERE tblReg.RegDateIs Not Null AND DateSerial(Year(Now()),Month([RegDate]),Day([RegDate])) Between DateSerial(Year(Now()),Month([DateStart]),Day([DateStart])) And DateSerial(Year(Now()),Month([DateEnd]),Day([DateEnd]));

[Keith Tizzard]

Ron

Thanks for your continued interest in this.

That's interesting and it works if the DateStart and DateEnd are in the same year. However let's take DateStart as 1/11/20 and DateEnd as 31/1/21. I'm looking for registration anniversaries in November, December and January.

Your condition becomes Between 1/11/21 and 31/1/21. or simply between 31/1/21 and 1/11/21. This picks up the very months I wouldn't want.

Now do you see the problem?

[Neil]

On 8/12/2021 5:05 PM, Keith Tizzard wrote:
> Ron
>
> Thanks for your continued interest in this.
>
> That's interesting and it works if the DateStart and DateEnd are in the same year. However let's take DateStart as 1/11/20 and DateEnd as 31/1/21. I'm looking for registration anniversaries in November, December and January.
>
> Your condition becomes Between 1/11/21 and 31/1/21. or simply between 31/1/21 and 1/11/21. This picks up the very months I wouldn't want.
>
> Now do you see the problem?
>

I see more than one problem trying to be solved in a simple manner! ;-)

However, Ron's code may be easily modifiable to address the year issue.
His code uses Year(Now()) in both parts of the query, which results in
all of the selections to fall in the same year.

Simply put in the year discretely, as in
FROM (Year(2020)) TO (Year(2021))

Of course, this can't be done if the dates aren't entered manually. In
that case, you'd need multiple queries, and select the one that fits
your needs.

Neil

--
best regards,

Neil

[Ron Weiner]

Keith Tizzard has brought this to us :

I am a little late to this party, and I probably don't understand what
you are trying to do, but why not use the "Between" operator.

Select Whatever From SomeTable
Where SomeColumn between [StartDate] and [EndDate]

Access insists that # delimits dates.

So if I were building a string with the sql statement it would go
something like this:

strSql = "Select Whatever From SomeTable Where SomeColumn between #"
strSql = strSql & [StartDate] & "# and #" & [EndDate] & "#"

Hope this helps

Rdub

[Keith Tizzard]

Ron, I agree that I need

Select Whatever From SomeTable
Where SomeColumn between [StartDate] and [EndDate]

The problem in the SomeColumn. I have the registration dates of vehicles. I need to find those vehicles whose Anniversary of their Registration dates falls within the date range - not the Registration dates themselves.

[Ron Weiner]

Keith Tizzard has brought this to us :

> Ron, I agree that I need
> Select Whatever From SomeTable
> Where SomeColumn between [StartDate] and [EndDate]
>
> The problem in the SomeColumn. I have the registration dates of vehicles. I
> need to find those vehicles whose Anniversary of their Registration dates
> falls within the date range - not the Registration dates themselves.
>

OK, think I got it... Need to do a little date math to move the
registration dates into the current year. I believe this has a chance
of working. I am assuming a column RegDate in your SomeTable:

Select Stuff From SomeTable
Where
CDate(DateAdd("yyyy",DateDiff("yyyy",[RegDate],Date()),[RegDate]))
Between #[StartDate]# And #[EndDate]#


What I am (attempting) doing here is to calculate the number of years
from the registration date to today, and then Add that number of years
into the registration date before comparing in the Between statement.

This is untested! Let me know if I am getting closer.

Rdub

[Keith Tizzard]

Ron, thanks for your interest.

I have had a similar exchange with your namesake earlier in this discussion

The problem occurs when the StartDate and EndDate are in different years. (we can assume for simplicity that these would be consecutive years)

[Ron Weiner]

Keith Tizzard explained on 8/14/2021 :

[Ron Weiner]

Keith Tizzard explained on 8/14/2021 :

OOOps got a little to wonky on the Send button.

AnywayI am probably not understanding your requirement. Can you supply
a small sampling of data and your criteria for selecting records, that
innustrate the issue you are having.

Rdub

[Ammammata]

Il giorno Sat 14 Aug 2021 06:31:52p, *Ron Weiner* ha inviato su
comp.databases.ms-access il messaggio news:sf8r5q$1ul$1...@dont-email.me.
Vediamo cosa ha scritto:

> AnywayI am probably not understanding your requirement.

he has been quite clear in his request

[Keith Tizzard]

Ron

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

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

This should produce vehicles
235
332
321
665
215

Within this the anniversary of 235 is 8/11/2020
and the anniversary of 215 is 12/1/2021

Hope this explains the issue

[mal....@gmail.com]

I'm not on a machine with Access, so can't test any SQL, but I think an answer might come from using "Mod 12" in your date calculations.
For instance - to grab the month (eg 11 for NOV.....add 3 (if the date range was 3 months (DateDiff), and then "mod 12" it.
That will wrap around the year whenever it adds to more than 12
11 + 3 = 14.
14 mod 12 = 2...Feb.

Then put that back together with the Day/Year etc. to test.

I think you would need to calculate a 'RegDate this Year', and perhaps even 'RegDate next Year' to pick up on those that 'wrap around'.

Or perhaps you can just take the RegDate, grab the MONTH of it (Assuming you don't want to drill down to the day), and test if falls between the given Months you want to test for.

Sorry if that is confusing - I will try to test it once I'm back in the office.

Mal.

[Ron Weiner]

OK... Got it now, and see the problem. I created a table tblVehicle
and put your example records in it. I have a solutions which is really
not all that good. But it gives the correct result set, so any port in
a storm.

Sql for when the date spans 2 years

SELECT tblVehicle.RegDate, tblVehicle.VIN
FROM tblVehicle
WHERE
CDate(DateAdd("yyyy",DateDiff("yyyy",[RegDate],Date())-1,[RegDate]))
Between #11/7/2020# And #12/31/2020#
OR CDate(DateAdd("yyyy",DateDiff("yyyy",[RegDate],Date()),[RegDate]))
Between #1/1/2021# And #1/14/2021#;

If the date span was all in the same year the Sql would be:

SELECT tblVehicle.RegDate, tblVehicle.VIN
FROM tblVehicle
WHERE

CDate(DateAdd("yyyy",DateDiff("yyyy",[RegDate],Date()),[RegDate]))

Between #1/1/2021# And #1/14/2021#;

If this query happened behind a form than you could prompt for the
dates, see if the start date and end date were in the same year and
select the sql statement accordingly. It’s a little messy I know but
it seems to work. Another problem with this is if the date span was
was this year and next year. Yea this solution sucks. I’ll think on
it more and see if I can come up with something better later. Sorry to
have wasted your time.

Rdub


Keith Tizzard brought next idea :

[Neil]

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

--
best regards,

Neil

[Neil]

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

[Ron Weiner]

It happens that Neil formulated :

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

[Neil]

Since #332, falling in March, doesn't fit the parameters why is that
expected to be included in the inquiry?

--
best regards,

Neil

[Ron Weiner]

Neil has brought this to us :

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]

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

[Keith Tizzard]

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.

[Neil]

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

[Ron Weiner]

Neil pretended :

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

[Neil]

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

[Keith Tizzard]

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

[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

[Neil]

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

[Keith Tizzard]

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.

[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

[Neil]

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

[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

[Ammammata]

Il giorno Sun 15 Aug 2021 09:35:17p, *Keith Tizzard* ha inviato su
comp.databases.ms-access il messaggio
news:d4c943f9-430b-46cb...@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

[Mike P]

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]

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