Finding a weeknumber from a table

[Finchy]

Hello All,

I am hoping someone can help me with a problem in VBA, I am pretty sure its an easy solution but my knowledge is limited with VBA, I am a bit of a newbie.

I have a calender table which contains every date for a year (April to April) and the corresponding week number.

I am coding a button to scroll through these week numbers to display that specific week in a form below, so far it works fine!

Private Sub Command76_Click()
On Error GoTo Err_Command76_Click
Dim week As String
Dim lastweek As String



lastweek = DMax("[WeekNo]", "tblCalendar")
week = Me.txtWeekNo




If week = lastweek Then
Me.txtWeekNo = "1"
DoCmd.Requery
cboName.Locked = False
DoCmd.GoToRecord , , acFirst

Else
week = Me.txtWeekNo + 1
Me.txtWeekNo = week
DoCmd.Requery
cboName.Locked = False
DoCmd.GoToRecord , , acFirst
End If


Exit_Command76_Click:
Exit Sub


Err_Command76_Click:
MsgBox Err.Description
Resume Exit_Command76_Click

'Me.txtWeekNo = Null


End Sub

My problem is thus:

Because the dates run April to April the actually last week number is 13. I need to use a bit of code which looks at the last date (31/03/2011) and gives me the week that date corresponds too so I can stop the user going past this week number. I am pretty sure its a Dmax but I am not sure how to go about it. Any help would be appreciated.

Regards,

John (Finchy) Finch

 

[JANR]

Any reason you can't use the inbuild DatePart function to get the weeknumber?

DatePArt("ww", [DateField])

JR

 

[Finchy]

Hmmm - that idea never crossed my tiny little mind although I have just tried it and theres a difference between the last week number in my table and the week number access produces using the last date.

My table shows - 31/04/2011 as week 13.
my code:

Dim week As String
Dim lastweek As String
Dim lastdate As Date
Dim lastwkno As String



lastweek = DMax("[WeekNo]", "tblCalendar")
lastdate = DMax("[Date]", "tblCalendar")
week = Me.txtWeekNo
lastwkno = DatePart("WW", lastdate)

This returns the weeknumber as '14'. Perhaps the data for the table is generated differently (this is not my database, I have just been tasked with improving it.). Is there anyway to look at the actual table to gain the week number next to the last date?

BTW, I really appreciate you taking the time to help me.

John

 

[spikepl]

Weeknumbers are hell's creation, because different standards exist, and Access 2003, and probably also 2007, does not get the ISO-standard right. I am not sure what the status is, so I have my own function. But look at what Microsoft says: http://support.microsoft.com/kb/200299

Besides, check ALL the params of DatePart for calculating weeknumber. Your weeknumber, without those params, uses default US-values, which is probably why it gets it wrong.

 

[JANR]

Well April does only have 30 days not 31.

datepart("ww",#30/4/2011#,,2) gives you week 17 which is the correct week.

Look in Access help for further details.

JR

 

[Finchy]

Hi spikepl.

I altered my code to look like this: lastwkno = DatePart("WW", lastdate, , 2)

It works now. In another part of this database there is a form for creating this tblcalender, looking at that it uses the same method to create the week numbers. I did look before writing this thread but didnt really know what I was looking at.

Thanks for solving my little problem anyway, I love this forum and its people like you guys responding so fast that makes it so good.

John Finch

 

[spikepl]

#6

If you use weeknumbers only to compare data and you generate those weeknumbers yourself, then there is no problem, if they all are calculated using the same function, whether it gives right or wrong results. If the weeknumbers are used to relate to external data, then I would urge you to check the link I posted.

 

[Finchy]

#6

If you use weeknumbers only to compare data and you generate those weeknumbers yourself, then there is no problem, if they all are calculated using the same function, whether it gives right or wrong results. If the weeknumbers are used to relate to external data, then I would urge you to check the link I posted.

Yes, I know what you're saying. I have done a little testing, by adding next years dates and its spot on. I cannot forsee a problem with the method I have used.

John