Date Criteria without yearSay the User entered 22/Feb/2000 to 02/Jan/2001how would i enter the criteria in a query to pull out all the records with dates between 22 Feb and 02 Jan regadless of the year?Thanks
I think you need to address how you get the date range from the user. It may well be that you want to interrogate a specific quarter or month, etc. This can be extracted from a date. Using wildcards in dates is unusual as you cannot use Between and And or >= and <=. You could say show me all records for quarter 3 for the past 5 years.
What you could do is store the dates in FULL format i.e. 14/04/2009 but have your query looking only at the first five characters in the date string only i.e 14/04. This way you get proper actual date stamps on the records plus the flexibility to query the date data in any way you wish.
What you could do is store the dates in FULL format i.e. 14/04/2009 but have your query looking only at the first five characters in the date string only i.e 14/04. This way you get proper actual date stamps on the records plus the flexibility to query the date data in any way you wish.
Thanks - I have gone with post 7 method - which works fine if the start and end period is in teh same year. I suppose I can evaluate if the end dat is in the next year and just add a year on to the end variant date.Sorry can you run me through post 8, I am not sure hwats happening here. Thanks everyone.
If a transaction occured on 1 Jan or 2 Jan, then, as I read your logic, it belongs to the previous year. And, that's what Post #8 does -- categorizes what selection period the transaction belongs to.
If you incorporated both Posts # 7 and # 8, and then placed, for example, 2008 as the criteria for your TheYear field, it would return all transactions between 22 Feb 08 thru 2 Jan 09.
Either the goal posts have moved or everyone is missing the boat. Including the OP. I thought that the OP wanted to return data for the same period for every year that it has data for (See #2).
What he was suggesting is that if he entered two dates, lets say for easiness 01/01/2008 & 31/03/2008 he then wanted to return results for ALL years irrespective of what the user typed in. In other words he wanted to look at the first three months activity for every year in the database that has activity in that period.
This was the point of my further post #4 that it would be better if the dates being collected form the user were meaningful periods. Using the above example you query would be
Either the goal posts have moved or everyone is missing the boat. Including the OP. I thought that the OP wanted to return data for the same period for every year that it has data for (See #2).
What he was suggesting is that if he entered two dates, lets say for easiness 01/01/2008 & 31/03/2008 he then wanted to return results for ALL years irrespective of what the user typed in. In other words he wanted to look at the first three months activity for every year in the database that has activity in that period.
This was the point of my further post #4 that it would be better if the dates being collected form the user were meaningful periods. Using the above example you query would be
Yep - thats what I want - and post 7 does indeed do somewhat of this - it substitues in the year of the date to be filtered - by the date criteria, so no matter what the year its correct.The problem still being - a period that crosses the year boundary - ie 21/03/2008 to 13/01/2009 - which post 8 , i think is supossed to fix - but I haven't got to grips with that yet.Much as I would like the date criteria to be meaningful periods - unfortunately I have to let the user put inwhatever they want.
Off the top of my head there is a solution, somewhat sloppy but it should work.
first off create a query that groups by the year of the date in question
In layman terms you would create a function that takes in both dates. You would then refer to the query above as a recordset and itterate throught it until EOF.
Using the day and month of each incoming date you would create a filter string, something like
Looping throught the recordset to create a large where condition. May need to adapt it to cover none matching years but this will get you irregular dates. If you get this part to work first then we can look at the other senerio.
You would end up with something like
([YourDate] >= #01/01/2007# And [YourDate] <= #20/03/2007#) AND ([YourDate] >= #01/01/2008# And [YourDate] <= #20/03/2008#)
Cheers that would work,I have gone withSELECT Membership.MembershipNo, Membership.RenewalDate, DateSerial(Year([renewalDate])+IIf(DatePart("y",[RenewalDate])=DatePart("y",GetEnd()),-1,0),Month(GetStart()),Day(GetStart())) AS VariantStart, DateSerial(Year([RenewalDate])+IIf(DatePart("y",[RenewalDate])>=DatePart("y",GetStart()) And DatePart("y",GetStart())>=DatePart("y",GetEnd()),1,0),Month(getEnd()),Day(GetEnd())) AS VariantEndFROM Membership;Where GetStart() and GetEnd() is the date criteria from the user. And RenewalDate is the date to be within that criteria. I have bascially used what was in post 7 to use the DateSerial to get the VariantStart and VariantEnd which is used as the citeria in the next query.I have adjusted the year down for the variantstart and up for the variantend - ie if the renewaldate (withouth year) is before the GetStart() date and the GetStart() and GetEnd are in differnatYears then adjust down by 1. The opposite for the end date.Seems to work, so far. Quite tricky though.Bob I still don't get "TheYear: Year([YourDate])+IIf(DatePart("y",[YourDate])
You have to abandon the date concept all together to work this problem...
Look at it as Months and days, not dates... Also you must understand the workings of a date... It is with a date near impossible to do this. However, ISO dates (YYYYMMDD) work magic on stuff like this. As you are not intrested in years we simply leave out the YYYY part.
Now there are 2 situations, assuming the end date is always bigger than the start date.
1) 10/Jan/2000 to 25/Dec/2000
The same year this is easy.... in ISO between 0110 and 1225.
i.e. dec 26 (1226) would not be selected but Oct 26 (1026) would be
2) 25/Dec/2000 to 20/Jan/2001
The to-date is in the next year... basicaly needs to reverse 1... this makes it harder... and this is a little more complex... but if you understand the concept its rather obvious!
1)
Add a field to your query
MonthDay: Format(Yourdatefield,"MMDD")
On this field put the criteria:
>= Format([YourFromDate], "MMDD") and <= format([yourToDate],"MMDD")
This is pretty straight forward...
** Note **
Between will also work, but makes the next option less obvious...
2)
Now comes the tricky part... as this one is different and less obvious.
Lucky part 1 can be excluded as there will be no date >= 1225 AND <= 0110
Here you want to select anything that is not selected by above 1. In the same field as for one, make an OR line and fill in this:
>= Format([YourFromDate], "MMDD") OR <= format([yourToDate],"MMDD")
** Note **
Not Between would work, but... is less intuative to code
The actual SQL would then look like:
Code:
WHERE ( Format([YourDateField],"MMDD") >= Format(YourFromDate,"MMDD")
And Format([YourDateField],"MMDD") <= Format(YourToDate ,"MMDD")
) Or
( Format([YourDateField],"MMDD") >= Format(YourFromDate,"MMDD")
Or Format([YourDateField],"MMDD") <= Format(YourToDate ,"MMDD")
)
Or with the betweens, which makes the SQL much more readable but less understandable IMHO:
Code:
WHERE Format([YourDateField],"MMDD") Between Format(YourFromDate,"MMDD") And Format(YourToDate,"MMDD")
OR Format([YourDateField],"MMDD") Not Between Format(YourFromDate,"MMDD") And Format(YourToDate,"MMDD")
I hope you more or less understand how this works... Good luck !
P.S. you can even add code if you want to search maximum 5 years (or so) forwards and backwards...
Cheers mate - I have already gone with the method I posted (which seems to work OK)I have had a look at you method and its seems to return all dates bewteen the criteria and all date not between the criteria. Which means it returns all of them, and idnore the criteria altogether????
Erm oh... Oops... I see I forget the important part...
You need to tell it when to apply what filter....
I.e.
Code:
WHERE ( Format([YourDateField],"MMDD") Between Format(YourFromDate,"MMDD") And Format(YourToDate,"MMDD")
and YourFromDate <= YourToDate
)
OR ( Format([YourDateField],"MMDD") Not Between Format(YourFromDate,"MMDD") And Format(YourToDate,"MMDD")
and YourFromDate > YourTodate
)
