Selecting Dates

[jpalk]

Hello,

I am a pretty new user to Access through work. I have a project I'm working on, and am not sure how to make it work. I assume a query will be necessary to accomplish this, so I've posted here, but if I should re-post elsewhere just let me know!

The scenario is this:

Our business is a resort. I would like to be able to input a guest's arrival date and the number of nights that they stay, and have the actual date of each day they will be at the resort returned in a selectable list.

So, for example, if a guest arrives May 1 for 7 days, the return will list May 1, May 2, May 3, May 4, May 5, May 6, & May 7.

Here's the catch--I need this list to return in a list box on a form or each in separate text boxes so we can select dates from those returned dates. If a guest wants to sign up for a particular activity at the resort on 3 of the 7 dates, but they are not "in a row," I want to be able to select the 3 days they've elected to join the activity so when we print out a report of all the guests requesting an activity on a certain date, they will do so.



Hopefully I have explained this clearly enough; I've tried to keep it as simple as possible. If anyone has any idea what I'm talking about & how I might do it, I'd really appreciate the help. I've done so much research in the Help sections & online to try & figure it out on my own, with no luck.

Thank you thank you!
Jess

[Rawb]

To return the result in a List Box, use the following Code:

Assumptions:
You are getting the number of days a guest is staying from a Text Box named "StayLength"
You are getting the guest's arrival date from a Text Box named "ArrivalDate"
Your List Box is called "AvailableDates"

  ' Empty out our List Box
  Me!AvailableDates.RowSource = ""

  ' Run this loop once for each day the guest is staying
  For i = 0 To (Me!StayLength - 1)
    If i = 0 Then
      ' If this is the first date, just set the RowSource of your List Box to the guest's Arrival Date
      Me!AvailableDates.RowSource = Me!ArrivalDate
    Else
      ' Otherwise, use our counter (i) to add to our Arrival Date and use that instead
      Me!AvailableDates.RowSource = Me!AvailableDates.RowSource & ";" & DateAdd("d", i, Me!ArrivalDate)
    End If
  Next

Try putting that in the StayLength's After Update Event and see how it works for ya.

EDIT: Added bold to the names of the various Form Objects that may need to be changed.

[jpalk]

Hi,

Thanks so much for your quick response, I appreciate it.

Because I'm so new, & have no experience with VBA or code of any kind, before I plug this in can I just copy & paste it in, or do I need to replace anything with my own data?

So excited to give this a try, thanks again for your help!

Jess

[Rawb]

Hi,

Thanks so much for your quick response, I appreciate it.

Because I'm so new, & have no experience with VBA or code of any kind, before I plug this in can I just copy & paste it in, or do I need to replace anything with my own data?

So excited to give this a try, thanks again for your help!

Jess

The VBA Code posted above is pretty much ready to go. All you should have to do is cut & paste it into the "After Update" Event of whatever Form Object you fill out last.

Make sure you change the bolded sections to whatever you're using as the names of your Form Objects though.

P.S.
The bold text wasn't there until just now >.>