By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

Dates/Name breakdown

P: n/a
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
Nov 16 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Nov 16, 9:57 am, Elainie <Elaine.Macint...@bsc.wales.nhs.ukwrote:
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.
Nov 16 '07 #2

P: n/a
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,"*",frmFilt erReport!cmbStaff))

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" <El**************@bsc.wales.nhs.ukwrote in message
news:8f**********************************@d50g2000 hsf.googlegroups.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

Nov 17 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.