Solved: Query working inconsistently

[icthus123]

I am creating a query dynamically via a form called frmQueryMain. This query lists all the schools in a given area and certain details about their finances. When I put in dates which are exactly a year apart (e.g. between 01/04/2006 and 31/03/2007) and click search I get the report I want listing all the data I want. And before I added all the data which is now in the database I got this whatever two dates I happened to enter.

However, now when I just put in two random dates (i.e. that are not exactly a year apart) I only get two schools on the report.

I'm really confused by this and can't find what the problem is. It's probably something really obvious though and I'd be glad of any help you guys can give me. I've attached the database with all the sensitive data taken out for you to look at.

Thanks.

[DarkSprout]

I tested your MDB on an Access2003 install.

With the Date range of 27/08/2007 - 04/10/2009 (quite random)

I got 14 pages in Landscape View / No error that I can see.

Sorry!

[icthus123]

I tested your MDB on an Access2003 install.

With the Date range of 27/08/2007 - 04/10/2009 (quite random)

I got 14 pages in Landscape View / No error that I can see.

Sorry!

Sorry, try it with "Blackburn and Darwen" selected in the combo box.

[XLGibbs]

There are some minor issues with using Is Null OR a date....

I prefer to assign the query parameters by directly linking the form cbo box itself as opposed to rebuilding the querydef every time.

here is the SQL for the query I would try to use, which assigned the criteria to those places on the FORM itself where they are created.

Paste this SQL into SQL Design view of the query and save it. Open the form, pick your LA and your date range. Don't hit SEARCH, but just leave the form open and run the query manually. You will see what I mean.

If you also replace the event code, with the minimal replacement below, it will be quicker (as a 2nd step to pre-defining the query itself)

SELECT tblLocalAuthority.[LA Number], tblLocalAuthority.[Local Authority], tblSchools.DfES, tblSchools.Pooled, tblSchools.Diocese, tblSchools.[School Number], tblSchools.[School Name], Format(nz([VASISAllocation],0),'?#,##0.00') AS VASIS, Format(nz([VASIS Carried Over],0),'?#,##0.00') AS [VASIS Carried], Format(Sum(nz([DFC Y1],0)),'?#,##0.00') AS TotalDFCY1, Format(Sum(nz([DFC],0)),'?#,##0.00') AS TotalDFC
FROM (((tblLocalAuthority INNER JOIN tblSchools ON tblLocalAuthority.[Local Authority] = tblSchools.[Local Authority]) LEFT JOIN [tblDFC/School] ON tblSchools.[School Number] = [tblDFC/School].School) LEFT JOIN tblProjects ON tblSchools.[School Number] = tblProjects.School) LEFT JOIN tblInvoice ON tblSchools.[School Number] = tblInvoice.School
WHERE (((tblInvoice.Date) Is Null Or (tblInvoice.Date) Between [Forms]![frmQueryMain]![cboStartDate] And [Forms]![frmQueryMain]![cboEndDate]) AND ((tblProjects.Date) Is Null Or (tblProjects.Date) Between [Forms]![frmQueryMain]![cboStartDate] And [Forms]![frmQueryMain]![cboEndDate]))
GROUP BY tblLocalAuthority.[LA Number], tblLocalAuthority.[Local Authority], tblSchools.DfES, tblSchools.Pooled, tblSchools.Diocese, tblSchools.[School Number], tblSchools.[School Name], Format(nz([VASISAllocation],0),'?#,##0.00'), Format(nz([VASIS Carried Over],0),'?#,##0.00'), [tblDFC/School].[Year Start], [tblDFC/School].[Year End]
HAVING (((tblLocalAuthority.[LA Number])=[Forms]![frmQueryMain]![cboLA]) AND (([tblDFC/School].[Year Start]) Is Null Or ([tblDFC/School].[Year Start])=[Forms]![frmQueryMain]![cboStartDate]) AND (([tblDFC/School].[Year End]) Is Null Or ([tblDFC/School].[Year End])=[Forms]![frmQueryMain]![cboEndDate]));


If you change the query to that (paste the SQL in SQL Design mode of the query)


you can use this event code:

Private Sub cmdSearch_Click()

DoCmd.OpenReport "rptMain", acViewPreview

DoCmd.Maximize

