How can I format my date picker or add a text box to display first day of the week?

I have a form with a date picker that will filter a sub-form with a per week value I.E. if the user selects 3-7-18 the sub-form will display all records in this week (3-4-18 to 3-10-18)

How can I format the date in the picker or add another text box to display the date in this format:
If the user selects 3-7-18 in the date picker:
Week of: 3-4-2018        (always display the start of the week date)
Bonus would be to add the week number:
Week number: 10 For week of: March 4 2018
DJPr0Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Here's a link to the various datetime functions
https://support.office.com/en-us/article/access-functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83

If you can't find what you need there, the Format() function also has date options.  This is the function that will give you the week number.
https://support.office.com/en-us/article/format-function-6f29d87b-8761-408d-81d3-63b9cd842530

To get the first day of a week subtract 1 from WeekDay(yourDate).  The result is the number of days you need to subtract from your date to get to the first day of the week.  So the whole expression would be:
DateAdd("d", WeekDay(YourDate) * -1, YourDate)
0
Gustav BrockCIOCommented:
I guess you mean:

FirstOfWeek = DateAdd("d", 1 - WeekDay(SelectedDate), SelectedDate) 

Open in new window

0
DJPr0Author Commented:
Pat,

Here is the edit for the function to supply me with Monday's date:
=DateAdd("d",-Weekday([txtWeekOf])+2,[txtWeekOf])

2 issues:
I am using this function in a separate text box to display this date due to I don't think this will work directly on my date picker ([txtWeekOf]).
- when I first open the form (before I use the date picker) the text box with the dateadd code displays #Type! - how can we add Nz or null function to thwart the error message.
- can I adjust the current date format from 3/5/18 to March 3, 2018?
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Gustav BrockCIOCommented:
Originally, you had Sunday as the first day, now Monday. You cannot have both.

In any case, your last expression will fail. Use either of these expressions:

' Sunday:
FirstOfWeek = DateAdd("d", 1 - WeekDay(Nz([txtWeekOf], Date())), Nz([txtWeekOf], Date())) 
' Monday:
FirstOfWeek = DateAdd("d", 1 - WeekDay(Nz([txtWeekOf], Date()), vbMonday), Nz([txtWeekOf], Date())) 
' or
FirstOfWeek = DateAdd("d", 1 - WeekDay(Nz([txtWeekOf], Date()), 2), Nz([txtWeekOf], Date()))

Open in new window

For the format use:

mmmm d, yyyy

Open in new window

0
DJPr0Author Commented:
Gustav,

I receive this error, before and after I use the date picker (txtWeekOf):
#Name?
Using in a text box in expression form:
=DateAdd("d",1-Weekday(Nz([txtWeekOf],Date()),[vbMonday]),Nz([txtWeekOf],Date()))
0
Gustav BrockCIOCommented:
First, vbMonday is a constant:

=DateAdd("d",1-Weekday(Nz([txtWeekOf],Date()),vbMonday),Nz([txtWeekOf],Date())) 

Open in new window

However, it must be replaced by its numeric value when used in an expression as ControlSource:

=DateAdd("d",1-Weekday(Nz([txtWeekOf],Date()),2),Nz([txtWeekOf],Date())) 

Open in new window

0
DJPr0Author Commented:
Gustav,

Works, however when I first bring up the form it display's a date of 3-5-2018 (this must be from the Date() variable)

Any we to stop displaying a date before I select a date in the date picker?

This is not a big issue.
0
Gustav BrockCIOCommented:
Yes, you can use IIf and IsNull:

=IIf(IsNull([txtWeekOf]),Null,DateAdd("d",1-Weekday(Nz([txtWeekOf],Date()),2),Nz([txtWeekOf],Date())))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DJPr0Author Commented:
Thanks Gustav
0
Gustav BrockCIOCommented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.