Trying to set an iif condition in query criteria

[visacrum]

I have a query that I need to return results for the current fiscal year (runs 1 June to 31 May) for a database of events. I got code help from another forum that I thought would work, but it's returning a blank sheet and I can't figure out why.

The sql for the entire query is:

SELECT tbluEventType.EventType, Count(jxtEventAttendance.SurgeonID) AS CountOfSurgeonID, tblEventInformation.[EventStartDate], (DateDiff("d",[EventStartDate],[EventEndDate])+1) AS Days

FROM (tbluEventType RIGHT JOIN tblEventInformation ON tbluEventType.EventTypeID = tblEventInformation.[EventTyp ID]) INNER JOIN jxtEventAttendance ON tblEventInformation.EventInfoID = jxtEventAttendance.EventInfoID

GROUP BY tbluEventType.EventType, tblEventInformation.[EventStartDate], (DateDiff("d",[EventStartDate],[EventEndDate])+1), tblEventInformation.[EventEndDate]

HAVING (((tblEventInformation.[EventStartDate])=iif(month(date())>=1 and month(date())<=5,"Between #" & dateserial(year(date()) -1,6,1) & "# And #" & dateserial(year(date()),6,0) & "#","Between #" & dateserial(year(date()),6,1) & "# And #" & dateserial(year(date())+1,6,0) & "#" )))
ORDER BY tblEventInformation.[EventStartDate];

I read in another post that criteria using iif have to be used on queries that return single records with no ambiguous joins. Is there something I'm missing in the HAVING clause, or should I preface this query with another query to just draw off tblEventInformation,[EventStartDate] to filter the dates I need, then have the written query above draw those dates from that query filter (that's an idea I just had while I was typing this)?

 

[DCrake]

I think the easiest way to do this is to create a function that calculates the financial year of any given date and query on that.

Public Function FinYear(Optional AnyDate As Date = Date()) As String
Dim fYear As String

If Month(AnyDate) < 6 Then
   FinYear = Year(DateAdd("yyyy",-1,AnyDate)) & "/" & Year(AnyDate)
Else
   FinYear = Year(AnyDate) & "/" & Year(DateAdd("yyyy",1,AnyDate))
End If
End Function

David

 

[visacrum]

I think the easiest way to do this is to create a function that calculates the financial year of any given date and query on that.

Public Function FinYear(Optional AnyDate As Date = Date()) As String
Dim fYear As String
 
If Month(AnyDate) < 6 Then
   FinYear = Year(DateAdd("yyyy",-1,AnyDate)) & "/" & Year(AnyDate)
Else
   FinYear = Year(AnyDate) & "/" & Year(DateAdd("yyyy",1,AnyDate))
End If
End Function

David

This has inspired me to check out the use of modules (something I'm not versed with), and this is an excellent start, I thank you, and I apologize for having more questions:

Could you explain the function of the slash? I'm trying to work this code out (I love to learn), but I'm not figuring out what the slash does. As I read it, assuming today's date, FinYear = 2009/2010, is that right? If so, I don't fully understand how to apply that to the query (assuming it gets applied to a criteria).

Also, should fyear be defined in this function? It's DIM'd out, but doesn't get referenced. I was thinking perhaps the criteria expression may define it, but don't know if that works that way once the function has ended.

I'm really less than a novice with modules, so I'm sorry for the noob questions.

 

[DCrake]

I think you are confusing yourself with Rem and Dim.

Dim is short for Dimension or declare Rem is a commented statement

Dim fYear as String is simply saying create me a variable called fYear and I want it to be a string type variable.

The slash "/" in the syntax is simply used a seperator between the two overlapping years. 01/06/2009/31/05/2010

The function looks a any date it receives and checks which calendar month it appears in. If it is before June it is part of last year this year financial year. Otherwise it is part of this year next year financial year.

You are correct in stating that I have not used fYear in the function and it can be removed.

To use this in a query
bring down the date field in question then
pick a new column and type in

FiscalYear:FinYear([YourDateFieldHere])

Then run the query to view the results. you will see that each date has been allocated a financial year. If you scroll to records either side of 1st June you will see it change.

Now lets say you only want to look at the current financial year whenever you run this query, but you don't want to have to change it every year. Then underneath the FiscalYear column in the condition line enter

=FinYear(Date())

This will then apply a filter to all records whose financial year is the current financial year based on today's date.

David

David

 

[visacrum]

DCrake, you set me on the path to my ultimate victory over this problem. It actually wasn't solved by a module, but when you told me to create the FiscalYear:FinYear([YourDateFieldHere]), it gave me a compile error. I was sure I was doing something wrong, so I ranged Google on it. Microsoft Help actually gave me code to simply return a fiscal year:

FYear: Year([FieldName])-IIf([FieldName]< DateSerial(Year([FieldName]),6,1),1,0)

Then I used the rest of your idea to put a criteria on THAT field, instead of the date field I was trying to work with:

IIf(Month(Date())<6,Year(Date())-1,Year(Date()))

And it works! I know it works, I played with that IIf statement (If Month is >7, subtracted years in different places...I could have just done another column running that as a test, but this is how I roll).

Thank you very very VERY much for setting me on the right path!

 

[RandomT]

Hi visacrum!

I've been searching on google how to go about doing this, and i know this is the closest to what I really want to do. I've done what microsoft support offered using FYear statement, but I want my financial year to appear as 2006/2007...2007/2008...2008/2009 and so forth on a crosstab report where date is the Column Heading. Do you think you can help me solve this problem of mine?

Thanx in advance

 

[DCrake]

RandonT

That what the function FinancialYear() does. See earlier thread.

David

 

[RandomT]

Thank you DCrake...

I've just done that but an error message prompts me that "The Microsoft Jet database engine does not recognize 'FinYear' as a valid field name or expression' after i put my label on the page header on my report like =FinYear[Date]

Please help!

Thank you so much in advance
RandomT

 

[DCrake]

You code is wrong it should be

FinYear([YourDateFieldHere])

You forgot the round brackets.

David

 

[RandomT]

Thank you DCrake!

Now it works on query but i want it also on report, is there anyway to format this again on the actual report where financial years are my column headings?

Thanx in advance,
RandomT