Creating a Form Where User Inputs Start and End Dates of a Report

Creating a form where user inputs start and end dates of a report

There are a few ways to perform this.

The easiest, and most straight forward, in my personal opinion, involve creating a form where the user(s) will be entering Start/End dates, using a query that captures all of the information necessary for your report (including your date parameters), and the report with your query as the recordsource.

Form

Create a form with 2 text box controls: Name them whatever you like (StartDateTxt, EndDateTxt, for example), and a button control.

Query

Now, create a query, that retrieves all of the correct information you need for this report. I know you said selling price, for starters.

For the date fields you want to narrow down your search by, you need to put them in the WHERE clause or the Criteria field in QBE. It might look something like:

[Forms]![YourFormName]![StartDateTxt] and [Forms]![YourFormName]![EndDateTxt]

Note: Referencing fields can sometimes be tricky.

Report

Next, you need to create a report. If you're new to this, you can use the Report Wizard located at: Create - Reports section - Report Wizard.

You can select your query from the dropdown list on the first prompt, and add the fields you desire/need. Click next: Here you can sort your order of appearance on the report. Click next: Then you can justify your layout - if you aren't familiar with the differences, you can play around with them. Click next: Select a 'theme' to be applied to your report (font/colors, etc). Click next: Then you can either preview the report or Modify the report's design. (Really, you can do either, by clicking either button and clicking print preview or design view.)

If you click on Preview the report - Access should ask you for any parameters your underlying query requires.

Back to the form.

Right click on your button control. -> Properties -> Event -> OnClick -> click the [...] icon to the very right of the row. Select code builder.

VBA

In-between Private Sub and End Sub, you're going to write

DoCmd.OpenReport "YourReportName"

You can also check to make sure your report isn't already open.

Dim ReportName As String
ReportName = "YourReportName"

            If CurrentProject.AllReports(ReportName).IsLoaded Then
                DoCmd.Close acReport, ReportName
            End If

            DoCmd.OpenReport ReportName, acViewPreview

I hope this helps you/answers your question.

Edit: If anyone wants to edit/add on to this either by means of clarification or another alternative approach, please feel free. Just let me know.

How do I create a form that submits two dates and outputs the report?

1) Make sure you get a basic HTML version up first
2) Then transfer this to Ajax.

This flow gets everyone on board and keeps logic organized.

All of your logic is correct so I will just give you the code for the HTML version and once you get that up you can get the remote version going.

controller:

 def index
       @start_date = Date.strptime(params[:start_date],"%d/%m/%Y")
       @end_date = Date.strptime(params[:end_date],"%d/%m/%Y")
       @stuffs = Stuff.find(:all, :conditions => ["created_at > ? and updated_at < ?",@start_date, @end_date])
      responds_to do |format|
          format.html #view needs to be the same name as the method
      end 
 end 

view

<% form_tag(stuffs_path, :method => :get) do %>
  <p>
    <label>Start Date</label> <%= datetime_select :start_date, params[:start_date] %> |
     <label>End Date</label> <%= datetime_select :end_date, params[:end_date] %>
  </p>
  <p><%= submit_tag "Get Stats", :disable_with => "Getting stats..." %></p>
<% end %>

routes.rb

you need a line at the top like this:

map.resources :stuffs

Basically what this does is create specific routes.
Now you can call stuffs_path and it will go to the index action.

If you are not using REST the search_stuff_path could be :controller => :your_controller => :action => your_action. IF you are using REST you need to add this as a member to your routes.rb in config. If you need more info let me know.

BTW - I used stuffs to be a generic term for your resource. I think what you want would be report so the controller, routes, and views would be reports_path and map.resources :reports respectively.

So now that you got the HTML version going it's easy to get remote going.

controller

def index
    responds_to do |format|
        format.js render :partial => "remote_report"
        # you can use RJS or a partial. 
        # If it's one place you want to update just use a partial.
        # Create a file _remote_report.html.erb as a partial.
        # Put this in the views folder of course
    end 
end 

In your view:

 <div id="div_id_to_update">
 <% remote_form_tag(stuffs_path, :method => :get, :update => "div_id_to_update") do %>
   <p>
     your form methods here
   </p>
   <p>your submit tag here</p>
 <% end %>
 </div>

Those are the parts you need to change to make it remote based.

MS Access 2010 input date once for multiple sub reports

Create a form with fields for the start and end date, and a button on it, which launches your main form. Use the 'Embedded Macro' Wizard to open the chosen Report On Click.

Then, use [Forms]![FORM NAME]![FIELD NAME] in the query to access the date, for example:

SELECT referrals.origin_country, Count(*) AS ['number']
FROM referrals
WHERE (((referrals.referral_date) Between [Forms]![EAL Referral Search Form]![dat_termly_report_start] And [Forms]![EAL Referral Search Form]![dat_termly_report_end]))
GROUP BY referrals.origin_country;

Form Where User Enters Multiple Fields to Run a Report, Leave Blank to Show All

If the user selects a value other than 'All Types', you want the rows which match that value.

But if the user selects 'All Types' or leaves the combo blank, you don't want any filtering based on DepositType.

You can write those conditions into your query's WHERE clause ...

WHERE
    [put your date selection criteria here]
    AND
    (
           DepositType = Forms!TypeAndDateSelectDeposit!Type
        OR Forms!TypeAndDateSelectDeposit!Type = 'All Types'
        OR Forms!TypeAndDateSelectDeposit!Type Is Null
    )

---

Related Topics