-
Jun 22nd, 2011, 05:46 AM
#1
Thread Starter
Frenzied Member
Access - Function to return the Date (Monday & Saturday date of current week
Hi
Iam in need of a function (VBA-Access) to return the date on Monday & Saturday of the current week.
May somebody help...
-
Jun 22nd, 2011, 07:30 AM
#2
Re: Access - Function to return the Date (Monday & Saturday date of current week
Try this:
Code:
Function ThisMonday() As Date
ThisMonday = Date + vbMonday - Weekday(Date, vbSunday)
End Function
Replace vbMonday with vbSaturday you will have ThisSaturday and similar for other days.
-
Jun 23rd, 2011, 02:31 AM
#3
Re: Access - Function to return the Date (Monday & Saturday date of current week
Anhn its very nice, a simple adding with ur code
Code:
Enum Thisday
Sunday = vbSunday
Monday = vbMonday
Tueday = vbTuesday
Wednesday = vbWednesday
Thuday = vbThursday
Friday = vbFriday
Satday = vbSaturday
End Enum
Function ThisWeekDay(MyDay As Thisday) As Date
ThisWeekDay = Date + MyDay - Weekday(Date, vbUseSystem)
End Function
-
Jun 23rd, 2011, 08:44 AM
#4
Re: Access - Function to return the Date (Monday & Saturday date of current week
Originally Posted by seenu_1st
Anhn its very nice, a simple adding with ur code
What you added is incorrect in cases the system FirstDayOfWeek is not Sunday.
You must always use vbSunday in Weekday(Date, vbSunday) for the function.
Your Enum is not need, you can use vbDayOfWeek instead.
-
Jun 23rd, 2011, 08:57 AM
#5
Re: Access - Function to return the Date (Monday & Saturday date of current week
-
Jun 23rd, 2011, 08:59 AM
#6
Re: Access - Function to return the Date (Monday & Saturday date of current week
Originally Posted by anhn
What you added is incorrect in cases the system FirstDayOfWeek is not Sunday.
You must always use vbSunday in Weekday(Date, vbSunday) for the function.
Your Enum is not need, you can use vbDayOfWeek instead.
not only that but it looks to me that it is overkill to create the Enum based on 7 constants that already have those values. Just to get rid of the vb prefix?
And I have a question. Are the 'abreviations' intentional or typos? Tueday, Thuday, Satday
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jun 23rd, 2011, 09:37 AM
#7
Re: Access - Function to return the Date (Monday & Saturday date of current week
i just made it to make the function to use as a general function for all days, bt there is an easy solution vbDayOfWeek, and the abreviations is just for a sample thats all. anyhw thanks for the infos.
-
Jun 23rd, 2011, 10:09 AM
#8
Re: Access - Function to return the Date (Monday & Saturday date of current week
Might be being thick :/ have a look at the format function :
Code:
debug.print format(now, "dddd")
Edit:
Scratch that - must read the post! lol sorry
What happens if the date is a sunday?
Code:
Public Sub FirstAndLastOfWeek(ByVal dte As Date, ByRef dteMonday As Date, ByRef dteSaturday As Date)
Select Case Weekday(dte)
Case 1
dteMonday=dte+1
Case Else
dteMonday = dte - (Weekday(dte) - vbMonday)
End Select
dteSaturday = dteMonday + 5
End Sub
Public Sub t()
'---- testing sub...
Dim dteS As Date, dteM As Date, dteN As Date
FirstAndLastOfWeek Now, dteM, dteS
Debug.Print dteM, dteS
End Sub
Last edited by Ecniv; Jun 23rd, 2011 at 10:20 AM.
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Jun 24th, 2011, 05:41 AM
#9
Thread Starter
Frenzied Member
Re: Access - Function to return the Date (Monday & Saturday date of current week
Thankz anhn
could you tell me more about this portion..
also let me know if the below code is equally good or not..
Code:
Me.txtStartDate = Date - Weekday(Date, vbSunday) + 2
BTW if I also need the saturday date, then...I tried adding vbSaturday instead of vbMonday is that correct....
-
Jun 24th, 2011, 09:06 AM
#10
Re: Access - Function to return the Date (Monday & Saturday date of current week
VB Constants: vbSunday = 1, vbMonday = 2, ..., vbSaturday = 7
So, you can use:
Code:
Me.txtStartDate = Date - Weekday(Date, 1) + 2 '-- Monday
Me.txtEndDate = Date - Weekday(Date, 1) + 7 '-- Saturday
But use vb...day is clearer.
Also because the default of the 2nd parameter of Weekday() function is vbSunday=1, so you can omit it if you want: use Weekday(Date) instead of Weekday(Date, 1) or Weekday(Date, vbSunday).
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|