filter report based on dates

MAS
MAS used Ask the Experts™
on
I followed the below link to filter the report based on dates selected in the textbox
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28050231.html

It is not getting filtered it is showing the entire report. below is my code.

Textbox names
Shipment_Date_From
Shipment_Date_To
Field name in table is "Shipment_Date"

DoCmd.OpenReport "ShipmentReport_datewise", acViewPreview, "", "([Shipment_date]>=#" & Format(Forms!Reporting!Shipment_Date_From, "dd-mm-yyyy") & "#) And ([Shipment_date]<=#" & Format(Forms!Reporting!Shipment_Date_To, "dd-mmm-yyyy") & "# )"


Can anyone help to sort out this
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
what data type are the fields Shipment_Date ? text of date?

anyhow, try the US format:
DoCmd.OpenReport "ShipmentReport_datewise", acViewPreview, "", "([Shipment_date]>=#" & Format(Forms!Reporting!Shipment_Date_From, "mm/dd/yyyy") & "#) And ([Shipment_date]<=#" & Format(Forms!Reporting!Shipment_Date_To, "mm/dd/yyyy") & "# )" 

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
the above presumes the field is date.
if it's text, with the format dd-mm-yyyy you cannot use >= and <= directly ...
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
Try this:
DoCmd.OpenReport "ShipmentReport_datewise", acViewPreview, "", "Format([Shipment_date], "yyyy-mm-dd")>='" & Format(Forms!Reporting!Shipment_Date_From, "yyyy-mm-dd") & "') And ([Shipment_date]<='" & Format(Forms!Reporting!Shipment_Date_To, "yyyy-mm-dd") & "' )"

Open in new window

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!

MASEE Solution Guide - Technical Dept Head
Most Valuable Expert 2017

Author

Commented:
Thanks it worked with mm-dd-yyyy
 I appreciate if you can guide how to make it landscape.
As of now it is opening in portrait
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
you have 2 options: define the report once and for all to be landscape (open the report, set the setting in the page setup options, save), or you can do it by code:

DoCmd.OpenReport "ShipmentReport_datewise", acViewDesign
Reports("ShipmentReport_datewise").Printer.Orientation = acPRORLandscape

and then you call it with avViewpreview like above
MASEE Solution Guide - Technical Dept Head
Most Valuable Expert 2017

Author

Commented:
Many thanks

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