Query doesn't pull all records

[tbjmobile@gmail.com]

Hello Folks,


I'm stuck again!
I have made 2 queries to place on a main report (Account) as sub reports, one to show work carried out in the current month and one to show booking information (arrivals) in the current month. They both use the same structure and number one works fine. Number two (Bookings) will not pull all the records for the current month, the joining field is the customer code which is a 3 character field in all 3 tables. The work sub report shows a 001 entry but the bookings table not, it only starts with the 002 entries. There is an a record in all three tables for 001 in the current month. Weird.

A maybe related problem is that every now and again, the query for bookings shows an '#error' emessage for the calculated query field

 RetailLinen: Switch([Limpcode]=0,0,[LimpCode]=1,6.5*[ChgWks]*[Pax],[LimpCode]=2,[ChgWks]*[Pax]*11,[LimpCode]=3,[ChgWks]*[Pax]*13,[LimpCode]=4,[ChgWks]*[Pax]*6.5 )

This is also weird because it happens maybe once every 5 to 10 times I run the query.
Thanks for looking.

[ranman256]

did you check to see if query property: TOP VALUES = ALL?
and there are no other criteria to limit? (filters)


A switch has nothing to do with record filtering so that shouldnt affect it.

[tbjmobile@gmail.com]

Thanks for that, the topvalue in query General PROPERTIS is set to all.
The only filters that I have are to only get the records from the current month, which works, namely
Expr1: Year([Arrive]) and in criteria for that - Year(Now()) And Year(Now())
Then
Expr2: Month([Arrive]) and in criteria for that - Month(Now()) And Month(Now())

[isladogs]

Expr1: Year([Arrive]) and in criteria for that - Year(Now()) And Year(Now())
Then
Expr2: Month([Arrive]) and in criteria for that - Month(Now()) And Month(Now())

Why are both of these duplicated? What happens if you remove the second part of each?

[tbjmobile@gmail.com]

Good question, nothing, it was probably me, although I don't know why I would have bothered to tput the 'And' in.

[isladogs]

Good question, nothing, it was probably me, although I don't know why I would have bothered to tput the 'And' in.

You mean it still works?

[tbjmobile@gmail.com]

It still works but the query isn't displaying all of the records. I've put the duplicate expression back in and it's still the same.
There are 10 dummy records in the bookings table, 9 of them with an arrival in February. The query only pulls 6 of those. The 3 that are missing are the three with customer code "001", so I can't move on with placing the query as a sub report onto the main account report.

[isladogs]

Perhaps worth uploading your db for someone to look at.
It won't be me today as I'm currently working on a big project

[tbjmobile@gmail.com]

O.k., thanks ridders. I'll do that tonight if nothing positive happens in the meantime.

[tbjmobile@gmail.com]

Hello All,
This is a zip of my DB so far. There is no real data in it yet they are all dummy entries for testing purposes. I know that nothing is finished and I have to do a lot of work on formatting but I don't want to spend that time until I know that everything works.
My problem is in the Account report. I have 2 sub-reports, both of which I have placed using similar queries. One for work carried out and one for occupation (called Bookings). Even the Booking query doesn't pull up the "001" booking. The rest of the query works and calculates correctly (I think) the data.

Problem #1.
The Account report doesn't show all of the 'Bookings' records for the customer codes but does show some that are in February. I have some VBA code which members have kindly supplied me with to add to calculate the Thursdays in the month and this will become the ChgWks, but for now I have just calculated this to get a number as .

Problem #2.
As you will see, I have managed to get the report footer text box to show the line totals for the Work sub-report and I can get a textbox in the main report to refer to it. I can't seem to repeat that in the Occupation sub-report. I'm using a control source of '=[qryCurrentMonthsBookings subreport].[Report]![TotRetailBkng]' which to me is similar to that of Work. I suspect it is because the query calculated fields have not been transferred to the sub-report but I don't know why. I know I could redefine these in the sub-report but I'm sure that would bring further problems so I need advice on what I've done wrong.

The Bookings sup-report throws up lots of other problems but I've left it in there for now for you to see that. Remove it and have a look at the rest please.

It's probably obvious to you guys but I've been staring at it for about 3 days now.
Thanks for any help you can offer.
Trevor.
Clients.zip

[isladogs]

Thanks
Sorry but I won't have time to look at this tonight - perhaps someone else will be able to do so

[isladogs]

Had a quick look at your test db this morning.
It certainly needs some more work doing before it is a finished product ...

I found a number of issues just on the parts related to the report & subreport - didn't look at anything else

1. Requires the latest version of Access 2016 - presumably due to use of a new datatype such as big int (large number)
Don't do as I did and attempt to do a compact & repair using Access 2010 - it will wipe the data in tblAccount & create a table MSysCompactError!
Lucky I had a copy to go back to!

2. Format error on tblAccount Acc Period - m\on\thn\am"e("m\on\th(d"ate))" REMOVED

3. First subreport - CurrentMonthsWork subreport
Record Source - qryCurrentMonthsWork - errors in criteria not fixed - see earlier post
Year(Now()) And Year(Now()) => changed to Year(Now())
Month(Now()) And Month(Now()) => changed to Month(Now)
This of course limits the results to the current month Feb 2018 so other records from e.g. Jan 2018 are not shown


4. Second subreport - qryCurrentMonthsBookings subreport
Record Source - qryCurrentMonthsBookings
Lots of records in tblBookingsCharges NOT in tblBookings - See qryUnmatchedBookingsCharges
These won't appear in report due to use of INNER JOIN in query
Solution: Add the missing records to tblBookings !!!!

5. Main report - lots of white space in footer - REMOVED
Also too wide for 1 page - REDUCED WIDTH

Now if you go to Print Preview - you will see data is shown in both subreports WHERE IT EXISTS
e.g. Page 3 - data missing ; Pg 4 - data exists for both subreports

On page 3 - #ERROR due to in calculated field with missing data
See below




Updated version attached for you to work on further
Good luck

[tbjmobile@gmail.com]

Thanks Ridders, I really appreciate your help and I'll go through all of your points. Could I have a problem with Access because it is Access 2016 and I only bought it in December of last year, so not even 3 months old yet ?
Trevor.

[isladogs]

If it's just you using your database, there won't be a problem.
However it can't currently be opened by anyone using early versions of A2016 or any versions prior to that.
As not everyone here uses A2016 you are limiting the number of people who can help you.

The cause is one or more fields in the Accounts table using a new datatype.
It's almost certainly a number field using large integer.
Do you really NEED that datatype?
If not, change to e.g. Long integer to solve the issue