• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

Access - Expression Builder

Experts,
I am having a problem thinking this one out.  I am building an access report that I want to flag dates depending on how the progress is going.
[EstDate] - This is a projected date when work will be done.
[ActualDate] - This is the date when work was completed.
I have a textbox that I want to display on the report depending on condition either "", "In Progress", "Completed, or "Behind Schedule"

I am trying to write a formula in the expression builder of the textbox to do this.

If [EstDate] is null nothing is reported, textbox will be blank.
If [EstDate] is > Date() and [ActualDate] is null then textbox will show "In Progress"
If [EstDate] < [ActualDate] then textbox will show "Behind Schedule".
If [EstDate] > [ActualDate] then textbox will show"Completed"

Am I pushing the limits of the expression builder?  I am open to ideas.

Thanks for any assistance,
Bob  
0
RobertStamm
Asked:
RobertStamm
  • 9
  • 8
  • 3
  • +1
2 Solutions
 
McOzCommented:
This is how I would see it:

If [actualdate] is not null, then the project is completed (regardless of the estimated date) --> "Completed"
Else if [estdate] < Date(), then the project is behind schedule (estdate is in the past) --> "Behind Schedule"
Else project is in progress but not yet behind schedule --> "In Progress"

expression would go something like:

iif(isnull([actualdate]),iif([estdate] < Date(), "Behind Schedule", "In Progress"), "Completed")

untested, but hopefully gets you going in the right direction...

Cheers, McOz
0
 
NorieCommented:
Bob

Try this:

Status: IIf(IsNull([EstDate]),"",IIf(IsNull([ActualDate]),"In Progress",IIf([EstDate]<[ActualDate],"Behind Schedule","Completed")))

0
 
McOzCommented:
Oops, missed the part about returning blank if estdate is null...

change expression to:

iif(isnull([actualdate]),iif(isnull([estdate]),"",iif([estdate] < Date(), "Behind Schedule", "In Progress")), "Completed")

McOz
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NorieCommented:
Forget that, I missed one of the criteria.

This should do it, fingers crossed.

Status: IIf(IsNull([EstDate]),"",IIf([EstDate]>Date() And IsNull([ActualDate]),"In Progress",IIf([EstDate]<[ActualDate],"Behing Schedule","Completed")))
0
 
RobertStammAuthor Commented:
imnorie,
You are close but the case [EstDate] has date and [ActualDate] is null is showing "Completed"

McOz
You are close but when [ActualDate] is > [EstDate], textbox is not showing "Behind Schedule"

These are both very close.
0
 
Patrick MatthewsCommented:
Embedded IIf expressions make my head hurt--I prefer Switch:

Switch([EstDate] Is Null, "", [EstDate] > Date() And [ActualDate] Is Null, "In Progress", [EstDate] < [ActualDate], "Behind Schedule", [EstDate] > [ActualDate], "Completed")

For more about Switch, please see my article:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html


BTW, what happens if [EstDate] = [ActualDate]?  Your conditions do not address that possibility...
0
 
McOzCommented:
"You are close but when [ActualDate] is > [EstDate], textbox is not showing "Behind Schedule""

That is because, by definition if [ActualDate] is not null, then the project is completed. Right?

"[ActualDate] - This is the date when work was completed."

By this definition, if you compare [EstDate] against [ActualDate], you will only get a "Behind Schedule" status for projects that have been completed, but after the estimated date. Is this what you want?

What happens if [EstDate] is in the past, and [ActualDate] is null?

McOz
0
 
RobertStammAuthor Commented:
Matthew,
I am not fimilar with switch.  I put it to the test and found a issue.
if [EstDate] has date and [ActualDate] is null, I am not getting "In Progress".  Everything else seems fine.

You are correct that I need to change logic to allow for that condition which would be "On Schedule"
Switch([EstDate] Is Null, "", [EstDate] > Date() And [ActualDate] Is Null, "In Progress", [EstDate] < [ActualDate], "Behind Schedule", [EstDate] >=[ActualDate], "Completed")