End Sub
And whichever parameters are applied via the cbo boxes or calendar control will be applied.

Additional checks might be needed to default selections to be sure a date on each end is picked.

Not sure why you would have date criteria with an Is Null OR operator attached either, as if there is NO date, it defeats the purpose of filter for it in the first place...

[icthus123]

There are some minor issues with using Is Null OR a date....


right, how do i put the issues right? :dunno

thanks for you advice about the other by the way. i'll do that. i didn't before because whenever i've done this before i've had to have different possible SQL strings, so i had to create it dynamically. and i just did it the same way i'd done it before. but i guess your right in the context it just slows things down.

[XLGibbs]

I would test on queries in design view to determine which combination gives you the results you want.

Your initial query is set to pull dates between a range, or where either of them are null.

It is hard to tell the results you are actually getting at here. You have start and end dates by school, and invoice dates and project dates.

What would be a good result for the query? I can try and get the desired results using your data and post the query back like before.

[XLGibbs]

SELECT tblLocalAuthority.[LA Number], tblLocalAuthority.[Local Authority], tblSchools.DfES, tblSchools.Pooled, tblSchools.Diocese, tblSchools.[School Number], tblSchools.[School Name], Format(nz([VASISAllocation],0),'?#,##0.00') AS VASIS, Format(nz([VASIS Carried Over],0),'?#,##0.00') AS [VASIS Carried], Format(Sum(nz([DFC Y1],0)),'?#,##0.00') AS TotalDFCY1, Format(Sum(nz([DFC],0)),'?#,##0.00') AS TotalDFC
FROM (((tblLocalAuthority LEFT JOIN tblSchools ON tblLocalAuthority.[Local Authority] = tblSchools.[Local Authority]) LEFT JOIN [tblDFC/School] ON tblSchools.[School Number] = [tblDFC/School].School) LEFT JOIN tblProjects ON tblSchools.[School Number] = tblProjects.School) LEFT JOIN tblInvoice ON tblSchools.[School Number] = tblInvoice.School
WHERE (((tblInvoice.Date) Is Null Or (tblInvoice.Date) Between [Forms]![frmQueryMain]![cboStartDate] And [Forms]![frmQueryMain]![cboEndDate]) AND ((tblProjects.Date) Is Null Or (tblProjects.Date) Between [Forms]![frmQueryMain]![cboStartDate] And [Forms]![frmQueryMain]![cboEndDate]))
GROUP BY tblLocalAuthority.[LA Number], tblLocalAuthority.[Local Authority], tblSchools.DfES, tblSchools.Pooled, tblSchools.Diocese, tblSchools.[School Number], tblSchools.[School Name], Format(nz([VASISAllocation],0),'?#,##0.00'), Format(nz([VASIS Carried Over],0),'?#,##0.00'), [tblDFC/School].[Year Start], [tblDFC/School].[Year End]
HAVING (((tblLocalAuthority.[LA Number])=[Forms]![frmQueryMain]![cboLA]) AND (([tblDFC/School].[Year Start]) Is Null Or ([tblDFC/School].[Year Start])=Year([Forms]![frmQueryMain]![cboStartDate])) AND (([tblDFC/School].[Year End]) Is Null Or ([tblDFC/School].[Year End])=Year([Forms]![frmQueryMain]![cboEndDate])));

Try that query. I noticed that the way it was written in both your original, and my parameter based query was comparing YearStart and YearEnd to the full date (thus no matches there...)

The revised above pulls the Year(EndDate) and Year(StartDate) from the form itself based on what is entered.

[icthus123]

