Access Question 1

[kina1]

I HAVE A QUERY IN WHICH I MADE 3 EXPRESSIONS.

WE ARE TRYING TO CALCULATE PROCESSING TIMES FOR FORMS.
EXPR 1 IS DATE OF FI MINUS DATE OF FILING.
EXPR 2 IS DATE OF CLOSURE MINUS DATE OF FILING
EXPR 3 IS LAST INPUT DATE MINUS DATE OF FILING

IN THE PROCESSING LINE, IF THEY HAVE AN FI, THAT IS THE SHORTEST AMOUNT OF PROCESSING TIME, IF THEY CLOSED THEN THAT IS THE SECOND LONGEST PROCESSING TIME AND IF THEY HAD NEITHER OF THOSE, THEN IT WAS STILL IN PROCESSING UP TO THE DATE OF LAST INPUT. SO I WANT TO CREATE AN EXPRESSION THAT WILL PRINT THE VALUE IN EXPR 1, IF IT'S BLANK THEN PRINT THE VALUE IN EXPR 2, AND IF THAT IS ALSO BLANK THEN PRINT WHAT IS IN EXPR 3.

SO, I'M THINKING AN IF/THEN LOOP?

SOMETHING LIKE:

IF <<EXPR1>>IS NOT NULL <<EXPR1>>, THEN (IF <<EXPR2>> IS NOT NULL <<EXPR2>>, THEN <<EXPR3>>)

I CAN'T FIGURE OUT HOW TO GET THE SYNTAX ALL CORRECT TO MAKE IT WORK.

CAN ANYONE OFFER ANY ASSISTANCE?

THANKS!

 

[cranebill]

Are you trying to do this in the query itself or are you doing this via code?

Bill

 

[kina1]

Bill,

I would like to do this in the query itself.

Thanks again!

 

[cranebill]

What are the expressions, are they just fields... or are they calculations? Post them if you dont mind.

Bill

 

[kina1]

Bill, below are the expressions I would like to use:

Expr 1: Days til FI: [FY04 Counselors Report]![Dt Final Intvw]-[FY04 Counselors Report]![Initial Contact]

Expr 2: Days til Clsd: [FY04 Counselors Report]![Dt Closed]-[FY04 Counselors Report]![Initial Contact]

Expr 3: Days til Last Input: [FY04 Counselors Report]![DtLstInput]-[FY04 Counselors Report]![Initial Contact]

Thanks again!

 

[cranebill]

Question. You want all this in the same Column right so that you only have one column for whichever expression is used. If this is the case if there are more than one record then this will cause a conflict because you really wont know what the number represents because the column heading which may apply to one of the items may not apply to all of them. If this is the case why not just use all 3 columns.

Bill

 

[kina1]

Thanks so much, Bill!

I will try it.

 

[kina1]

Bill,

May I ask one more question? I just realized that I already have those three expressions in 3 separate columns. What I would really like is a 4th column to look at all 3 columns and then print the lowest number.

Thanks again!

 

[cranebill]

Basically would that not be a toss up between expression 2 and 3?

 

[cranebill]

What you could do is create the fourth Column like this.

LowestNumber: IIF([Days Til Clsd]>[Days Til Last Input], [Days Til Last Input], [Days Til Clsd])

Bill

 

[kina1]

Bill,

If this works I will be forever grateful.

Thanks for all of your valuable assistance.

Thanks again!

 

[cranebill]

Well let me know if it does work.... it worked here lol... so if i can be of any more help feel free

These forums have helped me a ton in the past so I don't mind giving back

Bill

 

[kina1]

Thanks again, Bill!

I am going to give it a try in a bit.

Thanks!