IF query in report

[Neil Greenough]

I have a database report listing payments which are due to be received. The
listed items are company which needs to pay, amount, due date and a tick box
for if received.

Now what I would like to create is another column. If the current date is
passed the due date, then I would like the word "overdue" to appear in a
final column.

How would I go about creating this? Can I create it in the actual report?

Could people please give me a dummies guide to doing this as I am not all
that 'au fait' with Access.

 

[Allen Browne]

Open your report in design view.

Add another text box to the report, and set its Control Source property to:
=IIf([Due Date] < Date(), "Overdue", Null)

 

[fredg]

I have a database report listing payments which are due to be received. The
listed items are company which needs to pay, amount, due date and a tick box
for if received.

Now what I would like to create is another column. If the current date is
passed the due date, then I would like the word "overdue" to appear in a
final column.

How would I go about creating this? Can I create it in the actual report?

Could people please give me a dummies guide to doing this as I am not all
that 'au fait' with Access.

You could do this directly in the report.
Add an unbound control to the report.
Set it's Control source to:
=IIf(Date()>[Due Date],"Overdue","")

 

[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.

I have a database report listing payments which are due to be received. The
listed items are company which needs to pay, amount, due date and a tick box
for if received.

Now what I would like to create is another column. If the current date is
passed the due date, then I would like the word "overdue" to appear in a
final column.

How would I go about creating this? Can I create it in the actual report?

Could people please give me a dummies guide to doing this as I am not all
that 'au fait' with Access.

You could do this directly in the report.
Add an unbound control to the report.
Set it's Control source to:
=IIf(Date()>[Due Date],"Overdue","")

 

[Brendan Reynolds]

Are you *sure* it is that expression and not any other that is causing the
error? I just tried something very similar, the only difference is that I
was using the Orders table from the Northwind sample database for testing,
so I used the OrderDate field in place of your PaymentDate field ...

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

This works for me.

What if you delete that textbox from the report. Does the error go away?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

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.

I have a database report listing payments which are due to be received. The
listed items are company which needs to pay, amount, due date and a
tick box
for if received.

Now what I would like to create is another column. If the current date is
passed the due date, then I would like the word "overdue" to appear in
a
final column.

How would I go about creating this? Can I create it in the actual report?

Could people please give me a dummies guide to doing this as I am not all
that 'au fait' with Access.

You could do this directly in the report.
Add an unbound control to the report.
Set it's Control source to:
=IIf(Date()>[Due Date],"Overdue","")

 

[Neil Greenough]

Yes, if I delete the text box from the report, the error goes away.

When I type in the formula and then click on another tab in the properties
box, I get a message about a (comma) error.

Are you *sure* it is that expression and not any other that is causing the
error? I just tried something very similar, the only difference is that I
was using the Orders table from the Northwind sample database for testing,
so I used the OrderDate field in place of your PaymentDate field ...

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

This works for me.

What if you delete that textbox from the report. Does the error go away?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

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.

On Thu, 7 Oct 2004 15:48:23 +0000 (UTC), Neil Greenough wrote:

I have a database report listing payments which are due to be

received.
The

listed items are company which needs to pay, amount, due date and a
tick box
for if received.

Now what I would like to create is another column. If the current

date
is

passed the due date, then I would like the word "overdue" to appear in
a
final column.

How would I go about creating this? Can I create it in the actual report?

Could people please give me a dummies guide to doing this as I am not all
that 'au fait' with Access.

You could do this directly in the report.
Add an unbound control to the report.
Set it's Control source to:
=IIf(Date()>[Due Date],"Overdue","")

 

[Allen Browne]

Neil, what exactly does that message say?

Is is possible that you have a different character defined as your list
separator?
This is defined in the Windows Control Panel, under Regional Options.

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

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

Yes, if I delete the text box from the report, the error goes away.

When I type in the formula and then click on another tab in the properties
box, I get a message about a (comma) error.

Are you *sure* it is that expression and not any other that is causing
the
error? I just tried something very similar, the only difference is that I
was using the Orders table from the Northwind sample database for
testing,
so I used the OrderDate field in place of your PaymentDate field ...

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

This works for me.

What if you delete that textbox from the report. Does the error go away?

 

[Brendan Reynolds]

See Allen's response elsewhere in this thread. It could be that your system
is configured to use a different list separator character (the semi-colon is
used in many continental European countries) but it is difficult to be sure
without knowing the exact error message.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Yes, if I delete the text box from the report, the error goes away.

When I type in the formula and then click on another tab in the properties
box, I get a message about a (comma) error.

Are you *sure* it is that expression and not any other that is causing
the
error? I just tried something very similar, the only difference is that I
was using the Orders table from the Northwind sample database for
testing,
so I used the OrderDate field in place of your PaymentDate field ...

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

This works for me.

What if you delete that textbox from the report. Does the error go away?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

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.





On Thu, 7 Oct 2004 15:48:23 +0000 (UTC), Neil Greenough wrote:

I have a database report listing payments which are due to be received.
The
listed items are company which needs to pay, amount, due date and a
tick
box
for if received.

Now what I would like to create is another column. If the current date
is
passed the due date, then I would like the word "overdue" to appear in
a
final column.

How would I go about creating this? Can I create it in the actual
report?

Could people please give me a dummies guide to doing this as I am
not
all
that 'au fait' with Access.

You could do this directly in the report.
Add an unbound control to the report.
Set it's Control source to:
=IIf(Date()>[Due Date],"Overdue","")