Hi,
I have the following requirements
In a form, there exists a date field which needs to be automatically populated. The date has to be the Friday of every week. Assume in a calendar week Feb 4th - Feb 11th, if the user opens a form for data entry anytime between Feb 4th(Sunday) - Feb 9th (Friday), the data field should populate with Feb 9th date. If the user opens the form anytime after Feb 9th, the date field should populate with the next week's friday's date i.e. Feb 16th.
Can anyone pls help me with this?
Thanks,
Shank
11 2809
Take a look at this link it should help you with what you want.
For example this here will return the last sunday - Week Beginning: DateAdd("d",1-Weekday([GYourdateField]),[YourDateField])
Weekday is an Access built in function.
Good luck
Here is some code I made for you. There are really better ways to make this with the date functions but this works too. You can do this on the On Form Load or On Current
Good luck -
Private Sub btnSubmit_Click()
-
Dim myDate As Date
-
myDate = Date ' Date is equal to today's date
-
-
' The weeks starts with sunday which equals 1 Friday = 6
-
If Weekday(myDate) >= 2 And Weekday(myDate) <= 6 Then
-
Do While Weekday(myDate) <> 6 ' If our date is not Friday's date
-
-
myDate = DateAdd("d", 1, myDate) 'Adds a day to my date to find out the date for friday
-
-
Loop
-
Me.txtWeekofDate = myDate 'Sets your control equal to that date
-
Else
-
myDate = DateAdd("d", 2, myDate) ' Adds two days to account for the fact that it might be Saturday.
-
If Weekday(myDate) >= 2 And Weekday(myDate) <= 6 Then
-
Do While Weekday(myDate) <> 6
-
-
myDate = DateAdd("d", 1, myDate) 'Adds a day to my date to find out the date for friday
-
-
Loop
-
End If
-
Me.txtWeekofDate = myDate
-
-
-
End If
-
-
-
End Sub
-
NeoPa 32,556
Expert Mod 16PB
Try setting Default property of the control to : - =DateAdd("d",7-Weekday(Date(),7),Date())
Here is some code I made for you. There are really better ways to make this with the date functions but this works too. You can do this on the On Form Load or On Current
Good luck -
Private Sub btnSubmit_Click()
-
Dim myDate As Date
-
myDate = Date ' Date is equal to today's date
-
-
' The weeks starts with sunday which equals 1 Friday = 6
-
If Weekday(myDate) >= 2 And Weekday(myDate) <= 6 Then
-
Do While Weekday(myDate) <> 6 ' If our date is not Friday's date
-
-
myDate = DateAdd("d", 1, myDate) 'Adds a day to my date to find out the date for friday
-
-
Loop
-
Me.txtWeekofDate = myDate 'Sets your control equal to that date
-
Else
-
myDate = DateAdd("d", 2, myDate) ' Adds two days to account for the fact that it might be Saturday.
-
If Weekday(myDate) >= 2 And Weekday(myDate) <= 6 Then
-
Do While Weekday(myDate) <> 6
-
-
myDate = DateAdd("d", 1, myDate) 'Adds a day to my date to find out the date for friday
-
-
Loop
-
End If
-
Me.txtWeekofDate = myDate
-
-
-
End If
-
-
-
End Sub
-
Thanks so much for the quick response. A question on your logic.
//myDate = DateAdd("d", 2, myDate) ' Adds two days to account for the fact that it might be Saturday.
When I did some research, i found that Saturday has a weekday value of 7. I presume your logic works on the assumption that Saturday has a value 0? or maybe my understanding is incorrect? And is there a specific reason you chose the range 2 to 6 for weekday instead of 1 to 6?
Thanks again!
NeoPa 32,556
Expert Mod 16PB
If you look at post #4 you'll see that this really doesn't need to be this complicated.
If you look at post #4 you'll see that this really doesn't need to be this complicated.
Hi NeoPa,
Thanks for your reply. It works fine. Just curious about how the logic works in your case. I understand that Weekday(Date()) returns a number from 1-7 and date add adds a particular # of days to the current date.
In your suggestion, it says
DateAdd("d",7-Weekday(Date(),7),Date())
Weekday(Date(),7) - does this take the smaller of the two parameters Weekday(Date() and the value 7? And also, how does it take care that it always updates to the following Friday's date?
Thanks again!!!
NeoPa 32,556
Expert Mod 16PB
No Problem. - =DateAdd("d",
-
7-
-
Weekday(Date(),7),
-
Date())
- Weekday(Date(),7) returns the weekday number but starting from 1=Saturday rather than 1=Sunday.
- 7-Weekday(Date(),7) is therefore, the number of days until Friday. Saturday=1 so 7-1=6. Friday=7 so 7-7=0.
No Problem. - =DateAdd("d",
-
7-
-
Weekday(Date(),7),
-
Date())
- Weekday(Date(),7) returns the weekday number but starting from 1=Saturday rather than 1=Sunday.
- 7-Weekday(Date(),7) is therefore, the number of days until Friday. Saturday=1 so 7-1=6. Friday=7 so 7-7=0.
Wow! didnt know we could manipulate Weekday function this way.. Thanks for the explanation.
NeoPa 32,556
Expert Mod 16PB
No problem. - Go to your VBA window (Alt-F11 from the Access window).
- Go to your Immediate window (Ctrl-G).
- Type in a function (EG. Weekday).
- Press F1 for context-sensitive Help.
This can give you information about all sorts of things within Access.
No problem.- Go to your VBA window (Alt-F11 from the Access window).
- Go to your Immediate window (Ctrl-G).
- Type in a function (EG. Weekday).
- Press F1 for context-sensitive Help.
This can give you information about all sorts of things within Access.
Thanks. Did try that out. Can you pls help me with the thread "Insert into" creating issues?
NeoPa 32,556
Expert Mod 16PB
That's a new one today. I'll have to get to it as and when I can I'm afraid (I have a number of threads I'm trying to keep up with that I'm active in atm). I should get around to that later this evening or tomorrow.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bennett Haselton |
last post by:
I want to get an ASP.Net hosting account with my ISP, and I'm trying
to find out what level of access to the server is requried in order
for me to view the server in Server Explorer in Visual...
|
by: sedefo |
last post by:
I ran into this Microsoft Patterns & Practices Enterprise Library while i
was researching how i can write a database independent data access layer. In
my company we already use Data Access...
|
by: B Moor |
last post by:
Hello,
I am quite bogged down with this problem and would like some tips/help
if any one has any.
Thanks in advance.
The Problem
-----------
This system initially seemed quite stable for...
|
by: Jean |
last post by:
Hi,
I am using an Access 2000 front-end to an Oracle 9 backend.
I want to write a query that returns all records that are not older
than one year for Column "Status_30" (which is a Date).
...
|
by: Eric Girouard |
last post by:
Hi
A web design client of mine has been developing an Access database with
me for the last few months. His intention was to clean up a very messy
paper-based workflow consisting of many location...
|
by: Jordan S. |
last post by:
SQL Server will be used as the back-end database to a non trivial client
application.
In question is the choice of client application:
I need to be able to speak intelligently about when one...
|
by: Ira Solomon |
last post by:
Hi:
Any quick opinions on Access 2007?
Has anyone got this to coexist with Access 2003?
Thanks
Ira
|
by: starke1120 |
last post by:
Im creating a check in – check out database for RF guns. I have a
table that contains models.
ID (primary key)
Model
A table that contains Gun Details
ID (primary key)
Model_id...
|
by: scubasteve |
last post by:
Looking up values from an Access table is simple. Simulating the 'Range Lookup' functionality from Excel's VLookup formula is a bit trickier.
For those that aren't familiar with this, it allows...
|
by: beacon |
last post by:
Hi everybody,
I've searched and searched for the answers to these questions until I've turned blue in the face, so I finally decided to come here for some assistance.
Here's the info I'm...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |