Query to Complex with Criteria

[Order2Chaos]

Query too Complex with Criteria

Not exactly where I would have looked first to post this sort of thing, so I hope this is indeed the right spot!

First off, I consider myself mostly a beginner with Access, so please be gentle =)

For some background on my database:
I am building a database that runs basic analysis on financial information over several years, both weekly and monthly, presenting a variety of charts including cumulative.

Just to be dificult, the figures need to be based on a financial year (starting on the 1st of July here). Some basic math returns the financial week and month figures without a problem.

So far, everything works exactly as I wanted it to, no real problems.

There was a problem with partial weeks at the beginning of the financial year, as there was little or no data entered for these dates (2 day weeks, etc). So I have been asked to change it to start off the first FULL week of the financial year (I have decided this to be the first Monday of the financial year).

I managed to come up with a formula to use in one of my query fields in Design that returns the week number value that I am looking for, and as near as I can tell it works without a hitch. The problem occurs when I try to restrict the values with the Criteria field, and suddenly it is 'too complex'.

My formula is.. scary and confusing to look at, but as follows:

expr1: IIf(((([date]-IIf(Month([date])>6,DateSerial(Year([Date]),7,1),DateSerial(Year([Date])-1,7,1))+Weekday(IIf(Month([date])>6,DateSerial(Year([Date]),7,1),DateSerial(Year([Date])-1,7,1))))/7))-Round((([date]-IIf(Month([date])>6,DateSerial(Year([Date]),7,1),DateSerial(Year([Date])-1,7,1))+Weekday(IIf(Month([date])>6,DateSerial(Year([Date]),7,1),DateSerial(Year([Date])-1,7,1))))/7),0)>0,Round((([date]-IIf(Month([date])>6,DateSerial(Year([Date]),7,1),DateSerial(Year([Date])-1,7,1))+Weekday(IIf(Month([date])>6,DateSerial(Year([Date]),7,1),DateSerial(Year([Date])-1,7,1))))/7),0)+1,Round((([date]-IIf(Month([date])>6,DateSerial(Year([Date]),7,1),DateSerial(Year([Date])-1,7,1))+Weekday(IIf(Month([date])>6,DateSerial(Year([Date]),7,1),DateSerial(Year([Date])-1,7,1))))/7),0))-IIf(Weekday(IIf(Month([date])>6,DateSerial(Year([Date]),7,1),DateSerial(Year([Date])-1,7,1)))>2,1,0)

I have to stress that this formula appears to work exactly as I inteded, it is only when I try to restrict selected records with the Critera field in Query Design that I get an error.

The only thing I can think of to overcome this so far is to write the data to a temporary table and then run a select query on that table. I would like to avoid doing that for now if possible though *-)

If someone can suggest a better solution, or a better way to determine week number, it would be much appreciated =D

Thanks in advance,

John.

 

[Order2Chaos]

In an effort to try and get this to work properly I made my first custom function, which is much easier on the eyes (I don't have the function on this computer however), and have found a problem with that idea also - perhaps someone would be able to offer some insight into this problem?

The function itself returns the exact same figure as the expression above, and therefore does actually work. However, when I try to use the criteria field it just dies on me.. It seems that the field I'm referring to in the function is returning a null value when I try to use criteria in the query.

Why would my function work without criteria, yet only find null values with criteria? I can post the VBA code for the function later if needed.