Report Problem 1

[klcam]

Curious if anyone can offer help on an access problem I’m having. I have a rather large database that involves actions within a 7 state area. I have a form that works well to input the information into the table. My queries produce the desired results. My problem is my report. I have a report based on a query. Frequently, I need to count the number of actions by individual state within a given date range. By using the from[] and to[] in the date criteria of my query I get the desired results. In my report I have created a =Dcount expression in the control source. This expression works well when I do not use the from[] and to[] in the date criteria of my query. When I use this criteria in my query, I get the following output, #Error. Really would appreciate any ideas to solve my problem.

 

[THWatson]

klcam
Here's one approach to consider.

Create an unbound form with 3 controls. Let's call the form frmDateSelector. Let's call the 3 controls txtStartDate, txtEndDate and txtState.

In your query that populates the report, in the date column, put the following as criteria...
Between Forms!frmDateSelector!txtStartDate and Forms!frmDateSelector!txtEndDate

In your query, in the State column, put the following as criteria...
Like [Forms]![frmDateSelector]![txtState] & "*"
This would allow you to input an individual state or leave the txtState box blank to select all states.

Now, in your report, put a text box in the report footer to count the records in that report. Assuming that your one field is Action (change this to whatever is the correct name for your field you want to count)...
=Count([Action])

Tom

 

[THWatson]

I should have added that you can put command buttons on your form, to Preview and/or Print.

Tom

 

[THWatson]

klcam
You may wish to put something such as the following on the No Data event for your report, so that a message occurs when there is no data for the dates selected.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for the time period selected.", vbOKOnly, "No Data"
Cancel = True
End Sub

Tom