Hi Bunky,
try this...
format([DateExpr],"yy")
& format(format([DateExpr],"ww"),"00")
.... or, you can also use the DatePart function, but that returns an
integer...
DatePart("ww",[DateExpr]) returns a number 1,2,3...
Format([DateExpr], "ww") returns a string... "1", "2", "3", ...
There are also other date functions
year(DateExpr)
monthDateExpr)
day(year(DateExpr)
you can get the quarter out using "q" as the code for Format or DatePart
to find out more...
1. go to a module window
2. press F2 for the Object Browser
3. for the library, choose VBA
4. click on the DateTime Class
on the right, you will see various functions listed. Click on one --
look at syntax listed on bottom of screen. For more help, press F1
When you figure out which methods you want to use
you may want to just make a general function to do this...
'~~~~~~~~~~~~~~~
Function Get_YW( pDate as date) as string
if IsDate(pDate) then
Get_YW = format(pDate ,"yy") _
& format(DatePart("ww",pDate),"00")
end if
End Function
'~~~~~~~~~~~~~~~
then, in a query...
field --> SomeName: Get_YW([DateField])
where SomeName is not the name of one of your fields
If you are going to make a general function, you may want to consider
setting up a FiscalYear table with an FYID autonumber field and
beginning and ending dates. Instead of returning yyww, your function
could return the FYID...
For calendars based on Jan 1-Dec 31, FY does not need to be stored
because it is always the year... but when you have a different fiscal
year scheme, you should set up a structure to address that functionality...
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Crystal,
One more question, please. After I format to 'yyww'(so I can sort
ascending), the first weeks of each year shows one digit. Example - '041'
instead of '0401'. How do I get the leading zero to appear so my sorting
will work correctly?
So to bother you!
:
Hi Bunky,
....since sliced bread... wow! happy to help
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Bunky wrote:
Crystal,
Best thing since sliced bread! Thanks a bunch!
:
Hi Bunky,
how about including the year as well as the week number in your
criteria...something like this...
field --> format(DateExpression,"yyww")
criteria --> between "0545" and "0644"
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Bunky wrote:
I have a reporting problem. I have data for our entire fiscal year (Nov thru
Oct) that I wish to do calculations on and then report. I have changed via
queries the dates to week interval dates for ease of correct grouping.
However, the data that should be reported in Oct. 05 is being reported Oct.
06. I have tried different sortings and groupings in the report but have not
hit on anything to give me what I want. Any Ideas would be greatfully
appreciated.
Thanks,
