Access Creating a report using a date range

Jase Alexander
Jase Alexander used Ask the Experts™
on
HI Guys

I have attached a database I am currently working on

Ive tried some code I found in a previous source on EE in relation to adding fields and a command button on a form to create and run a report from a particular date range. The original source is :-

DoCmd.OpenReport "YourReport", acViewPreview, , "Full Date>=#" & [StartDateTextBox] & "# AND <=#" & [EndDateTextBox] & "#"


Ive created a form and tried posting the code and linking this to the command button but cannot seem to get it to link to the code?

Am I doing something wrong? Any help or other suggestions would be much appreciated.

J
EE.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Rey Obrero (Capricorn1)
Top Expert 2016

Commented:
try

DoCmd.OpenReport "YourReport", acViewPreview, , "[Full Date] >=#" & [StartDateTextBox] & "# AND [Full Date]<=#" & [EndDateTextBox] & "#"

using Between

 DoCmd.OpenReport "YourReport", acViewPreview, , "[Full Date] Between #" & [StartDateTextBox] & "# AND #" & [EndDateTextBox] & "#"
Jase AlexanderCompliance Manager

Author

Commented:
Hi Roy

Thanks for the reply

Do I just paste this into the standard code window or create a module?

How do I link this to a command button in the form?

Your input is much appreciated as always

J
Paul Cook-GilesSenior Application Developer

Commented:
This should work;  give it a spin.

1.  The field to which your criteria is/are applied has to be in your data source;  in this case, [Full Date] wasn't in the query attached to the report.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Senior Application Developer
Commented:
attaching db.  :)
EE.accdb
Rey Obrero (Capricorn1)
Top Expert 2016

Commented:
place the code in the click event of a button

private sub NameOfButton_click()

DoCmd.OpenReport "YourReport", acViewPreview, , "[Full Date] >=#" & [StartDateTextBox] & "# AND [Full Date]<=#" & [EndDateTextBox] & "#"


end sub
Jase AlexanderCompliance Manager

Author

Commented:
HI Paul

Thank you so much for the update

Just one thing - if I create a report from a query and call it, for example, ChargesByCountry, that have all columns; if I alter the "YourReport" in the code to "ChargesByCountry" will it select this report to run?

J
Paul Cook-GilesSenior Application Developer

Commented:
If the query has the Full Date field in it, the report should open and display whatever data meets the date criteria you entered on the form.  :)
Paul Cook-GilesSenior Application Developer

Commented:
Another note:  your objects (forms, queries, reports, buttons, and so on) should never have spaces in their names;  as you learn more about working in VBA, you'll find that avoiding spaces makes coding much easier.
Jase AlexanderCompliance Manager

Author

Commented:
Hey Paul

Thanks for the advice

Ive just created a report from FullDatawithFullDPDCharges and when I run the date range form to run the report, it still runs the By Country instead of this one?

Is there a way to point the code toward a specific report or will it just pick the first that is available?

J
Rey Obrero (Capricorn1)
Top Expert 2016

Commented:
@spicecave

you have to specifically pick the report you want to open

DoCmd.OpenReport "YourReportNAMEHERE"
Jase AlexanderCompliance Manager

Author

Commented:
Thanks Paul

Really helped me a lot

Also, thanks to Roy as always for the additional help

J

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial