In my criteria for my query I need it to return results between 6th June 2007 and 23rd June 2008 (inclusive). The catch is that after 23rd June 2008, the criteria should return results between 6th June 2008 and 23rd June 2009!
Is this possible or will I have to edit the criteria manually? The field is called SlaughterDate its a date/time field. Thanks in advance
Well you have two contant dates that require different years each year....
Instead of prefixing the 1 in this part
IIf(Date()<#2008-06-23#,0,1),
You would have to make that a formula...
Year(date) will fetch only the year from said date... So... try that and see where it gets you.
Edit: Or have a search at "Fiscal year" to find more samples.
If you're building your query SQL from a function, you can call this to build your 'between' statement.
If you're working straight from a query, you'll have to modify this function, but the logic for determining start and end dates will remain valid.
Code:
Function DateSpread(pdate As Date) As String
'coded by: raskew
're: http://www.access-programmers.co.uk/forums/showthread.php?t=152207
'to call:
'1) ? DateSpread(#6/24/08#)
'2) ? DateSpread(#6/22/08#)
'returns
'1) Between #6/6/2008# AND #6/23/2009#
'2) Between #6/6/2007# AND #6/23/2008#
Dim booDay As Boolean
Dim dteFrom As Date
Dim dteTo As Date
'returns true (-1) if pDate > 6/23/year(pdate), false (0) if d/m/year(pdate)<= 6/23/(year(pdate)
booDay = DatePart("y", DateValue("6/23/" & year(pdate))) - DatePart("y", pdate) > 0
dteFrom = DateSerial(year(pdate) + booDay, 6, 6)
dteTo = DateSerial(year(dteFrom) + 1, 6, 23)
DateSpread = "Between #" & dteFrom & "# AND #" & dteTo & "#"
End Function
wont something like this work
Between (iif(date()>dateserial(year(date()),06,23),dateserial(year(date()),06,06),dateserial(year(date())-1,06,06)) and (iif(date()>dateserial(year(date()),06,23),dateserial(year(date())+1,06,23),dateserial(year(date()),06,23))
Actually the "preferred international format" would be ISO, which is YYYY-MM-DD, not dd-mm. Your YYYY-DD-MM doesnt work for me at all
Notice tho that access WILL revert this ISO format back to the america format of MM-DD-YYYY despite your entering the YYYY-MM-DD format.
There are more roads to rome than one can count.
The function will work, the dateserial will work, the Iif will work or the Abs will work ... and a few more ways.
I usually prefer not to give solutions tho, but rather point in the right direction and have the OP come up with his own way of doing things.
1) Better for them to learn
2) Easier for them to support, cause they KNOW what is going on.
Has always worked fine for me , and did on the check I made before posting.
I can type #01/04/08# into the criteria row of the design grid and it will find the 1st of April, not the 4th of January.
Brian
PS realised that in SQL it is also US format, I think but I usually start in the design grid even if editing the SQL later so forgot that one.
Your correction to the originally format posted has been made! ... I truly do know the proper sequence, just typo'd it!! ... Thank you!!
...
ISO: International Standards Organization, thus the reason for the casual conversation of "prefered international format", meaning I, and many, prefer the International Standards Organization format of yyyy-mm-dd ..
...
With respect to what Access does when we type in literals ...
- When in VBA, you can input ISO (#2008-7-3#), or other forms (#3 July 2008#, #July 3 2008#), and VBA will convert the literal to US format (#7/3/2008#).
- When in the query designer, you can input ISO (#2008-7-3#), or other forms (#3 July 2008#, #July 3 2008#), and Access will covert it to the format indicated by your regional settings of the local PC.
- When designing the query in SQL view, you can type in the date literal in ISO format sticks, however, when you switch between "Design" view and "SQL" view, Access converts the date literal to US format, but not every time ... I *think* the conversion only occurs if the query gets "optimized", but the coversion does NOT seem consistent. With that said, I would bet that if we viewed the ShowPlan info, the date literal would be US format.
Ultimately it is apparent that the date literals feel quite comfortable with US format! It is my guess, as eluded to above, that when Access prepares an SQL statement for JET, the literal is in US format.
As and FYI, dates are actually stored in JET and used in VBA as a number, an 8 byte floating point number to be exact (equivalent to the Number/Double data type in Access, and the Double data type in VBA). So when we SEE dates, we are actually looking at a value that has been formated by the interface! The units of the value are Days, and the base date (date serial of 0) is 12/30/1899 12:00 AM (US format ). Even with the common understanding that the unit of the date serial is Days, the actually value of the number is evaluated by Access/VBA/JET as if its TWO distinct values SPLIT by a decimal point, the left of the decimal point is the number of days passed the base date, and the right of the decimal point is the portion of a day passed midnight, which means the TIME portion of the date serial can never be negative. The subtlety of the separation can only be see with date values that are LESS than the base date.
Take a look at the following, and take special note as to what is happening with the SerialNumber compared to the NaturalFlow
Code:
TimeStamp SerialNumber NaturalFlow
12/28/1899 12:00 am -2.000 -2.000
12/28/1899 04:00 am -2.167 -1.833
12/28/1899 08:00 am -2.333 -1.667
12/28/1899 12:00 pm -2.500 -1.500
12/28/1899 04:00 pm -2.667 -1.333
12/28/1899 08:00 pm -2.833 -1.167
12/29/1899 12:00 am -1.000 -1.000
12/29/1899 04:00 am -1.167 -0.833
12/29/1899 08:00 am -1.333 -0.667
12/29/1899 12:00 pm -1.500 -0.500
12/29/1899 04:00 pm -1.667 -0.333
12/29/1899 08:00 pm -1.833 -0.167
12/30/1899 12:00 am 0.000 0.000
12/30/1899 04:00 am 0.167 0.167
12/30/1899 08:00 am 0.333 0.333
12/30/1899 12:00 pm 0.500 0.500
12/30/1899 04:00 pm 0.667 0.667
12/30/1899 08:00 pm 0.833 0.833
12/31/1899 12:00 am 1.000 1.000
Interesting how as the timestamp approaches the base date, the time portion is always increasing from x.0 ... definately not the natural flow of a number line! {Note SQL Server, follows the "Natural Flow", but the base date is 1/1/1900} Also, notice how there is no serial values between -0.99 and 0) ... Alrighty then ... I've definately overestimated the curosity on the given subject!! ... sorry! ...
...
With respect to your response style, that's great! ... I generally don't like to just "sling code" (but have been guilty of it on occasion). I personally like to teach WITH the solution/suggestion I post, so the OP has something to chew on as the How's and Why's are explained. But each thread has situation of its own.
...
Again, thanks for posting the correction on my incorrect representation of the ISO format! ...
Has always worked fine for me , and did on the check I made before posting.
I can type #01/04/08# into the criteria row of the design grid and it will find the 1st of April, not the 4th of January.
Brian
PS realised that in SQL it is also US format, I think but I usually start in the design grid even if editing the SQL later so forgot that one.
As and FYI, dates are actually stored in JET and used in VBA as a number, an 8 byte floating point number to be exact (equivalent to the Number/Double data type in Access, and the Double data type in VBA). So when we SEE dates, we are actually looking at a value that has been formated by the interface! The units of the value are Days, and the base date (date serial of 0) is 12/30/1899 12:00 AM (US format ).
Quite intresting to know, tho less usefull becuase we dont use many dates prior to 1900... but still intresting to know.
Another intresting fact is that tho many things in Access are 0 based, the dates are not. 1 = 1-1-1900, not 0.
I knew this allready but intresting to know for other people too as it can have some real consequences in some cases.
Well I wouldnt know this Typo... I can see how it could be a typo. Anyway I appreciate the time you took to put all this onto the forum, eventho a lot of this was allready known to me. It will be usefull for others....
Another problem solved and a new forum friend met!
Keep up the good work Adrenaline
>> I know this and (ab)use this fact quite oftenly when working with dates.... <<
I kinda figured you knew dates were doubles ... I figured Brian knew this fact too. The depth of the posts I have read from each of you lead to that assumption... but as you indicate, a good addition to the thread.
>> Another intresting fact is that tho many things in Access are 0 based, the dates are not. 1 = 1-1-1900, not 0. <<
In VBA/Access, 0 = 12/30/1899 so 1 = 12/31/1899, not 1/1/1900 ... in SQL Server 0 = 1/1/1900. I find it interesting that the difference between the base dates in two MS Environments is two days different! ... ODBC handles this nicely when working with linked tables, but I ran into situations where my results were 2 days off because of this fact!! ...
>> Another problem solved and a new forum friend met! <<
Most definately!! ... I came to "visit" a couple times in the past, but decided to join the site just recently. Boblarson, the new AWF member LPurvis, and I are friends from www.utteraccess.com, so its good to "expand the horizon" so to speak! ...
Looking forward to coinciding in the threads with you in the future.
I read that the safest way is to pass it yyyy-mm-dd so I also tried this but still no results:
