Dates/Name breakdown

I have a requested date field which is a date, I need to create a form
with start requested date and end requested date with who it was sent
to......

So I need 2 date boxes in a popup form with these dates in, then press
a button to run a report with these dates in the report.... with a
selection of 10 different staff combo box...

when the dates have been selected and the persons name has been
selected then this report can be run, therefore the report must have a
criteria of the 2 dates and the person's name.

Please help as I have tried several ideas but I know that they are not
the right ones.

Elaine

Re: Dates/Name breakdown

On Nov 16, 9:57 am, Elainie wrote:
> I have a requested date field which is a date, I need to create a form
> with start requested date and end requested date with who it was sent
> to......
>
> So I need 2 date boxes in a popup form with these dates in, then press
> a button to run a report with these dates in the report.... with a
> selection of 10 different staff combo box...
>
> when the dates have been selected and the persons name has been
> selected then this report can be run, therefore the report must have a
> criteria of the 2 dates and the person's name.
>
> Please help as I have tried several ideas but I know that they are not
> the right ones.
>
> Elaine

This is actually fairly easy. It the click event of the button, you
would fill a temporary table with a custom INSERT INTO query, and then
call the report that would be made off of that table. For example:

dim sSQL as string
dim db as dao.database
set db=currentsdb()
db.execute "DELETE * FROM tblReportSales"
sSQL="INSERT INTO tblReportSales SELECT [Name],[InvoiceNo],
[InvoiceDate],[Total] FROM tblCustomer INNER JOIN tblSales ON
tblCustomer.[CustomerNo]=tblSales.[CustomerNo] WHERE [FromDate] >= #"
& txtFromDate & "# AND [ToDate] <= #" & txtToDate & "# AND tblSales.
[CustomerNo] = " & cmbCustomer & ";"
db.execute sSQL
docmd.OpenReport "Sales"

This code assumes a customer table and a separate sales table where
sales are associated to customers with a numeric customer id. It also
assumes the table tblReportSales already exists with the proper field
names and structures. It also assumes that the combo-box cmbCustomer
returns the customer id number.

Re: Dates/Name breakdown

Create a pop-up form (frmFilterReport) with 3 unbound controls. One text
box (txtStart and txtEnd) for each date and one combo box (cmbStaff) for the
recipient. Add a command button to print your report.

Create a query that contains the tables/fields needed for the report .
Set the [request date] criteria to: Between frmFilterReport!txtStart and
frmFilterReport!txtEnd.
Set the [staff] criteria to:
Like(IIF(frmFilterReport!cmbStaff=null,"*",frmFilterReport!c mbStaff))

Set the record source of your report to the above query.
You probably should set a default date range for your filter form so that
when the staff filter is empty, all records are returned.

-Ed





"Elainie" wrote in message
news:8f85ae42-f213-4895-840c-9448c193d3a2@d50g2000hsf.google groups.com...
>I have a requested date field which is a date, I need to create a form
> with start requested date and end requested date with who it was sent
> to......
>
> So I need 2 date boxes in a popup form with these dates in, then press
> a button to run a report with these dates in the report.... with a
> selection of 10 different staff combo box...
>
> when the dates have been selected and the persons name has been
> selected then this report can be run, therefore the report must have a
> criteria of the 2 dates and the person's name.
>
> Please help as I have tried several ideas but I know that they are not
> the right ones.
>
> Elaine