Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #14340 > unrolled thread

Need some SQL

Back to article view | Back to comp.databases.ms-access

Contents

  Need some SQL Keith Tizzard <internet.shopping@foobox.com> - 2021-08-12 04:27 -0700
    Re: Need some SQL Ammammata <ammammata@tiscalinet.it> - 2021-08-12 13:11 +0000
      Re: Need some SQL Ammammata <ammammata@tiscalinet.it> - 2021-08-12 13:12 +0000
    Re: Need some SQL Ron Paii <ron81pai@gmail.com> - 2021-08-12 08:11 -0700
      Re: Need some SQL Ron Paii <ron81pai@gmail.com> - 2021-08-12 08:13 -0700
        Re: Need some SQL Keith Tizzard <internet.shopping@foobox.com> - 2021-08-12 08:20 -0700
          Re: Need some SQL Ron Paii <ron81pai@gmail.com> - 2021-08-12 10:37 -0700
            Re: Need some SQL Keith Tizzard <internet.shopping@foobox.com> - 2021-08-12 10:48 -0700
              Re: Need some SQL Ron Paii <ron81pai@gmail.com> - 2021-08-12 11:55 -0700
                Re: Need some SQL Keith Tizzard <internet.shopping@foobox.com> - 2021-08-12 14:05 -0700
                  Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-13 11:41 -0400
    Re: Need some SQL Ron Weiner <rw@domain.com> - 2021-08-13 12:50 -0400
      Re: Need some SQL Keith Tizzard <internet.shopping@foobox.com> - 2021-08-14 03:40 -0700
        Re: Need some SQL Ron Weiner <rw@domain.com> - 2021-08-14 10:36 -0400
          Re: Need some SQL Keith Tizzard <internet.shopping@foobox.com> - 2021-08-14 09:17 -0700
            Re: Need some SQL Ron Weiner <rw@domain.com> - 2021-08-14 12:28 -0400
            Re: Need some SQL Ron Weiner <rw@domain.com> - 2021-08-14 12:31 -0400
              Re: Need some SQL Ammammata <ammammata@tiscalinet.it> - 2021-08-14 22:09 +0000
                Re: Need some SQL Keith Tizzard <internet.shopping@foobox.com> - 2021-08-15 03:30 -0700
                  Re: Need some SQL "mal....@gmail.com" <mal.reeve@gmail.com> - 2021-08-15 04:54 -0700
                  Re: Need some SQL Ron Weiner <rw@domain.com> - 2021-08-15 09:27 -0400
                    Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-15 10:54 -0400
                      Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-15 11:17 -0400
                        Re: Need some SQL Ron Weiner <rw@domain.com> - 2021-08-15 12:03 -0400
                          Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-15 13:04 -0400
                            Re: Need some SQL Ron Weiner <rw@domain.com> - 2021-08-15 13:23 -0400
                              Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-15 16:56 -0400
                                Re: Need some SQL Ron Weiner <rw@domain.com> - 2021-08-15 17:13 -0400
                                  Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-16 13:11 -0400
                        Re: Need some SQL Keith Tizzard <internet.shopping@foobox.com> - 2021-08-16 11:16 -0700
                          Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-16 15:05 -0400
                            Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-16 15:46 -0400
                              Re: Need some SQL Keith Tizzard <internet.shopping@foobox.com> - 2021-08-16 14:51 -0700
                                Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-16 20:11 -0400
                                  Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-16 21:54 -0400
                                    Re: Need some SQL Neil <neil@myplaceofwork.com> - 2021-08-17 02:50 -0400
                    Re: Need some SQL Ron Weiner <rw@domain.com> - 2021-08-15 14:47 -0400
                      Re: Need some SQL Keith Tizzard <internet.shopping@foobox.com> - 2021-08-15 12:35 -0700
                        Re: Need some SQL Ammammata <ammammata@tiscalinet.it> - 2021-08-17 08:16 +0000
                      Re: Need some SQL Ron Weiner <rw@domain.com> - 2021-08-19 17:30 -0400
    Re: Need some SQL Mike P <mikewpayne@tiscali.co.uk> - 2021-08-19 02:26 -0700

Page 2 of 3 — ← Prev page 1 [2] 3  Next page →

#14360

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

[toc] | [prev] | [next] | [standalone]

#14361

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

[toc] | [prev] | [next] | [standalone]

#14362

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

[toc] | [prev] | [next] | [standalone]

#14363

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

[toc] | [prev] | [next] | [standalone]

#14364

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

[toc] | [prev] | [next] | [standalone]

#14365

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

[toc] | [prev] | [next] | [standalone]

#14368

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

[toc] | [prev] | [next] | [standalone]

#14369

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

[toc] | [prev] | [next] | [standalone]

#14373

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

[toc] | [prev] | [next] | [standalone]

#14374

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

[toc] | [prev] | [next] | [standalone]

#14375

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

[toc] | [prev] | [next] | [standalone]

#14376

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

[toc] | [prev] | [next] | [standalone]

#14377

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

[toc] | [prev] | [next] | [standalone]

#14378

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

[toc] | [prev] | [next] | [standalone]

#14379

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

[toc] | [prev] | [next] | [standalone]

#14380

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

[toc] | [prev] | [next] | [standalone]

#14366

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

[toc] | [prev] | [next] | [standalone]

#14367

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

[toc] | [prev] | [next] | [standalone]

#14381

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

[toc] | [prev] | [next] | [standalone]

#14383

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

[toc] | [prev] | [next] | [standalone]

Page 2 of 3 — ← Prev page 1 [2] 3  Next page →

Back to top | Article view | comp.databases.ms-access

csiph-web