Building an Expression, Date Range, Parameters.

[alicias4]

So I am very new to Access; I've learned quite a bit form google and I am trying to complete an assignment.

Please let me know if this is possible.

I have one simple database that includes the name of a room, the daily rate, the arrival date and the departure date.

I'm being asked to come up with the base price per room during a specified period of time (Length of stay x daily rate)

I have successfully created a query which gives me the length of stay for each room using the Datediff function +1; I understand that I can create a parameter query using Between and a specified date, or to enter text such as [Start Date] And [End Date]

The problem I'm trying to wrap my head around, and maybe I'm just complicating things, but for example. First room arrival and departure is: 12/01/2010 to 12/04/2010 (So 4 days)

If I wanted to run a query for say 12/01/2010 to 12/02/2010; or even 12/02/2010-12/03/2010 how do I go about that?

Is there a function somewhere that will return the date values between a specific time frame that I could count, or some easier way to create an Iif function..

i.e - If the [departure date] is included in the specified time frame, take the departure date - arrival date + 1 * the daily rate (But then what if the arrival date isn't within that time frame either? -- I would end up with incorrect values, as mentioned above, if I tried a function like that for 12/02/2010 to 12/03/2010 neither are included, but the room was booked during that time, and I would like it to show the 2 days..)



Much help or guidance would be greatly appreciated.

Thank you!

[alicias4]

I should mention I'm using Access 2010; I have no idea if it's 32 or 64 bit or how to tell.. I'm not home right now

[pbaldy]

There is no native function I know of that will return all the dates between two specified dates. The only way that comes to mind is either having a table of all dates, or populating one on the fly. I have a couple of things where I need to LEFT JOIN against a table of all dates in a given period, and I use a loop to add each date between the start and end dates to a temp table.

[alicias4]

And I'll have to go look up what all of those mean.. Like I said, I'm completing this blind and learning as I go. I'll look up what Left Join and loops are and get back to you if I have further questions. Thanks for your input.

[alicias4]

I'm seeing that that stuff is VBA which is way beyond my knowledge. I don't even know where in Access I would input VBA or SQL code.

[pbaldy]

Can you post a sample db here and the result you'd want to see from it?

[alicias4]

I can try, I'm at work now without access to the program, so I will post tonight when I get home.

[Rawb]

SELECT * FROM MyTable WHERE [CheckIn]<=[MyDate] AND [CheckOut]>[MyDate]

If [CheckIn] and [CheckOut] are the Fields in your Table that correspond to the date they checked in and out, then the above Query should return all Records where the room is currently booked on that date (Using [MyDate] as a Parameter to hold the date you're checking on).

Note: The above query assumes that the room will be free on the checkout date (so they're leaving the morning of checkout, not staying the whole day).

[alicias4]

I tried the above code, what I ended up with is:
SELECT *
FROM Reservations
WHERE [Arrival Date]<=[From] AND [Departure Date]>[To];

What that does is if I enter 12/01/2004 to 12/03/2004 it populates nothing. Whereas the first entry on the table the arrival is 12/01/2004 and the departure is 04/12/2004.

I need to to return which room, and the number of days. I'm sure it'll be a multistep process, and I might have to do a few queries to get there. I'm just stumped. I'll try to upload the database, but I've never done it so bare with me.

[alicias4]

Reservations.accdb

Here is the attachment, I had to delete a number of other random queries I was trying to make the size right.

[Rawb]

Sorry, I didn't realize you were dealing with a date range in your search as well. My previous Query wouldn't work for something like that, only if you are comparing both the checkin and checkout times against the same date

I'll do a little more research on the subject, it might just be possible using a Subquery, but I don't think that's likely. The more likely scenario is that you'll have to build a Form to house some VBA Code and a Report to display the data.

The Form would only need to have two text fields (a "from" date and a "to" date) along with a Button that would open the Report when clicked. The Report could be pointed to a query that woudl include every Record in your Reservations Table. VBA code would be used to filter down the Report from there.

If you want to get started on setting up a Form, the following VBA code should do what you want:

    ' Declare our variables
    Dim i As Long
    Dim dteTemp As Date
    Dim nbrDays As Long
    Dim strCriteria As String
    Dim strDocName As String

    i = 0 ' Initial value, leave as-is
    strCriteria = "" ' Initial value, leave as-is
    strDocName = "rptShowReservations" ' the name of your Report!

    ' Make sure the user has entered a valid date range. Only one of the date
    ' fields needs to be used when searching for a single date and not a whole
    ' range.
    If Len(Me!FromDate & vbNullString) = 0 And Len(Me!ToDate & vbNullString) = 0 Then
        ' If no dates are entered, alert the user and make them enter at least one
        ' date
        MsgBox "Please enter a date range to query."

        Me!FromDate.SetFocus
    Else ' If the user entered a valid date range
        ' If the user only entered a single date, copy it to the other field for a
        ' valid date range (of 1 day)
        If Len(Me!FromDate & vbNullString) = 0 And Not Len(Me!ToDate & vbNullString) = 0 Then
            Me!FromDate = Me!ToDate
        ElseIf Not Len(Me!FromDate & vbNullString) = 0 And Len(Me!ToDate & vbNullString) = 0 Or _
            Me!ToDate = Me!FromDate
        End If

        ' Make sure the FromDate comes before (or on) the ToDate
        If Not Me!FromDate<=Me!ToDate Then
            ' If not, switch the dates!
            dteTemp = Me!FromDate
            Me!FromDate = Me!ToDate
            Me!ToDate = dteTemp
        End If

        ' Number of days in the date range
        nbrDays = DateDiff("d", Me!FromDate, Me!ToDate)

        ' build a WHERE Clause (WhereCondition, actually) that searches for each day
        ' in the date range
        For i = 0 To nbrDays Step 1
            strCriteria = strCriteria & " ([Arrival Date]<=#" & DateAdd("d", i, Me!FromDate) & "# And [Departure Date]>#" & DateAdd("d", i, Me!FromDate) & "#) Or "
        Next i

        ' Cut off that last "Or" in our WhereCondition
        strCriteria = Left(strCriteria, 1, Len(strCriteria) - 4)

        ' Open the Report, filtering by our WhereCondition
        DoCmd.OpenReport strDocName, acViewPreview, , strCriteria

I'm assuming you'll named the two date fields on your Form "FromDate" and "ToDate."

[alicias4]

I'm not exactly sure where to start... I created a blank form and put in 2 text boxes, called From and To. I added a button which I labelled okay.

If I right-click the form, I can click build and it takes me to VBA where I pasted your code. (One of the lines is red) I tried to build the button to run that code when pushed, but I obviously haven't the foggiest clue ha. VBA is entirely new to me, so I'm not quite sure where to put all of this stuff to make it actually run.

[pbaldy]

This should get you started.

http://www.baldyweb.com/FirstVBA.htm

[alicias4]

That makes a bit more sense on where I should be focusing my attention. I went to event for the form, chose click, and entered the code. When I entered my from date and to date from my text boxes, I received this error.

[pbaldy]

I don't think you ever addressed this:

Can you post a sample db here and the result you'd want to see from it?