Access: force a user to choose a friday from the calendar?

On one of my Access forms I have a field that the user can add a date to. It formatted to 'date'  so the little calendar appears when they click on it and they chose the date from there.

Is there a way to limit their choice to a Friday?
Who is Participating?

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

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.

Haris DulicIT ArchitectCommented:
I think you cannot do that but you can set a control afterwards to check is the friday selected:

If Weekday(Me.DatePicker) <> 5 Then
  MsgBox "You Can Select only Friday!"
  Me.DateTextBox = Me.DatePicker
End If

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
As samo4fun indicated, you can't modify the calendar that I'm aware of, but you can check if the weekday for a date is a friday in the controls beforeupdate event.


Cancel = True

 if an error occurs.   This will stop the control from accepting the value

Friday is day 5 of the week if it starts on Monday.  It is day 6 if the week starts on Sunday.  In the US we generally use Monday as the start of the week but you can override that if necessary.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.


normally the user should know that he should only choose a Friday, i.e. through a hint on the form or anything else like that.
So the more userfriendly method would be to check the chosen date after input and programmatically change the date to the Friday date of the same week. On this way the user can click on any date in a week and automatically gets the Friday date which is better than choosing a date and annoy him with a message box which says "hehehe...silly user, you have again chosen the wrong day, try it again and if you do not choose a Friday I will come back to laugh at you again!"
As said above, the user should already know to choose a Friday so he will not be surprised that the date changes to the Friday date of the same week, he will instead love the comfort.


MeginAuthor Commented:
samo4fun: That looks great, but I can't use Me.DatePicker. DatePicker is not available to me.

Bitsqueezer: That would be fantastic, but I have no idea where to start with that. Can you help me out further?
Haris DulicIT ArchitectCommented:
You need to download it and register it to your system..

Download here :

then use this steps to register it

Than you will be able to use it
The date picker has been integrated since A2007.  It is automatically activated for any control bound to a field with a date data type.  To make an unbound control a "date",  you need to specify a date format for the control.
MeginAuthor Commented:
Sorry. I don't think I was clear.

The actual datepicker attached to the field works fine.
The problem is using it in the VBA code. It is not a method/data member that is available to me.
Haris DulicIT ArchitectCommented:
maybe it is Me.DatePicker1?
MeginAuthor Commented:
And I can't download it. I am on a network and am not an administrator. Downloading it requires administrator rights/
MeginAuthor Commented:
When I type in "Me.", nothing beginning with the word "Date" shows up.

as you already said in your initial post you already have a date picker so I guess you have at least A2007. So you don't need to download anything here.

The date picker itself cannot be programmed, it is automatically activated if the field is of date type and you didn't explicitly deactivated it.

But that's not needed, let the user simply choose a date of the week he want and then use i.e. the AfterUpdate event of the textbox to change the date to the Friday date.

If your textbox has the name "MyDate" it would be as simple as that:
Private Sub MyDate_AfterUpdate()
    Me.NyDate = DateAdd("d", 5 - Weekday(Me.MyDate, vbMonday), Me.MyDate)
End Sub

Open in new window



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
MeginAuthor Commented:
That is perfect!!!!!

Just what I needed. Thank you!!!
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.