IIF statement to calculate days open

jereece

Registered User.
Local time
Today, 04:26
Joined
Dec 11, 2001
Messages
300
I am trying to write an IIF statement to calculate the days a report has been open. I have to do this because in the database I am linking, the start date gets changed too easily, so I have to calculate the days open this way: DAYS_OPEN: (Date()-([prob_eval_orig_due_dte_tm]-30)) The database automatically assigns a 30 day due date. However now I have some reports due in 45 days. I need to modify my query to say if [cause_type_cd] is equal to "RC" use 45 instead of 30 otherwise use 30 in the days open calc. I assume I could do this in an IIF statement but I am not very good at these. Below is my stab at it but I get syntax error.

Code:
DAYS_OPEN: IIf([cause_type_cd]=[RC],(Date()-([prob_eval_orig_due_dte_tm]-45),Date()-([prob_eval_orig_due_dte_tm]-30)))

Can someone help?

Thanks,
Jim
 
I am trying to write an IIF statement to calculate the days a report has been open. I have to do this because in the database I am linking, the start date gets changed too easily, so I have to calculate the days open this way: DAYS_OPEN: (Date()-([prob_eval_orig_due_dte_tm]-30)) The database automatically assigns a 30 day due date. However now I have some reports due in 45 days. I need to modify my query to say if [cause_type_cd] is equal to "RC" use 45 instead of 30 otherwise use 30 in the days open calc. I assume I could do this in an IIF statement but I am not very good at these. Below is my stab at it but I get syntax error.

Code:
DAYS_OPEN: IIf([cause_type_cd]=[RC],(Date()-([prob_eval_orig_due_dte_tm]-45),Date()-([prob_eval_orig_due_dte_tm]-30)))

Can someone help?

Thanks,
Jim

Your Code has issues with mismatched parentheses. A corrected version that should work is below. I have also provided an alternative version of Code that I believe does the same thing but is a little easier to read. Let me know what you think.
Code:
[COLOR=black][FONT=Times New Roman][SIZE=3]Your Corrected Code (I hope)[/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][/SIZE][/FONT][/COLOR] 
[COLOR=black][FONT=Times New Roman][SIZE=3]DAYS_OPEN: IIf([cause_type_cd]=[RC],(Date()-([prob_eval_orig_due_dte_tm]-45)), (Date()-([prob_eval_orig_due_dte_tm]-30)))[/SIZE][/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]An alternative[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black][/COLOR][/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman][COLOR=black]DAYS_OPEN: Date()-([/COLOR][[COLOR=black]prob_eval_orig_due_dte_tm]- IIf([cause_type_cd]=[RC], 45, 30))[/COLOR][/FONT][/SIZE]
 
Thanks. I had to change [RC] to "RC" but after that both worked fine.

I appreciate the help.

Jim
 
Thanks. I had to change [RC] to "RC" but after that both worked fine.

I appreciate the help.

Jim

I was not sure if the [RC] referred to another Field in the main Table, or if it should have been a String, and I opted not to comment. Sorry about that.
 

Users who are viewing this thread

Back
Top Bottom