We help IT Professionals succeed at work.

Access Creating a report using a date range

Jase Alexander
on
1,239 Views
Last Modified: 2017-03-24
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

CERTIFIED EXPERT
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
CERTIFIED EXPERT

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.
Senior Application Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT
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