Problem with query statement

[marcvanderpeet12]

Guys,

I have the following situation. I've created a database for different tests within my company. If you want to receive a degree you have to do two thing:

- pass exam with > 5,5
- pass assignment with a grade which is dependent on your workfield (operations dpt requires a 6, sales a 5)

I managed to get this working by adding the following text in the expression builder of a query:

ASSIGNMENT PASSED: IIf([ASSIGNMENTMENT_PASSED]>=[ASSIGNMENTMENT_PASSED_REQUIRED];"PASSED";"NOTPASSED")

And then I build another query based on this query which filters records which have a grade >5,5 and an ASSIGNMENT PASSED value of "PASSED"

So far so good. Only now they are going to change the assignment values required. So I'm facing a situation where:

- people of particular workfield who took the test > 01-01-14 have a particular threshold value
- people of that same workfield who took the test < 01-01-14 have a particular threshold value



So I have to include the date of my table into the query which determines whether or not people passed. Only cant find an efficient way to do this. Anybody suggestions?

Thanks in adance.

Dear regards,

Marc

[June7]

ASSIGNMENT_PASSED and ASSIGNMENT_PASSED_REQUIRED are the test scores? So 5.5 is not good enough for operations but too good for sales?

"If testdate>=1/1/14 then meet this criteria else this"

IIf(testdate >= #1/1/2014#, this expression, this expression)

Depending on how many different conditions, could need a custom function.

[NTC]

if there were lots of values involved - rather than one date, one test and one grade value - - probably the only sane way to approach it would be to make a 'passing grade' table ....and then you would query that table using the criteria of the record (i.e. date, test,) and then do a compare.... possibly a DLookUp rather than a query. But what I guess I'm saying is that as posted you kind of are in an inbetween situation where taking a table approach as I suggest is probably more work than shoe horning in more IIF code......