What you're describing there appears to be completely different than the
questions you were asking me!
You appeared to be asking how to correctly calculate the week number for
your locale, to which the answer is look at the optional 3rd and 4th
parameters to the DatePart function. For example, look at the following:
?DatePart("ww", #2005-01-01#, vbSunday, vbFirstFourDays)
52
?DatePart("ww", #2005-01-01#, vbSunday, vbFirstFullWeek)
52
?DatePart("ww", #2005-01-01#, vbSunday , vbFirstJan1)
1
?DatePart("ww", #2005-01-01#, vbSunday, vbUseSystem)
1
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Douglas
You are correct you get the name of any "Dates" that have a record. But
if there is no records for the first day of the week then you still
have not found the date of first week. The whole week may have no
appointments (Dates) at all. The only solution that works (so far) is
this code that I modified from a function created by TimK at Utter
Access VIP
Public Sub GetDate()
Dim intWeekNumber As Integer, intFullYear As Integer
Dim DayOne As Date, x As Integer
Dim i As Integer
intFullYear = YearNow ' YearNow is a calculated field on my form that
only shows year number
For i = 1 To 7
If DatePart("ww", DateSerial(intFullYear, 1, i), , vbFirstFullWeek) =
1 Then
x = i
Exit For
End If
Next i
DayOne = DateAdd("d", (WeekNumber - 1) * 7, DateSerial(intFullYear, 1,
1)) - x
WeekStartDate = DayOne + 1 '+ 1 changes first day of week from
Sunday to Monday
WeekEndDate = DayOne + 7
End Sub
WeekStartDate and WeehEndDate are two new Text boxes on my form. The
query now draws its criteria from the form as "Between WeekStarDate and
WeekEndDate" I can now do what I like with the dates!!!
message What happens if you use DayName: Format([Dates], "mmmm")?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Thanks Douglas
Try this: open a query (not linked to any table) in design view. In
the fields heading of the first 4 columns type each of the following:
Dates: Now()
Year: DatePart("yyyy",[Dates])
DayNumber: (DatePart("w",[Dates]))
DayName: WeekdayName([DayNumber])
Set your system date to Jan 1st 2005 and switch to datasheet veiw.
Yes, my system calender shows Saturday as the 1st of jan but what
does your query show?
I do hope that this is fixable
Thanks again Douglas
message If your expression is reporting one week too many, you'd need Week
Number: DatePart("ww",[AppointmentDate])-1 to correct it, not Week
Number: DatePart("ww",[AppointmentDate]-1)
However, as I mentioned already, DatePart gives you two optional
parameter to indicate what the first week of the year should be, and
what the first day of the week should be.
http://msdn.microsoft.com/archive/en-us/office97/html/output/F1/D6/S5B208.asp
I would, however, question "January the 1st shows as Sunday when it
is actualy Saturday". My calendar shows January 1st, 2006 as being a
Sunday.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I have a weekly diary report. Which shows the 7 days of the week.
The user can, through the use of a popup form, select the week
number to print a diary for. This lists all appointments and lays
them out like a regular diary. The first problem arose when I
realised that my week numbers were one week ahead of any normal
over the counter printed diary. As explained this is caused by the
Regional settings using the first day of the year as week one.
Which is incorrect by European standards. We use the first Sunday
as the start of the week. I could overcome this by deducting "1"
from the week number but that throws out next year as Sunday is the
start of the year 2006 so deducting "1" from it will bugger up next
years calendar. Also a really weird thing is if you put the
following in a new fields in a query Access:
Week Number: DatePart("ww",[AppointmentDate]) and another field
DayOfWeek: (DatePart("w",[AppointmentDate]))
In the first you get the wrong week number as explained but the
second also produces a day name that is one day out so January the
1st shows as Sunday when it is actualy Saturday. So to get the
correct week and day I have to change code to:
Week Number: DatePart("ww",[AppointmentDate]-1) and another field
DayOfWeek: (DatePart("w",[AppointmentDate]-1))
If you can show me how to attach additional expressions to the
query I would be very grateful
Life sure gets complicated
message What exactly are you trying to do in your queries?
Provided you're running the queries from inside of Access, you can
use the Format and DatePart functions in queries.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Thank you Douglas
will any of these have an effect on Queries? or do you have to
convert resulting data in the report?
message The Format and DatePart functions both have optional parameters
that let you specify both First Day of the Week, and First Week
of the Year.
There's also an API you can call (sorry, but I've forgotten the
exact one at the moment), but I don't believe there's any way to
make the change permanent.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi all
Does anyone know how to change the First Day of Week in access.
The system is realy strange. I have been working on this all
night. What I have found is that access or the regional
settings of my computer (I don't know which) counts the first
few days of the new year as the first week regardless of the
day names. Whereas most systems including diaries count the
first week as from the first sunday. So according to access 31
Dec 2004 was week 53 and 1st Jan 05 was week 1 now this is
where it gets confusing if you can't understand why your week
numbers are wrong, 2nd Jan 05 is week 2? meaning that week one
in access has only got 1 day!! I am hoping that if I change the
first day of the week to Sunday as in most diaries, that week 1
will have 7 days. Would be nice wouldn't it.
