Can anyone help me with the vba code to display all the weeks in a given year. Like My week starts on Sunday and ends on Saturday. My start date is Jan1st and enddate is Dec31st. I want to display all the sundays in one column and all saturdays in other column in a year.
Thanks
cb naidu
there really should be no need to do this in a database, but regardless, to populate a table with this stuff, use a recordset object, the weekday() function to check for sats and suns, and the addnew and update methods to get it done.
Thanks for the reply. I didnot understand what you explained. Can u explain in detail how to do it. Can you also explain how to loop it for all the 52 weeks in access.
1. Write vba code to populate Start date and end date of the week and then insert it into the table.
or
2. Write a query to get the start date and end date of the week and display it in the form.
My problem is to loop through the records. I am able to get the first record but i am unable to loop it for 52 weeks. Your help is really appreciated.
It is not altogether appropriate on this forum to be asking for someone to do your homework. The purpose of the exercise is to use your noggin' to figure out how to do it, not get someone to show you! Think outside the box, try things, compile it, test it, figure out why it isn't working and try something else. Eventually you will get it, learn something in the process and will remember it for life, not just get an answer for a project you don't want to take the time to complete on your own. If you signed up for the class and don't wan't to go through the head-scratching it takes to learn, what's the point.
It is not altogether appropriate on this forum to be asking for someone to do your homework. The purpose of the exercise is to use your noggin' to figure out how to do it, not get someone to show you! Think outside the box, try things, compile it, test it, figure out why it isn't working and try something else. Eventually you will get it, learn something in the process and will remember it for life, not just get an answer for a project you don't want to take the time to complete on your own. If you signed up for the class and don't wan't to go through the head-scratching it takes to learn, what's the point.
I think if the two questions, as posted in post #5, are the two questions verbatim as asked by the teacher then you should ask the teacher for clarification.
The question should be; do you want the date of the first Sunday on or after 1 January or the date of the Sunday in the first week of the year?
it might be relevant. I have an app where i store a table of week start days, and corresponding week numbers.
if a company has a non-calendar financial year, they may need this.
---------------
its also a deceptively tricky project.
you need to be able to manage a loop
you need to understand how format works
you need to understand how the week "works" - ie how to determine when week 1 starts in a new year
you need to design a table to store the data
you need to be happy to write and execute a SQL statement / or find a way of managing the insert process with an append function
you need to be OK with forms - the easy bit
even distinguishing between Saturdays and Sundays to do this is a challenge.
ie does the week start on a Sat or Sun (or another day)
if it starts on a Sun, then the Sats are 1 wekk number earlier
if it starts any other day, then Sats and Suns are in the same week
except the first week, when you COULD have a floating sunday. (ie if 1s t Jan is a Sunday)
I suspect this would be a few hours of any of our times.
---------------
I think there is an issue with data values/format function maybe, also
its easy to say format(anydate, "ww") to get the week number
but is it so easy to take a week number, and determine the week start date FROM that week number, using VBA functions?
--------------
to help you, here's a little code snippet that will show you all the Saturdays in this year - this isnt exactly what you wanted, but it may help
Code:
Sub showsats()
Dim s As String
Dim d As Date
Dim w As Long
w = 0
s = "Saturdays " & vbCrLf
For d = #1/1/2010# To #12/31/2010#
If WeekDay(d) = vbSaturday Then
w = w + 1
s = s & " Week: " & Format(w, "00") & " Date: " & d & vbCrLf
End If
Next d
MsgBox (s)
End Sub
how do YOU know what my calibre is? I happen to be the most incompetent VIP of the AWF group that there is on this forum. ask anyone who has seen my work. it usually comes installed with 30% bugs. because of this fact, i offer discounted services.
how do YOU know what my calibre is? I happen to be the most incompetent VIP of the AWF group that there is on this forum. ask anyone who has seen my work. it usually comes installed with 30% bugs. because of this fact, i offer discounted services.
Haha!! That's a very generous offer. I really think the OP should consider this. You may want to edit the negatives so the OP can trust that his/her "investment" would yield fruits lol
why edit the negatives???????? that's my market niche! why would someone pay for the same old service when they could get service from me with all the bugs? we all want more for the money, don't we? when you get me, you get as much as you want for the money! i think we should cut this off though, the OP has probably gone elsewhere for help.
