Expression Problem... Always Returns -1

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Will try not to be long winded. Database tracking parts and receipt of
documentation... Status field in form to display "Red" "Yellow" "Green" based
on time till A-Rev date for the part.

Form Fields in question: [Status] [TargetARev] [ReceiptDate]

Control Source in [Status]:
=IIf(IsNull([SDoCReceiptDate]),"Incomplete" Or
(IIf(([TargetARev]-Date()>30),"Green",IIf([TargetARev]-Date()<0,"Red",IIf([TargetARev]-Date() Between 1 And 29,"Yellow")))),"Completed")

The "Completed" portion is the only thing working. Anything else results in
-1. I can't for the life of me figure out where that is coming from. None
of the fields are currently formatted to date/time. They were initially, but
when I realized that dates could not reflect a null or "" value, I had to
find a work around.

I appreciate any help.
 
Meredith said:
Will try not to be long winded. Database tracking parts and receipt
of
documentation... Status field in form to display "Red" "Yellow"
"Green" based
on time till A-Rev date for the part.

Form Fields in question: [Status] [TargetARev] [ReceiptDate]

Control Source in [Status]:
=IIf(IsNull([SDoCReceiptDate]),"Incomplete" Or
(IIf(([TargetARev]-Date()>30),"Green",IIf([TargetARev]-Date()<0,"Red",II
f([TargetARev]-Date()
Between 1 And 29,"Yellow")))),"Completed")

The "Completed" portion is the only thing working. Anything else
results in -1. I can't for the life of me figure out where that is
coming from. None
of the fields are currently formatted to date/time. They were
initially, but
when I realized that dates could not reflect a null or "" value, I
had to
find a work around.

I appreciate any help.
Click to expand...

What is this

"Incomplete" Or (IIf( ...

supposed to mean? Whatever the result of the IIf() expression, it will
be ORed with the string "Incomplete" to give a truth value, and I
imagine that truth value will always be True (= -1). I can't figure out
from the controlsource expression as it is currently formulated just
what it is you want to have happen. I see possible values of
"Incomplete", "Green", "Red", "Yellow", and "Complete". Under what
circumstances should each of these be displayed?
 
hehe... yeah... that's about how confused I am about it now. There is
probably an easier way to do this than what I have constructed in my brain.

The process is to send suppliers a form [RequestDate]. They return the form
[ReceiptDate]. Hopefully, they return it before the A-Rev Date [ARevDate].
The status field serves the purpose of displaying the current status based on
[ARevDate]-Date(). If there are over 30 days until A-Rev, the status is
"Green", between 1 and 29 "Yellow", <0 "Red".

The [ReceiptDate] is either going to be empty or have a date. If there is a
date, the status should be "Completed".

If [ReceiptDate] is blank, then the string for "Green" "Yellow" "Red" comes
in (Based on [ARevDate]-Date().

But then I realized that if there was no date in the [ARevDate] field, all
of this would be futile, so I wanted to factor in an "Incomplete" Status.
Simply meaning that there wasn't enough information to give a correct status.

I do see what you're saying about the OR statement and why that won't work.
I've just thought about this for so long, my brain has turned to green
Jell-O. Please let me know if there is anything I left out. I really do
appreciate the help.

Meredith =)
 
So close but yet so far...

I tried rewriting the whole thing... Removed the OR after doing some
thinking on your message. Completed and Incomplete are working. Now on
everything else I am getting #Error. Seems like this should work, but of
course, it's never that easy...

=IIf(IsDate([SDoCReceiptDate]),"Completed",IIf(([TargetARev]-Date())>=30,"Green",IIf(([TargetARev]-Date())<=0,"Red",IIf(([TargetARev]-Date())
Between 1 And 29,"Yellow","Incomplete"))))

Mer
 
Meredith said:
hehe... yeah... that's about how confused I am about it now. There is
probably an easier way to do this than what I have constructed in my
brain.

The process is to send suppliers a form [RequestDate]. They return
the form [ReceiptDate]. Hopefully, they return it before the A-Rev
Date [ARevDate]. The status field serves the purpose of displaying
the current status based on [ARevDate]-Date(). If there are over 30
days until A-Rev, the status is "Green", between 1 and 29 "Yellow",
<0 "Red".

The [ReceiptDate] is either going to be empty or have a date. If
there is a date, the status should be "Completed".

If [ReceiptDate] is blank, then the string for "Green" "Yellow" "Red"
comes in (Based on [ARevDate]-Date().

But then I realized that if there was no date in the [ARevDate]
field, all of this would be futile, so I wanted to factor in an
"Incomplete" Status. Simply meaning that there wasn't enough
information to give a correct status.

I do see what you're saying about the OR statement and why that won't
work. I've just thought about this for so long, my brain has turned
to green Jell-O. Please let me know if there is anything I left out.
I really do appreciate the help.

Meredith =)
Click to expand...

If you're using Access 2000 or later, it may be somewhat clearer to use
the Switch function instead of nested IIfs. I think the expression
below corresponds to the logic you've outlined above, though it doesn't
match up with what you originally posted. I've broken it onto multiple
lines for clarity, but it should all be on one line in your text box's
ControlSource property:

=Switch([ReceiptDate] Is Not Null, "Complete",
[ARevDate] Is Null, "Incomplete",
[ARevDate]-Date()>=30, "Green",
[ARevDate]-Date()>=1, "Yellow",
True, "Red")

Note that I've adjusted your boundary conditions slightly, as you had a
logical "hole" at [ARevDate]-Date() = 30. I've also used the
control/field names you used in your most recent message, though these
aren't the ones you used in your original post. You can fix the names
in the expression up as needed.
 
Meredith said:
So close but yet so far...

I tried rewriting the whole thing... Removed the OR after doing some
thinking on your message. Completed and Incomplete are working. Now
on
everything else I am getting #Error. Seems like this should work,
but of
course, it's never that easy...

=IIf(IsDate([SDoCReceiptDate]),"Completed",IIf(([TargetARev]-Date())>=30
,"Green",IIf(([TargetARev]-Date())<=0,"Red",IIf(([TargetARev]-Date())
Between 1 And 29,"Yellow","Incomplete"))))
Click to expand...

I've posted an expression I think will work, but I have to say that once
expressions start to get this complicated, I usually just write a VBA
function to do the evaluation and calculate the result, and call that
from my controlsource or query.
 
Back
Top