HOw to inslert a formula into a report

  • Thread starter Thread starter W. Guy Delaney
  • Start date Start date
W

W. Guy Delaney

Recently I asked for helpin trying to figure of how to write an llf
statement that will insert the
PO Box number into the Address Line of my Telephone Directory if the Street
Address is blank.

Two people responded. One gave me this foimual:

NZ9[Address], [PO Box])

The other gave me this formula"

llf(Len([Address]>1,[PO Box,[Address])

In both cases, I have inserted the named from my own table.

When I go to the design view of the report I'm trying to create, how do I
insert one or the other of these formulas. When I insert the Address field
and then modify it to be like one of the formulas, I get an error message
when I run the report.
 
Use either expression as the Control Source of a text box:

=Nz([Address], [PO Box])

=IIf(Len([Address])>1,[PO Box],[Address])

Note that I fixed some parentheses, etc. I think the first one is tidier,
assuming you cannot have a zero-length string, which is different from Null.

You could also use the expression in a query. At the top of a blank column
in query design view:

AddressChoice: Nz([Address], [PO Box])
 
I put =Nz([Address], [PO Box]) as the Control Source in a text box and when
I run the report it tells me to enter the parameter value for the PO Box.

BruceM said:
Use either expression as the Control Source of a text box:

=Nz([Address], [PO Box])

=IIf(Len([Address])>1,[PO Box],[Address])

Note that I fixed some parentheses, etc. I think the first one is tidier,
assuming you cannot have a zero-length string, which is different from
Null.

You could also use the expression in a query. At the top of a blank
column in query design view:

AddressChoice: Nz([Address], [PO Box])


W. Guy Delaney said:
Recently I asked for helpin trying to figure of how to write an llf
statement that will insert the
PO Box number into the Address Line of my Telephone Directory if the
Street
Address is blank.

Two people responded. One gave me this foimual:

NZ9[Address], [PO Box])

The other gave me this formula"

llf(Len([Address]>1,[PO Box,[Address])

In both cases, I have inserted the named from my own table.

When I go to the design view of the report I'm trying to create, how do I
insert one or the other of these formulas. When I insert the Address
field and then modify it to be like one of the formulas, I get an error
message when I run the report.
Click to expand...
Click to expand...
 
Both [Address] and [PO Box] need to be fields in the report's Record Source.

W. Guy Delaney said:
I put =Nz([Address], [PO Box]) as the Control Source in a text box and
when I run the report it tells me to enter the parameter value for the PO
Box.

BruceM said:
Use either expression as the Control Source of a text box:

=Nz([Address], [PO Box])

=IIf(Len([Address])>1,[PO Box],[Address])

Note that I fixed some parentheses, etc. I think the first one is
tidier, assuming you cannot have a zero-length string, which is different
from Null.

You could also use the expression in a query. At the top of a blank
column in query design view:

AddressChoice: Nz([Address], [PO Box])


W. Guy Delaney said:
Recently I asked for helpin trying to figure of how to write an llf
statement that will insert the
PO Box number into the Address Line of my Telephone Directory if the
Street
Address is blank.

Two people responded. One gave me this foimual:

NZ9[Address], [PO Box])

The other gave me this formula"

llf(Len([Address]>1,[PO Box,[Address])

In both cases, I have inserted the named from my own table.

When I go to the design view of the report I'm trying to create, how do
I insert one or the other of these formulas. When I insert the Address
field and then modify it to be like one of the formulas, I get an error
message when I run the report.
Click to expand...
Click to expand...
Click to expand...
 
The Record Source is "qryDirectory." In that query, both [Address] and [PO
Box] are present.


BruceM said:
Both [Address] and [PO Box] need to be fields in the report's Record
Source.

W. Guy Delaney said:
I put =Nz([Address], [PO Box]) as the Control Source in a text box and
when I run the report it tells me to enter the parameter value for the PO
Box.

BruceM said:
Use either expression as the Control Source of a text box:

=Nz([Address], [PO Box])

=IIf(Len([Address])>1,[PO Box],[Address])

Note that I fixed some parentheses, etc. I think the first one is
tidier, assuming you cannot have a zero-length string, which is
different from Null.

You could also use the expression in a query. At the top of a blank
column in query design view:

AddressChoice: Nz([Address], [PO Box])


Recently I asked for helpin trying to figure of how to write an llf
statement that will insert the
PO Box number into the Address Line of my Telephone Directory if the
Street
Address is blank.

Two people responded. One gave me this foimual:

NZ9[Address], [PO Box])

The other gave me this formula"

llf(Len([Address]>1,[PO Box,[Address])

In both cases, I have inserted the named from my own table.

When I go to the design view of the report I'm trying to create, how do
I insert one or the other of these formulas. When I insert the Address
field and then modify it to be like one of the formulas, I get an error
message when I run the report.
Click to expand...
Click to expand...
Click to expand...
 
I am so sorry. I have POBox in my table and PO Box in the source code. A
space that should not be there. I'm truly sorry.


BruceM said:
Both [Address] and [PO Box] need to be fields in the report's Record
Source.

W. Guy Delaney said:
I put =Nz([Address], [PO Box]) as the Control Source in a text box and
when I run the report it tells me to enter the parameter value for the PO
Box.

BruceM said:
Use either expression as the Control Source of a text box:

=Nz([Address], [PO Box])

=IIf(Len([Address])>1,[PO Box],[Address])

Note that I fixed some parentheses, etc. I think the first one is
tidier, assuming you cannot have a zero-length string, which is
different from Null.

You could also use the expression in a query. At the top of a blank
column in query design view:

AddressChoice: Nz([Address], [PO Box])


Recently I asked for helpin trying to figure of how to write an llf
statement that will insert the
PO Box number into the Address Line of my Telephone Directory if the
Street
Address is blank.

Two people responded. One gave me this foimual:

NZ9[Address], [PO Box])

The other gave me this formula"

llf(Len([Address]>1,[PO Box,[Address])

In both cases, I have inserted the named from my own table.

When I go to the design view of the report I'm trying to create, how do
I insert one or the other of these formulas. When I insert the Address
field and then modify it to be like one of the formulas, I get an error
message when I run the report.
Click to expand...
Click to expand...
Click to expand...
 
No problem. That would do it. Is it fixed?

W. Guy Delaney said:
I am so sorry. I have POBox in my table and PO Box in the source code. A
space that should not be there. I'm truly sorry.


BruceM said:
Both [Address] and [PO Box] need to be fields in the report's Record
Source.

W. Guy Delaney said:
I put =Nz([Address], [PO Box]) as the Control Source in a text box and
when I run the report it tells me to enter the parameter value for the
PO Box.

"BruceM" <bamoob_at_yawhodotcalm.not> wrote in message
Use either expression as the Control Source of a text box:

=Nz([Address], [PO Box])

=IIf(Len([Address])>1,[PO Box],[Address])

Note that I fixed some parentheses, etc. I think the first one is
tidier, assuming you cannot have a zero-length string, which is
different from Null.

You could also use the expression in a query. At the top of a blank
column in query design view:

AddressChoice: Nz([Address], [PO Box])


Recently I asked for helpin trying to figure of how to write an llf
statement that will insert the
PO Box number into the Address Line of my Telephone Directory if the
Street
Address is blank.

Two people responded. One gave me this foimual:

NZ9[Address], [PO Box])

The other gave me this formula"

llf(Len([Address]>1,[PO Box,[Address])

In both cases, I have inserted the named from my own table.

When I go to the design view of the report I'm trying to create, how
do I insert one or the other of these formulas. When I insert the
Address field and then modify it to be like one of the formulas, I get
an error message when I run the report.
Click to expand...
Click to expand...
Click to expand...
 
W. Guy Delaney said:
Recently I asked for helpin trying to figure of how to write an llf
statement that will insert the
PO Box number into the Address Line of my Telephone Directory if the
Street
Address is blank.

Two people responded. One gave me this foimual:

NZ9[Address], [PO Box])

The other gave me this formula"

llf(Len([Address]>1,[PO Box,[Address])

In both cases, I have inserted the named from my own table.

When I go to the design view of the report I'm trying to create, how do I
insert one or the other of these formulas. When I insert the Address field
and then modify it to be like one of the formulas, I get an error message
when I run the report.
Click to expand...
 
Back
Top