Strange Error with report

[Dazzy]

Hey Guys

Please see the attached sample database.

I am trying to display upcoming events in an outlook style report, I found this formatting code somewhere on this forum.

We have 5 rooms available for bookings, the report loads fine for 4 of these but for whatever reason the Board Room report (Weekly Bookings Board Room) throws up a data mismatch error, and the datasource is exactly the same as the other reports but checking for a different room.

Any help would be appreciated.

 

[pbaldy]

The record source of that report isn't valid (no FROM clause):

SELECT DateAdd("d",-Weekday([EventDate]),[EventDate])+1 AS WeekOf, qryEventWeekBookings.EventDate, qryEventWeekBookings.[Start Time], qryEventWeekBookings.[End Time], qryEventWeekBookings.Company, qryEventWeekBookings.Title, qryEventWeekBookings.Location

 

[GinaWhipp]

It's not *Board Room* that is the problem it's the *Week Of* field that is the problem, it returns nothing so cannot evaluate. Removing the *>=Date()-7* let's the query run.

 

[GinaWhipp]

@pbaldy

You might want to redownload, I got a FROM clause...

SELECT DateAdd("d",-Weekday([EventDate]),[EventDate])+1 AS WeekOf, qryEventWeekBookings.EventDate, qryEventWeekBookings.[Start Time], qryEventWeekBookings.[End Time], qryEventWeekBookings.Company, qryEventWeekBookings.Title, qryEventWeekBookings.Location FROM qryEventWeekBookings WHERE (((DateAdd("d",-Weekday([EventDate]),[EventDate])+1)>=Date()-7) AND ((qryEventWeekBookings.Location)="Board Room"));

 

[Dazzy]

Hi


Thanks for getting back to me, the report I just copied and renamed from the IT Suite 1 report. Removing the date does work but then it shows all bookings and not from the current week onwards.


I don't know what it is about this report but even with the date removed and you cycle though the pages of the report, it becomes distorted and that doesn't happen any of the rest.


Thanks guys, and further help would be great. This one has me stumped.

 

[GinaWhipp]

I don't know what you mean by distorted, please explain exactly what you see...

 

[Dazzy]

Hi


Please see the following screenshot.

Compare it with a similar for IT Suite 1 report

 

[GinaWhipp]

Okay, I got it now. Well, that's going to take more than a few minutes to investigate and it's almost 2:00 am where I am... so tomorrow (or rather today) I will look at. If no one gets here before me!

 

[JHB]

..
We have 5 rooms available for bookings, the report loads fine for 4 of these but for whatever reason the Board Room report (Weekly Bookings Board Room) throws up a data mismatch error, and the datasource is exactly the same as the other reports but checking for a different room.

Any help would be appreciated.

But you've not the same data foundation, it is only events cancelled for "Board Room".
If you remove the two entry in table "tblEventException" the data mismatch error goes away.
For the report "error" part, only for "Board Room" you've more as 1 company.
If you put in a criteria only to show "FRCI" company, the report shows okay!

 

[GinaWhipp]

Thanks JHB! I had not even looked in the table, never made it that far.

 

[Dazzy]

Thanks, I will have to rethink this has event occurrence cancellations have to be possible.