reporting on a date range

[Billy]

I am a real novice at access but have database that runs my business.
I run a report each month to show me the revenue and profit.

However, when I try to run the report for July and for Sept (it works fine for Aug) it says "expression typed incorrectly or too complex...".

In fact I can run the report from the 01/09/05 to the 18/09/05 and it works fine but if I do it to the 19/09/05 and beyond I get the above error.

It also works for the 1/10/05 to current date no problem.

I haven't got a clue what is causing the prob, any help gratefully received.

This is what is on the Form:

=" For Orders Rec'd Between: " & Forms![Report Date Range]![ Beginning Order Date] & " and " & Forms![Report Date Range]![ Ending Order Date]

and this is what is on the report:

>=[Forms]![Report Date Range]![Beginning Order Date] And <=[Forms]![Report Date Range]![Ending Order Date]

 

[AN60]

Do you use two date fields, a start & end date, to view your data?

 

[Billy]

Yes a pop up form asks for the "begin date and end date"
so if for example I want to run the report for a month I just put in 1/8/05 and then 30/8/05 then hit a "preview" button and the report gets generated

 

[AN60]

[MyDateField] Between [Forms]![MyForm]![txtStartDate] And [Forms]![MyForm]![txtEndDate] Or [Forms]![MyForm]![txtStartDate] Is Null


Above is what I use in queries which are used to generate my reports. I place this in the "Field" section (within the query) and then in the Criteria I put True then clear the tick from the box. If the date fields are left blank then the report will show all dates but if a date is in either start or end fields then that is what the query uses. To generate a year to date report I set the default dates in each field to the start of the current year and the current date in the other. This works every time I use it and is nice and simple. To "jazz" it up a bit I use a calendar button for each date field. I really like one calendar which was mentioned by "Old Soft Boss" on this forum not long ago. I've used two calendars he has mentioned and both work well. Hope it helps.

 

[Billy]

problem solved

it was two miss matching records! A record in one table had been deleted but its corresponding record in another table had not.Deleted the errant records and ...it now works fine.