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.

J
EE.accdb
Jase AlexanderCompliance ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)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] & "#"
0
Jase AlexanderCompliance ManagerAuthor 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
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Learn more
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
attaching db.  :)
EE.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)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
0
Jase AlexanderCompliance ManagerAuthor 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
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
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.  :)
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
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.
0
Jase AlexanderCompliance ManagerAuthor 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
0
Rey Obrero (Capricorn1)Commented:
@spicecave

you have to specifically pick the report you want to open

DoCmd.OpenReport "YourReportNAMEHERE"
1
Jase AlexanderCompliance ManagerAuthor Commented:
Thanks Paul

Really helped me a lot

Also, thanks to Roy as always for the additional help

J
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.