Financial Year Query Help

[Kirsti]

Hi,



I have a query where the criteria is based on an unbound form which holds the dates for the current Financial Year. It then also takes data for <Month(Now()). I noticed that even though the dates in frmFinancialYear are from 01/07/2011 to 30/06/2012, the data for 2011 is not coming through. If I take out the <Month(Now()) criteria the query works as expected showing data for 2011 and 2012.

Then to test, I tried Month(Now())-6, and this did not bring through December 2011 data either.

Does anyone have any idea why this would be?

[June7]

Review this for possible cause http://allenbrowne.com/ser-36.html

[Kirsti]

Thanks June,

I have looked though, and in my tables and forms, I have all date fields set to 'Medium Date' to try to avoid that issue.

I still haven't managed to figure it out, so any other suggestions greatly appreciated.

Kirsti

[June7]

The conflict with international dates is the only thing I could think of. It came to mind because you show the dates in the day/mon/year arrangement in your post.

Do you want to provide file for analysis? Follow instructions at bottom of my post.

[Kirsti]

Thanks June, database attached.

Kirsti

[June7]

I removed expression from ControlSource for each date textbox so I could select from the date picker. The queries work as designed.

December 2011 did not return because Month(Now())-6 = 0 (assuming Now is June). 12 is not less than 0. Month(Now())-7 = -1. See what is happening?

Change the Month expressions to:
Year([tblMediation_Tenancy_Tribunal_Activity_ALLDATA.Dat e_Application_Lodged) & "/" & Format(Month([tblMediation_Tenancy_Tribunal_Activity_ALLDATA.Dat e_Application_Lodged]),"00")

Year(Now()) & "/" & Format(Month(Now()),"00")

If you want to subtract months:
Year(DateAdd("m",-6,Now())) & "/" & Format(Month(DateAdd("m",-6,Now())),"00")

[Kirsti]

Thanks June,

I am trying to use your syntax but getting an error: "The expression you entered contains invalid syntax, or you need to enclose your text data in quotes".

It seems to thing the "00" at the end is text?

Are you able to post back the DB I posted that you tested with?

Thanks, Kirsti

[Kirsti]

Also, yes, what you say about 12 not being less than 0 makes perfect sense. Thanks.

[June7]

Here is the working SQL statement. Paste it into SQL View of query designer (fix the extraneous spaces the website threw in at Housing_Officer and Date_Application). Enjoy.

PARAMETERS [Forms]![frmFinancialYear]![dteF1] DateTime, [Forms]![frmFinancialYear]![dteT1] DateTime;
SELECT tblMediation_Tenancy_Tribunal_Activity_ALLDATA.Hou sing_Officer, tblMediation_Tenancy_Tribunal_Activity_ALLDATA.Arr ears_Current, Date() AS [Report Date], IIf([Arrears_Current]=-1,1,0) AS Total, tblMediation_Tenancy_Tribunal_Activity_ALLDATA.Dat e_Application_Lodged
FROM tblMediation_Tenancy_Tribunal_Activity_ALLDATA
WHERE (((tblMediation_Tenancy_Tribunal_Activity_ALLDATA. Arrears_Current)=True) AND ((tblMediation_Tenancy_Tribunal_Activity_ALLDATA.D ate_Application_Lodged) Between [Forms]![frmFinancialYear]![dteF1] And [Forms]![frmFinancialYear]![dteT1]) AND ((Year([tblMediation_Tenancy_Tribunal_Activity_ALLDATA.Dat e_Application_Lodged]) & "/" & Format(Month([tblMediation_Tenancy_Tribunal_Activity_ALLDATA.Dat e_Application_Lodged]),"00"))<Year(DateAdd("m",-7,Now())) & "/" & Format(Month(DateAdd("m",-7,Now())),"00")));

[Kirsti]

Wonderful, thanks June.

I actually need 2 versions of this code. One to bring back all records in the previous month, which I have been able to achieve using the code you posted above. (The Month(Now())-7 was just an example, as I was trying to test how the code would work when running it in Jan, to bring Dec results). So this bit is now working - Thank you!

The second version I need, is to bring back all records in the financial year, excluding the current month's data. I tried to achieve this by playing with your code, with no success. Are you able to show me what this code would look like?

I really appreciate your help.

[June7]

Wouldn't the criteria simply be: <Year(Now()) & "/" & Format(Month(Now()),"00")

[Kirsti]

Perfect, thanks again June.

[June7]

You could possibly make one query serve for both. Use a query input parameter in the criteria for the months to subject. The input could be reference to another textbox on form.

<Year(DateAdd("m",-Nz([Forms]![frmFinancialYear]![tbxMonths],0),Now())) & "/" & Format(Month(DateAdd("m",-Nz([Forms]![frmFinancialYear]![tbxMonths],0),Now())),"00")