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.