Form to create reports

[Miten]

My data has dates in it and i want to make a form so that the user can input a date and all the invoices after that date are shown in a print preview report form. Im not sure what kind of box i should use and the code to assign to the button.

Thanks in advance.

 

[Wiz47]

My data has dates in it and i want to make a form so that the user can input a date and all the invoices after that date are shown in a print preview report form. Im not sure what kind of box i should use and the code to assign to the button.

Thanks in advance.

Create a query for the record source of the report that looks something like:

SELECT YourTableName.*

FROM YourTableName

WHERE [YourInvoiceDateField] > Forms!YourFormName!YourForm_UnboundFieldFor_EndDate

ORDER By (Whatever order you want the data to appear in)

Then just call the report when the button is clicked. (You can use the command button wizard to help you with that)

 

[Miten]

Ok what i did was create a query for the table with the dates in it, i then created a form with a text box and a button, the button opens a report made from the query, and i put that code you made in the text box using the expression builder and code builder but neither worked even when i replaced the names of the tables etc.

If you could be a bit more specific in the code you posted where and what i should replace with what i may get a better idea of whats going on.

Thanks again.

 

[Wiz47]

Ok what i did was create a query for the table with the dates in it, i then created a form with a text box and a button, the button opens a report made from the query, and i put that code you made in the text box using the expression builder and code builder but neither worked even when i replaced the names of the tables etc.

If you could be a bit more specific in the code you posted where and what i should replace with what i may get a better idea of whats going on.

Thanks again.

The query should have one field that is the name of the invoice date from your table and a second field that is the name of the ending date. That is an unbound field (not in the table, but the name of the textbox from your form)

The textbox that holds the ending date should be UNBOUND (no control source). The only thing that should be there is the name you gave it under the OTHER tab. If you did it right, when in design mode - it should say Unbound in the box on the form.

Below is what the query should look like now. It should *not* be in the textbox, but in the report as the Record Source.

SELECT YourTableName.*

FROM YourTableName

WHERE (YourTableName.YourInvoiceDate > Forms!YourFormName!The name you gave that unbound textbox)

To run the report from the button:

Go to design view for the form
Doubleclick on the report button you made (it should open a window)
Click on the EVENT Tab
Click on the On Click option (then click on the three dots) elipsis
Click on Code Builder
Enter this code in:
DoCmd.OpenReport "The_Name_of_Your_Report", acViewPreview

If you want to upload a stripped down version of your db, I'll be more than happy to take a look at it and get it running right for you - if you are still having problems.