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?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

print @tr
print @fr

remove ;)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Il giorno Thu 12 Aug 2021 01:27:22p, *Keith Tizzard* ha inviato su comp.databases.ms-access il messaggio news:f1f90ec7-f445-4424-a826-32533bfd549cn@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 ] ...........

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

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?

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

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]));

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

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?

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

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?

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

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]));

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

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?

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

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]));

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

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?

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

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]));

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thursday, August 12, 2021 at 12:48:47 PM UTC-5, Keith Tizzard wrote:

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

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?

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

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]));

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.

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]));

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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?

On Thursday, 12 August 2021 at 19:55:34 UTC+1, Ron Paii wrote:

On Thursday, August 12, 2021 at 12:48:47 PM UTC-5, Keith Tizzard wrote:

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

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?

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

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]));

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.

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]));

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

On Thursday, 12 August 2021 at 19:55:34 UTC+1, Ron Paii wrote:

On Thursday, August 12, 2021 at 12:48:47 PM UTC-5, Keith Tizzard wrote:

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

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?

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

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]));

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.

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]));

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Keith Tizzard has brought this to us :

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?

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.



On Friday, 13 August 2021 at 17:51:02 UTC+1, Ron Weiner wrote:

Keith Tizzard has brought this to us :

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?

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

On Friday, 13 August 2021 at 17:51:02 UTC+1, Ron Weiner wrote:

Keith Tizzard has brought this to us :

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?

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Keith Tizzard explained on 8/14/2021 :

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)

On Saturday, 14 August 2021 at 15:36:49 UTC+1, Ron Weiner wrote:

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

On Friday, 13 August 2021 at 17:51:02 UTC+1, Ron Weiner wrote:

Keith Tizzard has brought this to us :

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?

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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)


On Saturday, 14 August 2021 at 15:36:49 UTC+1, Ron Weiner wrote:

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

On Friday, 13 August 2021 at 17:51:02 UTC+1, Ron Weiner wrote:

Keith Tizzard has brought this to us :

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?

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

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)

On Saturday, 14 August 2021 at 15:36:49 UTC+1, Ron Weiner wrote:

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

On Friday, 13 August 2021 at 17:51:02 UTC+1, Ron Weiner wrote:

Keith Tizzard has brought this to us :

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?

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

On Saturday, 14 August 2021 at 23:09:30 UTC+1, Ammammata wrote:

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
--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.


On Sunday, August 15, 2021 at 8:30:47 PM UTC+10, Keith Tizzard wrote:

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
On Saturday, 14 August 2021 at 23:09:30 UTC+1, Ammammata wrote:

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
--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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 :

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

On Saturday, 14 August 2021 at 23:09:30 UTC+1, Ammammata wrote:

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
--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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


On 8/15/2021 9:27 AM, Ron Weiner wrote:

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 :

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

On Saturday, 14 August 2021 at 23:09:30 UTC+1, Ammammata wrote:

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
--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\ -=- -=- -=- -=- -=- -=- -=- -=-
- -=- ........... [ al lavoro ] ...........

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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 ] ...........

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)