Report based on Date Fields

[Viper210]

I am a complete novice and need help determining how to build this date tracking report:

I have created a form which tracks activities/status of my clients, and on this form, I have 30 unique date fields to keep track of when 30 activities must/will take place.

What I am attempting to do is print a report showing only the clients who have populated activity date fields that fall in between the range of a 1 week time span. (EX: 7/23/2012-7/27/2012)

I know I will need Start and End Date fields for the date range but don’t know if I should put this on a command button or build a query (don’t know how to build either one)

Some of the activity date fields are:

• Prop Sale Date
• Actual Sale Date
• Redemption End Date

It would be helpful if I could perform a print preview prior to printing

Remember if you can assist, please reply in the most elementary terms that you can.

Thank you in advance for your assistance

 

[ypma]

If i am understanding your requirement , you will need to create a querry based on the table where your data is located and add all the fields that you require for your report into this query. Then create a report using the new query as its data source.
As for your filter of dates i would suggest you have a start and end date and name them "StartDate" and "EndDate" on your form which will be the filter for all the variious dates you have assuming you have the same week span for all your dates .

Then Add the criteria you wish to use for your report .by referencing the start and end dates of your form .
Between [Forms]![NameofForm]![StartDate] And [Forms]![NameOfForm]![EndDate]

This criteria would have to be inserted in each of the date fields you wish to view
This way the weekly criteria dates you wish to use can be changed in the form with out amending the query.
There may be other ways to acheive this so other posts may follow.

Regards Ypma

 

[Viper210]

Well following your advice, I decided to create a query on a new form whose sole purpose is to limit my records to a specific date range. I found the following code to achieve this:

Private Sub cmdPreview_Click()Dim strReport As String Dim strDateField As StringDim strWhere As StringDim lngView As LongConst strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptSales" strDateField = "[Prop Sale Date]" lngView = acViewPreview

If IsDate(Me.txtStartDate) ThenstrWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) ThenIf strWhere <> vbNullString ThenstrWhere = strWhere & " AND "End IfstrWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If you look at the strDateField, it only list one of the many fields I have to track. How do I modify the code to list multiple fields?

 

[ypma]

I think i see the bigger picture now, my suggestion is Ok for one critrea but not for two dates or more as second critriea would restrict the criteria of the first date and so on.
i would make a seperate query of each date field you need in your report and enter the critiea in each one, then add these new queries to your originlal query.Once a new query has been added .You will have to join the properties using number two which includes all records from both where the joined fields are equal . Try with one additional date at a time and bulid up you query to show the data you wish to desplay in your report.
I am unable to write a SQL script as my knowledge is not upto scratch to help you .
Hopefully a programmer will take over and assist you in that .