I added an = to your statement.

0
 
RobertStammAuthor Commented:
Ok gang...I had to update my logic.

What happens if [EstDate] is in the past, and [ActualDate] is null?
BTW, what happens if [EstDate] = [ActualDate]?  Your conditions do not address that possibility...

Here is the new set of rules...
If [EstDate] is null nothing is reported, textbox will be blank.
If [EstDate] is > Date() and [ActualDate] is null then textbox will show "In Progress"
If [EstDate] is < Date() and [ActualDate] is null then textbox will show "Behind Schedule"
If [EstDate] < [ActualDate] then textbox will show "Behind Schedule".
If [EstDate] >= [ActualDate] then textbox will show"Completed"

The makes my head hurt and I am not smart enough to figure it out!
Thanks all for your efforts so far.






0
 
NorieCommented:
Robert

Strange, when I test it shows 'In Progress' when [EstDate] has a date and [ActualDate] is null.

By the way I assumed that you wanted >= , if [EstDate]>[ActualDate] is False then [ActualDate<=[EstDate] is true.

IIf([EstDate]<[ActualDate],"Behing Schedule","Completed") takes care of both outcomes.
0
 
NorieCommented:
Robert

There your conditions.:)

What happens when [EstDate] = Date?
0
 
RobertStammAuthor Commented:
You guys are tough :)...add this to the rules...

Here is the new set of rules...
If [EstDate] is null nothing is reported, textbox will be blank.
If [EstDate] is >= Date() and [ActualDate] is null then textbox will show "In Progress"
If [EstDate] is < Date() and [ActualDate] is null then textbox will show "Behind Schedule"
If [EstDate] < [ActualDate] then textbox will show "Behind Schedule".
If [EstDate] >= [ActualDate] then textbox will show"Completed"

0
 
NorieCommented:
Oops, accidentally hit Submit.

Meant to add this:

Status: IIf(IsNull([EstDate]),"",IIf(IsNull([ActualDate]),IIf([EstDate]>Date(),"In Progress","Behind Schedule"),IIf([EstDate]<[ActualDate],"Behind Schedule","Completed")))



0
 
RobertStammAuthor Commented:
FYI - I have to take a break and run my kids around town...I will get back to this later tonight.  Thanks so much for your input and assistance.

Bob
0
 
NorieCommented:
Missed the update:

IIf(IsNull([EstDate]),"",IIf(IsNull([ActualDate]),IIf([EstDate]>=Date(),"In Progress","Behind Schedule"),IIf([EstDate]<[ActualDate],"Behind Schedule","Completed")))
0
 
RobertStammAuthor Commented:
Imnorie...I think that was it!  I will test more tonight before awarding points.
0
 
Patrick MatthewsCommented:
Updating Switch:


Switch([EstDate] Is Null, "", [EstDate] > Date() And [ActualDate] Is Null, "In Progress", [EstDate] < Date() And [ActualDate] Is Null, "Behind Schedule", [EstDate] < [ActualDate], "Behind Schedule", [EstDate] >= [ActualDate], "Completed")


Of course, you still have a hole here: what if [EstDate] = Date() and [ActualDate] is null?
0
 
NorieCommented:
What if both [EstDate] and [ActualDate] are null?

Or has that already been covered.:)
0
 
RobertStammAuthor Commented:
If both null then textbox equals "".
0
 
NorieCommented:
IIf(IsNull([EstDate]) And IsNull([ActualDate]),"",IIf(IsNull([ActualDate]),IIf([EstDate]>=Date(),"In Progress","Behind Schedule"),IIf([EstDate]<[ActualDate],"Behind Schedule","Completed")))
 
0
 
RobertStammAuthor Commented:
Thanks. I will test later tonight.
0
 
RobertStammAuthor Commented:
Thanks imnorie.  This was just what I needed.

Matthew, I want to give you credit for assisting.  The switch is something i will look into.

I appericate all the help from everyone.
Thanks,
Bob
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now