Case Statement Syntax

[CFrockchelle]

I'm not actually sure how to write case statement in Access. I have a report that has a scheduled completion date, completed date, and days over due. The days over due is the difference between the scheduled completion date and the actual complete date. On my report I want to display the following but not sure what the syntax is since I don't program in Access.

Case duedate <= 0
"On Schedule"
Case duedate > 0
Number of days overdue
Case Else
"In progress"

Thanks for any help that you can provide.

 

[dhookom]

You might be able to get by with a text box bound to your expression that calculates the days over due and set the Format Property to:
#,##0;"On Schedule","On Schedule","In Progress"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CFrockchelle]

I couldn't get what you suggested to work. Not sure what I did wrong. I have the days over due already calculated in a query. I used that as my control source and for the Format property I put in what you suggested and when the report is ran I get - On Schedule,On Schedule,In Progress.

In the report I want to display if the value is less than 0 I want it to display "On Schedule" more than Zero the number itself, and if its null "In Progress". Can this even be done in a report? I don't see any way to do any type of coding. Any other suggestions on how I may be able to get this to work? Sorry for being a pain!!

 

[dhookom]

In the query, does the column display left or right aligned? Do you see any values greater than 0?

What is the exact Format property value you used?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CFrockchelle]

The report displays the actual value of Days Over Due if the value is greater than 0 which is what I want. But if the value is less than zero it displays On Schedule,On Schedule,In Progress. I just copied and pasted what you had into the format property.

I have some values that are null because the date complete is null so there is nothing to calucate so if the field is null I need it to display "In progress" which would have been by else case. (Sorry for not explaining very well).

Can you put IF statements in the control source?

 

[CFrockchelle]

I think I figured it out. . . I used the following in my control source:

=IIf([DaysOverDue]<=0,"On Schedule",IIf([DaysOverDue]>0,[DaysOverDue],"In Progress"))

Thanks for all your help.

 

[dhookom]

My bad with the property value. Two of the commas should have been semi-colons.

#,##0;"On Schedule";"On Schedule";"In Progress"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]