Hello,

I have a table in which the rows each contain the date that they were added.
I am wanting to let a user select a start date and an end date.
Then append the rows that have the dates between the two selections to a report.
Do I need to make a form to do this or can it be done on the report itself?

Thanks
-Dave

Ideally what you want to do is to set the report to use that table as the recordsource. Then use text boxes on a form for your user to enter the dates. Then when they submit the dates, you validate them and then build a string, something like "myDateField > #1/1/2006
# and myDateField <= #5/31/2006#"

Now use the DoCmd.OpenReport command to open your report and pass the string in as the criteria parameter.

HTH

Mike

Mike
EchoVue.com

Thanks,

I'm working on it now.

-Dave

Well, I'm having a problem.

I made the form with two comboboxes that pull the dates(Begin and End) from the date field in my table. When I run the form and select a date from the combobox I get an error message saying that the value entered is invalid.
I thought I had it narrowed down to being the columnwidths property, but it didn't seem to have any effect.
Any ideas on this?

thanks

-Dave

OK - I get this a lot!!! What you want to do, is make sure that the combo boxes are not bound to the table. So under the datatab, delete anything in the data source field. Then under record source click the button with the ... and set up a query that just pulls in the data field.

Let me know how that goes and we can take it from there.

Mike

Mike
EchoVue.com

The other option, would be to change the controls to unbound textboxes and have the user enter the complete date. This would be good if you want a report from 5/1 - 5/30 and perhaps those dates don't occur in the table. Afterwards, I can walk you through ensuring that the dates are valuable.

If you feel really adventurous, I can send you a part of a database that I did recently, that pops up a calendar for the user to pick from, and that way, you ensure that the date is entered in the right format!

Mike
EchoVue.com

Hey Mike,

Thanks for replying.

I figured out why I was getting the error.
Somehow the rowsource got set to pull the date column and the ID column. So, I deleted the ID column and now the comboboxes are working fine, as far as that goes.

Am I still going to need to do what you recommended about deleting the record source and building a query? Or do you think it will work like I have it now?
The calendar would be cool, but the field contains the date and the time, so I don't know if it would work.

Thanks again,

Dave

Hi Dave,

I probably wouldn't worry about changing the rowsource etc. now. See what you can do as far as now running the report, using a filter. I need to head into a meeting (gotta love those Friday afternoon snoozes!) and I'll check back in after that to see if you need anything else.

Mike

Mike
EchoVue.com

Hi Mike,

Hope your meeting was fun.

I'm not sure about how to use a filter. I've been researching it some, but I'm not sure what kind of filter I need to use.
I put some of the code I'm trying to use to this post. I thought maybe you could tell me if I'm on the right track or barking up the wrong tree.
I also just found out that I need to filter using a second criteria. I've put a radio button group on my form to use to select the second criteria. This comes from another field in the same table. The options are 20,40, or both.


Private Sub btnReport_Click()
    Dim strBegin As String
    Dim strEnd As String
    Dim strDoc As String
    Dim strWhere As String

    strDoc = "Floor Requests"
If Len(Me.cboStartDate & "") > 0 And Len(Me.cboEndDate & "") > 0 Then
    ' entries in both
    strWhere = strWhere & " AND Req_Date Between #" & Me.cboStartDate & "# AND #" & Me.cboEndDate & "#"
Else
    If Len(Me.cboStartDate & "") > 0 And Len(Me.cboEndDate & "") = 0 Then
    strWhere = strWhere & " AND Req_Date >= #" & Me.cboStartDate & "#"
Else
    If Len(Me.cboStartDate & "") = 0 And Len(Me.cboEndDate & "") > 0 Then
    strWhere = strWhere & " AND Req_Date <= #" & Me.cboStartDate & "#"
End If
If Len(strWhere & "") = 0 Then
    DoCmd.OpenReport strDoc, acViewPreview
Else
      DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=Mid(strWhere, 6)
End If

End Sub


Thanks for all your help

-Dave

Well, I made it through without falling asleep, but I don't know if that was a good thing or a bad thing.

That code is kind of the way I would do it, perhaps if I could offer some changes (it may just be personal preference though)

It's a little cleaner!

Good Luck,

Mike

Mike
EchoVue.com