Building Instant Reports By Date

[stpark22]

Hello,

I'm building a tracking tool using Access 2003, where my users will be entering orders that includes quantity of products sold, and cost per unit.

I'm in the process of building several instant reports where they can click on a control without having to specific any criterias such as data ranges and pull up an instant report

What I'm having trouble with is building reports that will compute totals by certain dates (Totals For Last Week, Totals For Last Month) based on the run date.

What is the best way to build a query that will automatically locate a weekly or monthly date range based on the run date
For example: If I run a report today (6/22/2007) for last week's data, I want to write a script to query records between (6/10/2007 - 6/16/2007)

[ADezii]

Hello,

I'm building a tracking tool using Access 2003, where my users will be entering orders that includes quantity of products sold, and cost per unit.

I'm in the process of building several instant reports where they can click on a control without having to specific any criterias such as data ranges and pull up an instant report

What I'm having trouble with is building reports that will compute totals by certain dates (Totals For Last Week, Totals For Last Month) based on the run date.

What is the best way to build a query that will automatically locate a weekly or monthly date range based on the run date
For example: If I run a report today (6/22/2007) for last week's data, I want to write a script to query records between (6/10/2007 - 6/16/2007)

Based on a Run Date of 6/22/2007, the following code will dynamically create a Query based on a ficticious Table which will return all Records between the Range of 6/10/2007 and 6/16/2007 (10 days prior to Run Date to 6 days prior to Run Date). The code makes the hugh assumption that the Run Date is on a Friday and is based on your beginning and ending specifications. Any questions, feel free to ask:

Dim dteRunDate As Date, dteStartDate As Date
Dim dteEndDate As Date, MySQL As String
Dim MyDB As DAO.Database, qdf As DAO.QueryDef
 
Set MyDB = CurrentDb()
 
For Each qdf In MyDB.QueryDefs
  'Create your own Query Name
  If qdf.Name = "Orders For Date Range" Then
    MyDB.QueryDefs.Delete qdf.Name
  End If
Next
 
dteRunDate = #6/22/2007#
dteStartDate = DateAdd("d", -12, dteRunDate)
dteEndDate = DateAdd("d", 6, dteStartDate)
 
'substitute your own Table and Field Name 
MySQL = "Select * From tblTableName Where [Your_Date_Field] Between #" & dteStartDate & "# And #" & dteEndDate & "#;"
 
'if different, must match the Query Name you deleted above
Set qdf = MyDB.CreateQueryDef("Orders For Date Range", MySQL)
 
RefreshDatabaseWindow
 
Set qdf = Nothing

[stpark22]

Thank you for responding. The issues I have with this method is the following: I don't want to hard code the "run date" because this will frequently change. This will affect the parameters passed to the DateAdd function

dteRunDate = #6/22/2007#
dteStartDate = DateAdd("d", -12, dteRunDate)
dteEndDate = DateAdd("d", 6, dteStartDate)

Also, I rather use the ADO library because I'm running this procedures using data with a SQL Server database and I'm looking to convert this into a web app.

[FishVal]

Thank you for responding. The issues I have with this method is the following: I don't want to hard code the "run date" because this will frequently change. This will affect the parameters passed to the DateAdd function

dteRunDate = #6/22/2007#
dteStartDate = DateAdd("d", -12, dteRunDate)
dteEndDate = DateAdd("d", 6, dteStartDate)

Also, I rather use the ADO library because I'm running this procedures using data with a SQL Server database and I'm looking to convert this into a web app.

Try to use the following

dteStartDate = DateAdd("d", -Weekday(dteRunDate) - 7, dteRunDate)
dteEndDate = DateAdd("d", -Weekday(dteRunDate), dteRunDate)
 

[ADezii]

Thank you for responding. The issues I have with this method is the following: I don't want to hard code the "run date" because this will frequently change. This will affect the parameters passed to the DateAdd function

dteRunDate = #6/22/2007#
dteStartDate = DateAdd("d", -12, dteRunDate)
dteEndDate = DateAdd("d", 6, dteStartDate)

Also, I rather use the ADO library because I'm running this procedures using data with a SQL Server database and I'm looking to convert this into a web app.

The issues I have with this method is the following: I don't want to hard code the "run date" because this will frequently change.

I rather use the ADO library because I'm running this procedures using data with a SQL Server database and I'm looking to convert this into a web app

1. The Run Date was hard coded for demo purposes only.
2. Whether you use DAO or ADO would not effect the basic logic, only the syntax.