Show only moved courses

[chrisb1981]

I have a report that is date driven and shows when people have booked onto a course. This works great but now my boss wants something different.

What he wants is a report that will show what has moved from which month and to where to track sales figure changes.

E.g Rob books on a course in february then moves to may, bill has also booked on the course in february but not moved.

What i need the report to do is when the dates for february are entered as the search criteria Rob will show up because he moved courses but bill will not be shown as he never moved.

The field for the original date is called invoiceDate and the other field is called MoveDate

What I have so far for the working report is

SELECT SalesForecast.CompanyName, SalesForecast.CourseName, SalesForecast.TotalGross, SalesForecast.TotalDiscount, SalesForecast.TotalNet, SalesForecast.SaesOrder, SalesForecast.InvoiceNumber, SalesForecast.InvoiceDate, SalesForecast.MovedDate, SalesForecast.MovedDate2, SalesForecast.MovedDate3, SalesForecast.Nominal
FROM SalesForecast
WHERE (((SalesForecast.InvoiceDate)>=[forms]![Original Date]![Completion Start Date] And (SalesForecast.InvoiceDate)<=[forms]![Original Date]![Completion End Date]) AND ((SalesForecast.Nominal)>=[forms]![Original Date]![Start No] And (SalesForecast.Nominal)<=[forms]![Original Date]![End No]))
GROUP BY SalesForecast.CompanyName, SalesForecast.CourseName, SalesForecast.TotalGross, SalesForecast.TotalDiscount, SalesForecast.TotalNet, SalesForecast.SaesOrder, SalesForecast.InvoiceNumber, SalesForecast.InvoiceDate, SalesForecast.MovedDate, SalesForecast.MovedDate2, SalesForecast.MovedDate3, SalesForecast.Nominal;

I now i need to alter the WHERE clause but don't know what needs adding

 

[edtab]

try adding another condition:

And Not IsNull(SalesForecast.MoveDate)

should pick up only those records with values entered in the field
MoveDate.

 

[chrisb1981]

Thanks edtab it worked great!! I just couldn't think what needed to be added