intermittent #error in unbound txt box with IIf statements

[quaddawg via AccessMonster.com]

I am having a peculiar (to me) problem that does not happen all the time.
On a subform in data sheet view the first box is an unbound box (not a tab
stop) with the following function:

=IIf([End Date] Is Null And [StartDate] Is Not Null,"Active", IIf([End Date]
Is Not Null And (Format([End Date],"yyyymm"))>=Format(Date(),"yyyymm"),
"Active", IIf([End Date] Is Not Null And (Format([End Date],"yyyymm"))
<=Format(Date(),"yyyymm"),"Inactive"," ")))

*It's gone through many iterations as I try to diagnose the problem (e.g.,
the final " " is new but makes no difference. Also, the perhaps superfluous
"Is Not Nulls" are a result of diagnosing the problem).

If I start to add a new record in the subform datasheet by entering anything
in any of the other datasheet fields, a new line for a new record pops up (as
usual) and the box with the function above reads #Error. If I fill in that
line, it reads properly and the next new line that pops up has the error
message.

Strangely enough, this does not always happen, which leads me to think that
the problem might be more than just the poorly written function above.

Any suggestions? If Nz is one, please tell me where to put it/them, as I've
tried tons of combinations to no avail.

 

[Allen Browne]

Try:
= IIf([End Date] <= Date(), "Inactive", "Active")

If the end date has already passed, this shows Inactive.
For all other cases (null or not), it shows Active.

Is that what you need?

 

[quaddawg via AccessMonster.com]

The error message still exists using the suggested function.

Also, I need the formatting (or something like it) in order to list
memberships as "active" that are in the same month, but with an earlier day,
than the current date.

Also, the suggested function puts "active" in any blank record as soon as the
new line pops up.

I'm pretty happy with what my function captures, I just wish I could suppress
the #error message, which is more an annoyance than anything.

To summarize, I'd like the box to be blank until data is entered into End
Date and/or StartDate.

Try:
= IIf([End Date] <= Date(), "Inactive", "Active")

If the end date has already passed, this shows Inactive.
For all other cases (null or not), it shows Active.

Is that what you need?

I am having a peculiar (to me) problem that does not happen all the time.
On a subform in data sheet view the first box is an unbound box (not a tab

[quoted text clipped - 27 lines]

I've
tried tons of combinations to no avail.

 

[Allen Browne]

Now I'm confused.

Originally the text box showed #Error on the new record row.

Now it still shows #Error on the new row, but it also shows Active?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The error message still exists using the suggested function.

Also, I need the formatting (or something like it) in order to list
memberships as "active" that are in the same month, but with an earlier
day,
than the current date.

Also, the suggested function puts "active" in any blank record as soon as
the
new line pops up.

I'm pretty happy with what my function captures, I just wish I could
suppress
the #error message, which is more an annoyance than anything.

To summarize, I'd like the box to be blank until data is entered into End
Date and/or StartDate.

Try:
= IIf([End Date] <= Date(), "Inactive", "Active")

If the end date has already passed, this shows Inactive.
For all other cases (null or not), it shows Active.

Is that what you need?

I am having a peculiar (to me) problem that does not happen all the time.
On a subform in data sheet view the first box is an unbound box (not a
tab

[quoted text clipped - 27 lines]

I've
tried tons of combinations to no avail.

 

[quaddawg via AccessMonster.com]

That's the frustrating part--it's intermittent, as before. Sometimes a new
record line has a blank box (for my old function) or reads "Active" (your
function); and sometimes the new record line reads "#Error" for both
functions (to diagnose, both functions are present). When it's working, it's
working for both, when it's not, it's not working for either.

The #Error message of course disappears as I enter data on the line with the
error message. It's infuriating because as far as I can tell, doing the
exact same actions sometimes is kosher, and sometimes yields errors.

This is why I'm thinking it's related, at least in part, to something else
screwy.

Now I'm confused.

Originally the text box showed #Error on the new record row.

Now it still shows #Error on the new row, but it also shows Active?

The error message still exists using the suggested function.

[quoted text clipped - 28 lines]

 

[Allen Browne]

This could be caused by something else.

Once Access is unable to resolve a calculated control (resulting in #Error),
and other calculated controls that have not already been resolved show
#Error as well. I am guessing that another control could be the cause.

No point making the expression more complex to do what you want until this
issue is solved. One possibility is to remove any other calculated controls.
Once this expression is working 100% of the time, you can start adding the
others back until you find the culprit.

Sometimes Access gets confused about the Name of objects. Make sure you
don't have any control that has the same name as a field, unless the Control
Source is that field also. For example, if you have fields named FirstName
and Surname, you must not have a box that is named Surname but its Control
Source is =[Firstname] & [Surname].

Whenever Access is confused about names, it is also worth checking that Name
AutoCorrect is turned off. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect

Then compact the database:
Tools | Database Utilities | Compact
Explanation of why:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

That's the frustrating part--it's intermittent, as before. Sometimes a
new
record line has a blank box (for my old function) or reads "Active" (your
function); and sometimes the new record line reads "#Error" for both
functions (to diagnose, both functions are present). When it's working,
it's
working for both, when it's not, it's not working for either.

The #Error message of course disappears as I enter data on the line with
the
error message. It's infuriating because as far as I can tell, doing the
exact same actions sometimes is kosher, and sometimes yields errors.

This is why I'm thinking it's related, at least in part, to something else
screwy.

Now I'm confused.

Originally the text box showed #Error on the new record row.

Now it still shows #Error on the new row, but it also shows Active?

The error message still exists using the suggested function.

[quoted text clipped - 28 lines]

I've
tried tons of combinations to no avail.