Link to home
Start Free TrialLog in
Avatar of Jase Alexander
Jase AlexanderFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access Creating a report using a date range

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.

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image


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

using Between

 DoCmd.OpenReport "YourReport", acViewPreview, , "[Full Date] Between #" & [StartDateTextBox] & "# AND #" & [EndDateTextBox] & "#"
Avatar of Jase Alexander


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

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.
Avatar of Paul Cook-Giles
Paul Cook-Giles
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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?

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.  :)
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.
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?


you have to specifically pick the report you want to open

DoCmd.OpenReport "YourReportNAMEHERE"
Thanks Paul

Really helped me a lot

Also, thanks to Roy as always for the additional help