By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Iif Statement Returns Error in Form

P: n/a
Working in a form, I have an Iif statement where if a date is not
filled in, another field will show as a blank. If the date IS filled
in, the 2nd field will show its actual value. The following statement
is in the Control Source of the [00-Selected for Interrator]field
=IIf(IsNull([09-ReceivedDate])," ",[00-Selected for Interrator])

So, the results I get are this: If the date field is blank, my
[00-Selected for Interrator] field is blank. If my date field is
filled in, my [00-Selected for Interrator] returns an #ERROR.

I don't see anything wrong with this code. Does anyone have any
thoughts on this??? Thank you.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a


"kathy" <ka********@mmoh.com> wrote in message
news:20**************************@posting.google.c om...
Working in a form, I have an Iif statement where if a date is not
filled in, another field will show as a blank. If the date IS filled
in, the 2nd field will show its actual value. The following statement
is in the Control Source of the [00-Selected for Interrator]field
=IIf(IsNull([09-ReceivedDate])," ",[00-Selected for Interrator])

So, the results I get are this: If the date field is blank, my
[00-Selected for Interrator] field is blank. If my date field is
filled in, my [00-Selected for Interrator] returns an #ERROR.

I don't see anything wrong with this code. Does anyone have any
thoughts on this??? Thank you.

I tried this in the property sheet and got the same result. However this
works if you set the controlSource in the form current event.

If IsNull(Me.conditioning_field) Then
Me.target_field.ControlSource = ""
Else
Me.target_field.ControlSource = "details"
End If

Put this also in the After update of the conditioning_field as well so that
the target_field will refresh properly....

yamafopa!
Nov 12 '05 #2

P: n/a
Tym
On 18 Dec 2003 11:40:39 -0800, ka********@mmoh.com (kathy) wrote:
Working in a form, I have an Iif statement where if a date is not
filled in, another field will show as a blank. If the date IS filled
in, the 2nd field will show its actual value. The following statement
is in the Control Source of the [00-Selected for Interrator]field
=IIf(IsNull([09-ReceivedDate])," ",[00-Selected for Interrator])

So, the results I get are this: If the date field is blank, my
[00-Selected for Interrator] field is blank. If my date field is
filled in, my [00-Selected for Interrator] returns an #ERROR.

I don't see anything wrong with this code. Does anyone have any
thoughts on this??? Thank you.


I;ve found that "is null" only refers to numeric strings. For testing
strings you need to use

is = ""

what do you mean by "actual value"?

if they fill in a date in [09-ReceivedDate] you "translate" it to a
field which is a date format?

=IIf([09-ReceivedDate]=""," ",CvDate([09-ReceivedDate]))

?

Or am I missing something?
Nov 12 '05 #3

P: n/a
ka********@mmoh.com (kathy) wrote in
news:20**************************@posting.google.c om:
Working in a form, I have an Iif statement where if a date is
not filled in, another field will show as a blank. If the date
IS filled in, the 2nd field will show its actual value. The
following statement is in the Control Source of the
[00-Selected for Interrator]field
=IIf(IsNull([09-ReceivedDate])," ",[00-Selected for
Interrator])

So, the results I get are this: If the date field is blank, my
[00-Selected for Interrator] field is blank. If my date field
is filled in, my [00-Selected for Interrator] returns an
#ERROR.

I don't see anything wrong with this code. Does anyone have
any thoughts on this??? Thank you.


This is a circular reference. You have a control on a form called
[00-selected for Interrator], and you are saying that if the date
is filled in, show the contents of the control in itself.

You also have a field in the bound query or table of the form
with the same name: [00-selected for Interrator]. Access names
controls with the name of the field when you use a Wizard or drag
and drop from the fields list. When you modified your control
source, Access got confused.

It's very easy to fix. Either rename the control, or specify the
table name in your function, eg change it to

=IIf(IsNull([09-ReceivedDate])," ",[tablename].[00-Selected
forInterrator])

Where you change [tablename] to what's the table's name.

Bob Q
Nov 12 '05 #4

P: n/a
Perhaps I need to clarify:
The table is being used to track the progress of a document as it
winds its way through a process. When this process is completed, the
admin person fills the date in the field [09-ReceivedDate]. Some of
these records have been pre-selected for back end audit. If a
particular record was selected for audit, then there is text in the
field called [00-Selected for Interrator]. So, when the admin person
notes that a record is closed by inputting a date in the
[09-ReceivedDate] field, it is at this time I want the [00-Selected
for Interrator] field to show.

My iif statement tries to say, that if there is any value in the date
field, then give me the contents of the audit field. If there is no
value in the date field, give me a blank.

It appears to me that the first 2 arguments of the iif statement are
working fine, as I get a blank when there is no text in the
[00-Selected for Interrator] field AND a date value is filled in to
show the record is closed. The problem lies only when there is text in
the audit field AND a dae value is filled in to show the record is
closed. This is when I get the #ERROR.

Thanks again for your help. I hope my explanation helps to clarify the
situation.

no*****@ictis.net (Tym) wrote in message news:<3f***************@169.100.100.5>...
On 18 Dec 2003 11:40:39 -0800, ka********@mmoh.com (kathy) wrote:
Working in a form, I have an Iif statement where if a date is not
filled in, another field will show as a blank. If the date IS filled
in, the 2nd field will show its actual value. The following statement
is in the Control Source of the [00-Selected for Interrator]field
=IIf(IsNull([09-ReceivedDate])," ",[00-Selected for Interrator])

So, the results I get are this: If the date field is blank, my
[00-Selected for Interrator] field is blank. If my date field is
filled in, my [00-Selected for Interrator] returns an #ERROR.

I don't see anything wrong with this code. Does anyone have any
thoughts on this??? Thank you.


I;ve found that "is null" only refers to numeric strings. For testing
strings you need to use

is = ""

what do you mean by "actual value"?

if they fill in a date in [09-ReceivedDate] you "translate" it to a
field which is a date format?

=IIf([09-ReceivedDate]=""," ",CvDate([09-ReceivedDate]))

?

Or am I missing something?

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.