SELECT tblLocalAuthority.[LA Number], tblLocalAuthority.[Local Authority], tblSchools.DfES, tblSchools.Pooled, tblSchools.Diocese, tblSchools.[School Number], tblSchools.[School Name], Format(nz([VASISAllocation],0),'?#,##0.00') AS VASIS, Format(nz([VASIS Carried Over],0),'?#,##0.00') AS [VASIS Carried], Format(Sum(nz([DFC Y1],0)),'?#,##0.00') AS TotalDFCY1, Format(Sum(nz([DFC],0)),'?#,##0.00') AS TotalDFC
FROM (((tblLocalAuthority LEFT JOIN tblSchools ON tblLocalAuthority.[Local Authority] = tblSchools.[Local Authority]) LEFT JOIN [tblDFC/School] ON tblSchools.[School Number] = [tblDFC/School].School) LEFT JOIN tblProjects ON tblSchools.[School Number] = tblProjects.School) LEFT JOIN tblInvoice ON tblSchools.[School Number] = tblInvoice.School
WHERE (((tblInvoice.Date) Is Null Or (tblInvoice.Date) Between [Forms]![frmQueryMain]![cboStartDate] And [Forms]![frmQueryMain]![cboEndDate]) AND ((tblProjects.Date) Is Null Or (tblProjects.Date) Between [Forms]![frmQueryMain]![cboStartDate] And [Forms]![frmQueryMain]![cboEndDate]))
GROUP BY tblLocalAuthority.[LA Number], tblLocalAuthority.[Local Authority], tblSchools.DfES, tblSchools.Pooled, tblSchools.Diocese, tblSchools.[School Number], tblSchools.[School Name], Format(nz([VASISAllocation],0),'?#,##0.00'), Format(nz([VASIS Carried Over],0),'?#,##0.00'), [tblDFC/School].[Year Start], [tblDFC/School].[Year End]
HAVING (((tblLocalAuthority.[LA Number])=[Forms]![frmQueryMain]![cboLA]) AND (([tblDFC/School].[Year Start]) Is Null Or ([tblDFC/School].[Year Start])=Year([Forms]![frmQueryMain]![cboStartDate])) AND (([tblDFC/School].[Year End]) Is Null Or ([tblDFC/School].[Year End])=Year([Forms]![frmQueryMain]![cboEndDate])));

Try that query. I noticed that the way it was written in both your original, and my parameter based query was comparing YearStart and YearEnd to the full date (thus no matches there...)

The revised above pulls the Year(EndDate) and Year(StartDate) from the form itself based on what is entered.

I've used this:


SELECT [tblLocalAuthority].[LA Number], [tblLocalAuthority].[Local Authority], [tblSchools].[DfES], [tblSchools].[Pooled], [tblSchools].[Diocese], [tblSchools].[School Number], [tblSchools].[School Name], Format(nz([VASISAllocation],0),'?#,##0.00') AS VASIS, Format(nz([VASIS Carried Over],0),'?#,##0.00') AS [VASIS Carried], Format(Sum(nz([DFC Y1],0)),'?#,##0.00') AS TotalDFCY1, Format(Sum(nz([DFC],0)),'?#,##0.00') AS TotalDFC
FROM (((tblLocalAuthority INNER JOIN tblSchools ON [tblLocalAuthority].[Local Authority]=[tblSchools].[Local Authority]) LEFT JOIN tblInvoice ON [tblSchools].[School Number]=[tblInvoice].[School]) LEFT JOIN tblProjects ON [tblSchools].[School Number]=[tblProjects].[School]) LEFT JOIN [tblDFC/School] ON [tblSchools].[School Number]=[tblDFC/School].[School]
WHERE (((Year([Year Start])) Is Null Or (Year([Year Start]))=Year([Forms]![frmQueryMain]![cboStartDate])) AND ((Year([Year End])) Is Null Or (Year([Year End]))=Year([Forms]![frmQueryMain]![cboEndDate]))) AND((([tblInvoice].[Date]) Is Null Or ([tblInvoice].[Date]) Between [Forms]![frmQueryMain]![cboStartDate] And [Forms]![frmQueryMain]![cboEndDate]) And (([tblProjects].[Date Submitted]) Is Null Or ([tblProjects].[Date Submitted]) Between [Forms]![frmQueryMain]![cboStartDate] And [Forms]![frmQueryMain]![cboEndDate]))
GROUP BY [tblLocalAuthority].[LA Number], [tblLocalAuthority].[Local Authority], [tblSchools].[DfES], [tblSchools].[Pooled], [tblSchools].[Diocese], [tblSchools].[School Number], [tblSchools].[School Name], Format(nz([VASISAllocation],0),'?#,##0.00'), Format(nz([VASIS Carried Over],0),'?#,##0.00')
HAVING (((tblLocalAuthority.[LA Number])=[Forms]![frmQueryMain]![cboEndDate]));


Which seems to work okay. Thanks.