Between dates query

[dave_1985]

Hi everyone, I am a relatively inexperienced access user and I am having problems with a query on my database.

Im creating a hotel reservation booking system and I want to run a query that will show active bookings on a certain date. For example, if i enter the date 2/1/2008 I want it to show bookings made from dates that span this date.

Example

Booking Start Date Booking End Date
31/12/2007 4/1/2008
1/1/2008 3/1/2008

So now if I enter 2/1/2008, I want these 2 records to appear. Any advice about how to do this? It all seems a bit complicated to me

 

[neileg]

You need to capture the user date in a form so you can apply it as a criterion to both dates. These become in the SQL of your query:

WHERE ([Booking Start Date]>= Forms!MyFormName!MyControlName) AND ([Booking End Date]<=Forms!MyFormName!MyControlName)

 

[rockyjr]

or you can try this :

In your query add this to the Criteria of your date :

Between [start date] And [end date]

or add this if you have date fields that are blank

 IS NOT NULL AND Between [start date] And [end date]

You'll be ask to enter a start date and an end date (ie: 2008/01/01)

and/or, if you want to have a preset on a form.....
create a start date and end date field (set FORMAT as Short Date)

and on the form, go to the OnCurrent Event, select code builder and enter this :

Private Sub Form_Current()
Me.star tdate = Date - 365
Me.end date = Now()
End Sub

this is for a year by default... if you want a month by default, just enter -31 instead of -365

And make sure that the start date and end date are the name of the fields that where created on the form.