Sorting/Viewing by a specific Date

[dragonmanUSMC]

I have a report based off of a query that shows all of the sales orders that have been processed into our system.

What I would like to do is view the orders in my report by date and to only show the date I am intrested in. Right now I am using the 'BETWEEN'[beginningdate] 'AND' [enddate] criteria in my query and that works well but I have to put an end date in and now the report wants to show me all the orders from (ex. 01/06/06 -- 01/07/06). When I try and type in 01/06/06 -- 01/06/06 my report shows error.

I would greatly appreciate any assitance you may offer in helping me resolve this problem.

Thankyou for your time

 

[THWatson]

dragonman72
I would suggest selecting your dates from a form.

1. Create an unbound form. Let's call it frmDateSelect
2. On the form put two unbound text boxes. Let's call them txtStartDate and txtEndDate
3. On the form put a command button to run the report
4. In the query, in the date column, put Between Forms!frmDateSelect!txtStartDate and Forms!frmDateSelect!txtEndDate

You should be able to enter 1/06/06 in both text boxes in the form and it should show only records from that date.

In the event that you want to select more than a single date, then you can use Sorting and Grouping in the report to order the records by date.

Tom

 

[dragonmanUSMC]

Tom thank you for the reply I will try that out and let you know if it works.

Kenn

 

[dragonmanUSMC]

Tom,
I am still having problems getting this to work properly, I have followed each of your recommendation, however when the report open it does not initialize the form frmDateSelect. I put the following in the on open event of the report

On Open: docmd.openform "frmDateSelect",,,,,acDialog.

Any further recommendations would be greatly appreciated

Thanks in advance
Kenn

 

[THWatson]

Kenn
Sorry...I have been out for a few hours and just picked this up.

1. The record source for the report should be the query.
2. Don't put the code on the Open event for the report.

On the form, you have a command button to run the report. Behind the command button include this...
DoCmd.OpenReport "YourReport", acViewPreview

Then, when the report runs it will produce records based on the dates selected in the text boxes on the form.

Tom

 

[dragonmanUSMC]

Tom thank you for all your assistance, I have applied your latest recommendations and it seems to function almost were I need it to be. I say almost because of two issues.

1. If I enter in a start and end date that covers a two week period the orders are not sorting in ascending order by order date the order dates are mixed up through out. In the query order the sort option I selected "ascending"..??

2. If I enter in 1/5/06 as the start and end date it is only showing me 3 of 11 orders from 1/5/06. Although this is an improvement because the old way I couldn't see anything I was wondering what other advice you could offer.

Thank you in advance for any help

 

[THWatson]

Kenn
Regarding your two issues...

1. In order to have the records show in the proper order in the report, you have to set Sorting and Grouping in the report itself. Setting the sort order in the query won't always end up in the records showing in the correct order in the report.
In the Design view for the report, click on the View tab and then Sorting and Grouping. Put the OrderDate field as the first level of sorting, and then put the OrderNbr field as the second level.

2. As for why only 3 of 11 records are showing, I'm not sure what is happening there, because all records should show. Is there more than one table involved?
Perhaps you could post your SQL behind the query, and I could see if I can spot anything there.

Tom

 

[dragonmanUSMC]

Tom thanks for the reply the sorting issue is resolved...thank you. Here the sql for my query, and there are two tables involved here Orders and Order Detail here it is:

SELECT Orders.OrderID, Orders.JobOrderNO, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.ShipDate, Orders.PurchaseOrderNumber, Orders.[Invoice No], Orders.FreightCharge, Orders.SalesTaxRate, Orders.FuelSurChargePerc, Orders.CarrierFuelSurCharge, Orders.CCProcFee, Orders.CCProcAmount, Orders.Ins, Orders.InsAmount, [Order Details].[Qty Shipped], [Order Details].BasePrice, [Order Details].UnitPrice, [Order Details].SalePrice, [Order Details].Discount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate) Between [Forms]![frmDateSelect]![txtStartDate] And [Forms]![frmDateSelect]![txtEndDate]))
ORDER BY Orders.OrderDate;

 

[THWatson]

Kenn
Well, I can't spot anything there in the SQL.

Do all 11 records show in the query?
If they do, then the same 11 records should show up in the report.
If all 11 don't show in the query, then perhaps you need to twiddle with the Join in the query. Change it from showing only records where the field in Orders and OrderDetails are equal, to "show all records in Orders"

Tom

 

[dragonmanUSMC]

When I prompt the report from the query itself It returns the same 3 records. I will fiddle with teh Join and see if I can't come up with anything. One other thing, after I have entered the start and end dates in the frmDateSelect form, I click on my preview button and the report opens but the frmDateSelect is still on top of the report and when I exit "X" the form frmDateSelect my switchboard menu pops up. More of a inconvenience factor than a functional factor. Is there a snippet I could put into to automatically close the frmDateSelect after the preview command button is clicked?

Thanks for all your help

 

[THWatson]

Kenn
Yes, if only 3 records show in the query, something needs to be changed in the Join.

As for the Form staying on top after you click the Preview command button, make sure that Modal is set to No in the form's design.

You don't want to close the Form until after you Print the report. Say that you close the form upon Preview, the report will show properly but when you tell it to Print you will get nothing...because the date parameters will be gone. The form has to stay open because the text boxes provide the parameters.

Tom

 

[dragonmanUSMC]

I changed the Modal to 'NO' I'm going to continue working on the last issue with missing orders on the report. When I figure it out I will be sure to post my solution so that others might benefit from my struggles. I greatly appreciate your help Tom have a good day!

Kenn