Append a Date Range

[NiyiO]

Hi Guys

Can someone please help me with this? I have an Events form which has some date fields setupdate and enddate. There is a linked events calendar subform attached. I have attached the database for you to see.

If you open the events form and go to the events date tab, I want to be able to type in the dates in the setup and end date fields, and then run a query which populates the events calendar subform. I created a date table (dates) with a list of dates and tried to create an append query (Query3) with those fields as criteria which didn't work as it wasn't recognising the parameters (but i'm guessing that is because it wasn't attached to the Events Form). However after entering the dates, I was able to append them to the events calendar only after including the EventsID field to the dates table and the query. This however means that the records aren't linked to the Events table as the EventsID field is blank (the EventsID is what links the E.Calendar subform to the Events Form). Therefore though the records were reflected in the events calendar form, it wasn't showing up on the events form.

I dont know if this makes much sense but what I basically want to do is a series of functions. Create a query which extracts a set of dates based on criteria entered into the main form, and appends them to a datasheet subform which is linked to the main Events form via the EventsID, then copies the Event ID for all the recordsets that have been added.

Is this possible? Thanks for your help in advance.

 

[Angelflower]

Hello NiyiO,

What I have learned from these borads is that you can do it one of three ways....

1. >#10/1/2006# And <#10/5/2006#

or you could use

2. Between [Begining Date] And [Ending Date]

or

3. Between Forms![frmDate]![txtBegDate] And Forms![frmDate]![txtEndDate]

You could use a form with two unbound text boxes for start and end dates. Then add the above code to the date query field that I want to narrow the record on.

Private Sub Form_Load()

' Enter parameters in BeginningDate and EndingDate text boxes when
' form is loaded.
Me!BeginningDate = ""
Me!EndingDate = ""

End Sub

Private Sub Preview_Click()
' Preview report.
Dim strDocName As String

On Error GoTo Err_Preview_Click

'Check to see that ending date is later than beginning date.
If IsDate(BeginningDate) And IsDate(EndingDate) Then
If EndingDate < BeginningDate Then
MsgBox "The ending date must be later than the beginning date."
Exit Sub
End If
Else
MsgBox "Please use a valid date for the beginning date and the ending date values."
Exit Sub
End If

strDocName = "report name"
DoCmd.OpenReport strDocName, acViewPreview

Exit_Preview_Click:
Exit Sub


Hope this helps!
Angelflower

 

[NiyiO]

Thanks Angelflower. It's given me some ideas and I have had to rethink a few things but thanks for your help.