Requiring a date input for a certain day of the week

[lubbs65]

in access for an input form I am trying to find a way to have the date inputs only work for a certain day of the week. Let's say Monday. This of course means if they type in a date that isn't a Monday, or click a spot on the calendar that is incorrect, an error should get split up.

If anyone knows of the best way to do this that would be great, but here are the methods I am currently exploring:
1) for the drop down calendar, if there is a way to "gray out" all non-Thursday dates so that they can't click them.

2) in Excel for a graph in you can have the this option: "[$-F800]dddd, mmmm dd, yyyy". so a date that is outputted on the axis would say "Thursday, June 9, 2011". If I could do the same in Access, but then under validation code write something like "In(Thursday) I can then require a thursday string for that date. Then, an error would shoot out if they did not select a thursday date. I have not been able to find out how to have dates automatically set to this format by either typing them, or clicking a random date on the calendar.

Another problem with idea 2) is that I have a program that runs these dates that need to be in the format "6/9/11" rather then "Thursday, June 9, 2011" So I would then need a second data type. say I have input_1 that only accepts those Thursday dates by searching for the string "Thursday" I would also need an input_2 that would always be equivalent to input_1 accept would require the format "6/9/11" (which seems to be default). Then when I run my program, I can just ignore input_1 and use input_2's dates.

Those are the only idea that I've been able to conjure. If anyone knows of a working method it would be very much appreciated!

 

[SydneyGeek]

You can create a drop-down list (combo box) that will only display Mondays. If you go to the Help in the code environment (Alt+F11, then F1) do a search for ListMondays.

You will get an article with a useful function that, by default, will display the next 4 Mondays. To change to another day, alter the 9 in the intOffset=... row to another number (8 for Sunday, up to 14 for Saturday). If the sequence starts too late, subtract 7 from these values.

There is another line in the code (ListMondays = 4) that determines how many items to display. If you want to change the number of Mondays, change this number.

The article also shows you how to use the code with the combo box.

Denis

 

[lubbs65]

This looks very close to what I need. There are some problems though:
1) This list only lists Mondays after the current date. If i were to go back and change a date from say 2009 to another 2009 Monday that seems impossible.

2) I currently have these "date" inputs as textboxes. is there a quick and easy way to change them to listboxes, or combo boxes? According to the help site the code requires a list or combo boxes and then I searched for "Row Source Type" in my text box in design view but it obviously was not there.

3)These textboxes have calendars associated with them so you can just click the calendar then select the date. Would I still be able to implement the calendar to also only allow Mondays after making this change?

 

[lubbs65]

update:
I figure I will simply delete the text boxes for dates and replace them with combo boxes. the code works but I still have all the same problems I thought I would encounter.

I also tried a different approach:
I set the date textboxes to the format "Long Date" so that there output is Monday, June 6, 2011. Now, if you type 5/6/11 into the box, it automatically changes it to the long form. If you click the calendar date it puts in 5/6/2011 then after hitting tab to go to a subsequent box it too changes to the long form.

Then I had the Validation code In("Monday"). With this validation in play, I get a problem. The Validation code executed before the 5/6/11 is ever changed to Monday, June 6, 2011. So I obviously get an error. Is there a way to have the validation code take into effect after the short form is changed to long form rather than prior to the change?