Avatar of Jase Alexander
Jase Alexander
Flag 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.

J
EE.accdb
Microsoft Access

Avatar of undefined
Last Comment
Jase Alexander

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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 Alexander

ASKER
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-Giles

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Paul Cook-Giles

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

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 Alexander

ASKER
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-Giles

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.  :)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Paul Cook-Giles

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 Alexander

ASKER
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)

@spicecave

you have to specifically pick the report you want to open

DoCmd.OpenReport "YourReportNAMEHERE"
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jase Alexander

ASKER
Thanks Paul

Really helped me a lot

Also, thanks to Roy as always for the additional help

J