Query Criteria (2000 SP3)

Topic

New Reply

I have a report (actually two separate reports on one master sheet) that has the requirement of the current month’s stats as well as year-to-date stats. This, in and of itself, is not the problem. The problem is that the fiscal year runs from Apr 1 to Mar 31, but for the first reporting year, because the agency received the funding starting Oct 1, the fiscal year will be from Oct 1 through Mar 31. Thereafter, it will revert to the normal fiscal year (Apr 1).

I use a date picking form for running all reports and and am using two separate queries for the monthly and YTD stats. For YTD, what I was trying to do was something like: If the ending date of the period is before April 1, 2008, then give me info from Oct 1 to [Forms]![frmDates]![txtenddate], otherwise, give me from Apr 1 in the current fiscal year to the current reporting month.

I was using a pair of nested IIf statements. The various dates are caculated using dateserial. I get an error message stating that the expression is too complex to be evaluated.

Can anyone suggest some air code that would be an appropriate starting point. I no longer have the IIf statements since they weren’t working and the client needed info right away, so I just hard-coded the dates they needed today. I can re-construct them if it would be helpful.

Reply | Quote

Replies

It depends on the complexity of the query. This works for me, but it may not work for you:

Between DateAdd(“m”,3,DateSerial(Year(DateAdd(“m”,-3,[Forms]![frmDates]![txtEndDate])),1-6*(Year(DateAdd(“m”,-3,[Forms]![frmDates]![txtEndDate]))<2008),1)) And [Forms]![frmDates]![txtEndDate]))

Reply | Quote

Thanks Hans.

It works, but it isn’t giving me the correct results for the current half-fiscal year. I popped it into the query and for Nov 30 2007 as the end date, it gave me all the records from April 1, 2007. I don’t have the brain cells these days to parse your math. Is there possibly an error somewhere. Too many brackets for my little brain.

Reply | Quote

I included some closing parentheses (from the SQL string) that I shouldn’t have -sorry about that. Let’s try again:

Between DateAdd(“m”,3,DateSerial(Year(DateAdd(“m”,-3,[Forms]![frmDates]![txtEndDate])),1-6*(Year(DateAdd(“m”,-3,[Forms]![frmDates]![txtEndDate]))<2008),1)) And [Forms]![frmDates]![txtEndDate]

If I enter 30-Nov-2007 in the text box on the form, I get all records with a date between 1-Oct-2007 and 30-Nov-2007.

Reply | Quote

I’ve been away from the database for the last few days. Just tried it and works like a charm as usual. Add another beer to my account.

For future reference, could you explain what is happening here, Hans? I get the basics of using dateadd, I just can’t figure out exactly what you did with the math! Particularly the second dateadd with the 1-6*…

Reply | Quote

DateAdd(“m”,-3,[Forms]![frmDates]![txtEndDate]) subtracts 3 months from the end date. So if txtEndDate = 5-Dec-2007, this DateAdd results in 5-Sep-2007. The year of this date is the fiscal year (dates before the 1st of April end up in the previous year).

Year(DateAdd(“m”,-3,[Forms]![frmDates]![txtEndDate])) is the fiscal year.

If this is before 2008, Year(DateAdd(“m”,-3,[Forms]![frmDates]![txtEndDate]))<2008 is True = -1, so 1-6*(Year(DateAdd("m",-3,[Forms]![frmDates]![txtEndDate]))<2008) = 1 – 6*-1 = 1+6 = 7, corresponding to July.

If the fiscal year is 2008 or later, Year(DateAdd("m",-3,[Forms]![frmDates]![txtEndDate]))<2008 is False = 0, so 1-6*(Year(DateAdd("m",-3,[Forms]![frmDates]![txtEndDate]))<2008) = 1 – 6*0 = 1, corresponding to January.

DateSerial(Year(DateAdd("m",-3,[Forms]![frmDates]![txtEndDate])),1-6*(Year(DateAdd("m",-3,[Forms]![frmDates]![txtEndDate]))<2008),1) is the date in the fiscal year, in month 7 or 1, day 1, in other words it's the 1st of July (before 2008) or the 1st of January (2008 or later).

The outer DateAdd then adds 3 months again to translate from fiscal year back to calendar year, so it evaluates to the 1st of October (before 2008) or the 1st of April (2008 or later). This is the effective start of the fiscal year.

Of course I didn't write down the expression in one go. I built it in small parts, then successively substituted each part in the next part, until I arrived at this horrible expression.

Reply | Quote

It took a while, but I finally understand it. I got hung up on the multiplying by 6 till I realized that 6*-1 = negative 6 so 1 minus negative 6 = 1 plus 6… Once I figured out why you wanted to do that at all, it was simple. LOL

Now that I’ve got it, I give thanks for the existence of Hans because I never would have come up with that one!

Reply | Quote