Control Source

[Neil Greenough]

I am trying to enter the following in a new unbound text box in a report:

=IIf([PaymentDate] < Date(), "Overdue", Null)

PaymentDate is the exact name of one of my fields. All I did was inserted a
new text box, clicked on properties and then pasted the above statement in
the Control Source.

Nonetheless, when I try this I keep on getting the following error:

"The function you entered can't be used in this expression:

*You may have used a DoEvents, LBound, UBound, Spc or Tab Function in an
expression
*You may have used an SQL aggregate function, such as Count, in a design
grid or in a calculated control or field.

 

[Graham R Seach]

Neil,

1. Open any code module.
2. Select Compile from the Debug menu.
3. Fix whatever error is returned.
4. Repeat Steps 2 & 3 until no more errors are returned.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Neil Greenough]

I have done that Graham and fixed a few, however I am still having no joy.

When I run the report now, I get a pop up box before the report is generated
saying "Oustanding." On another occasion when I tried to fix it, I entered
the code =IIf([PaymentDate] < Date(), "Overdue", Null), however when I went
to review the code, it had been reduced to just , "Overdue", Null),

Neil,

1. Open any code module.
2. Select Compile from the Debug menu.
3. Fix whatever error is returned.
4. Repeat Steps 2 & 3 until no more errors are returned.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I am trying to enter the following in a new unbound text box in a report:

=IIf([PaymentDate] < Date(), "Overdue", Null)

PaymentDate is the exact name of one of my fields. All I did was

inserted

a
new text box, clicked on properties and then pasted the above statement in
the Control Source.

Nonetheless, when I try this I keep on getting the following error:

"The function you entered can't be used in this expression:

*You may have used a DoEvents, LBound, UBound, Spc or Tab Function in an
expression
*You may have used an SQL aggregate function, such as Count, in a design
grid or in a calculated control or field.

 

[Graham R Seach]

Neil,

Where did the word "Outstanding" come from? If you're getting a popup before
the report shows up, there's a problem somewhere else. Fix that first.

You should double-check that [PaymentDate] is actually the name of a control
on the report (and that it's in the same section as the control into which
you're adding the code). You should also check that you don't have multiple
controls with the same name. Finally, double-check that you entered the code
correctly into the ControlSource (by the way, I'd replace the Null with an
empty string [two double-quotes]).
=IIf([PaymentDate] < Date(), "Overdue", "")

I'm not able to reproduce the problem here.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have done that Graham and fixed a few, however I am still having no joy.

When I run the report now, I get a pop up box before the report is generated
saying "Oustanding." On another occasion when I tried to fix it, I entered
the code =IIf([PaymentDate] < Date(), "Overdue", Null), however when I went
to review the code, it had been reduced to just , "Overdue", Null),

Neil,

1. Open any code module.
2. Select Compile from the Debug menu.
3. Fix whatever error is returned.
4. Repeat Steps 2 & 3 until no more errors are returned.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I am trying to enter the following in a new unbound text box in a report:

=IIf([PaymentDate] < Date(), "Overdue", Null)

PaymentDate is the exact name of one of my fields. All I did was

inserted

a
new text box, clicked on properties and then pasted the above

statement

in

 

[Graham R Seach]

Neil sent me his database. I had a look, and this is what I found.

Neil's report contained 5 columns of data, only 3 of which are relevent
here. They consisted a textbox called [PaymentDate] (whose label read "Due
Date"), a checkbox called [Status] (whose label read "Received"), and Neil
wanted an additional textbox (whose name was undefined, yet labelled
"Status") to be added. It was this textbox which should check the value of
both [PaymentDate] and [Status], and display text based on what it found.

The ControlSource for the new textbox (whose default name I left as Text31)
should read as follows:
=IIf(Nz([PaymentDate], Date() + 1) < Date() And Not [Status],
"Outstanding", "")

....which (omitting the [Status] field, since it is a new requirement) is
essentially the same as what was suggested in a previous post.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Neil,

Where did the word "Outstanding" come from? If you're getting a popup before
the report shows up, there's a problem somewhere else. Fix that first.

You should double-check that [PaymentDate] is actually the name of a control
on the report (and that it's in the same section as the control into which
you're adding the code). You should also check that you don't have multiple
controls with the same name. Finally, double-check that you entered the code
correctly into the ControlSource (by the way, I'd replace the Null with an
empty string [two double-quotes]).
=IIf([PaymentDate] < Date(), "Overdue", "")

I'm not able to reproduce the problem here.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have done that Graham and fixed a few, however I am still having no joy.

When I run the report now, I get a pop up box before the report is generated
saying "Oustanding." On another occasion when I tried to fix it, I entered
the code =IIf([PaymentDate] < Date(), "Overdue", Null), however when I went
to review the code, it had been reduced to just , "Overdue", Null),

Neil,

1. Open any code module.
2. Select Compile from the Debug menu.
3. Fix whatever error is returned.
4. Repeat Steps 2 & 3 until no more errors are returned.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I am trying to enter the following in a new unbound text box in a report:

=IIf([PaymentDate] < Date(), "Overdue", Null)

PaymentDate is the exact name of one of my fields. All I did was inserted
a
new text box, clicked on properties and then pasted the above

statement

in
the Control Source.

Nonetheless, when I try this I keep on getting the following error:

"The function you entered can't be used in this expression:

*You may have used a DoEvents, LBound, UBound, Spc or Tab Function

in

an