Expression in a report

  • Thread starter Thread starter Joanne
  • Start date Start date
J

Joanne

I have a cheese database that I need to create an expression in a report to
calculate the Average Moisture for only Vats 1-12 and then the remaining Vats
13-??. Can anyone help me? I can get it to average all of the vats
moistures but not 1-12.
 
Assuming the value of Vat is a number field then the expression would be

=AVG(IIF(Vat>=1 and Vat<=12,Moisture,Null))

=AVG(IIF(Vat>12,Moisture,Null))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
The value is a number but the table that the value comes from says text. I
tried the formula and it averages all of my vats??
 
I got that expression to work. Now I need help with another expression. I
need it to say Summary for Vats 13-??. I know the one for vats 1-12 but
since there is always a different number of total vats I don't know the
expression for vats 13-??

For Vats 1-12 I used ="Summary for Vats 1-12"
 
Thanks that worked. I have one more expression I am having problems with.
In the same report I have 6 different products but 2 of the products need to
say something different just for this report. For example if I have product
names one, two, three, four, five, and six. I need Product five to read as
product one and product six needs to read as product two in this report only.
I was thinking something like this, but I can't get it to work??

IIf([Product]="five" or "Six", "One" or "Two", [Product])

Any suggestions?

Duane Hookom said:
Try a text box with a control source like:
="Summary for Vats 13-" & Max([Vat])

--
Duane Hookom
Microsoft Access MVP


Joanne said:
I got that expression to work. Now I need help with another expression. I
need it to say Summary for Vats 13-??. I know the one for vats 1-12 but
since there is always a different number of total vats I don't know the
expression for vats 13-??

For Vats 1-12 I used ="Summary for Vats 1-12"
Click to expand...
Click to expand...
 
I would probably attempt to store this type of information in your table(s)
rather than hard-coding product names inside expressions. Your products are
bound to change some time and you shouldn't have to edit an expression. You
should edit data in tables.

However try something like:
IIf([Product]="five","One",IIf([Product]= "Six","Two", [Product]))

--
Duane Hookom
Microsoft Access MVP


Joanne said:
Thanks that worked. I have one more expression I am having problems with.
In the same report I have 6 different products but 2 of the products need to
say something different just for this report. For example if I have product
names one, two, three, four, five, and six. I need Product five to read as
product one and product six needs to read as product two in this report only.
I was thinking something like this, but I can't get it to work??

IIf([Product]="five" or "Six", "One" or "Two", [Product])

Any suggestions?

Duane Hookom said:
Try a text box with a control source like:
="Summary for Vats 13-" & Max([Vat])

--
Duane Hookom
Microsoft Access MVP


Joanne said:
I got that expression to work. Now I need help with another expression. I
need it to say Summary for Vats 13-??. I know the one for vats 1-12 but
since there is always a different number of total vats I don't know the
expression for vats 13-??

For Vats 1-12 I used ="Summary for Vats 1-12"

:

The value is a number but the table that the value comes from says text. I
tried the formula and it averages all of my vats??

:

Assuming the value of Vat is a number field then the expression would be

=AVG(IIF(Vat>=1 and Vat<=12,Moisture,Null))

=AVG(IIF(Vat>12,Moisture,Null))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Joanne wrote:
I have a cheese database that I need to create an expression in a report to
calculate the Average Moisture for only Vats 1-12 and then the remaining Vats
13-??. Can anyone help me? I can get it to average all of the vats
moistures but not 1-12.
Click to expand...
Click to expand...
Click to expand...
 
Okay thanks.
Duane Hookom said:
I would probably attempt to store this type of information in your table(s)
rather than hard-coding product names inside expressions. Your products are
bound to change some time and you shouldn't have to edit an expression. You
should edit data in tables.

However try something like:
IIf([Product]="five","One",IIf([Product]= "Six","Two", [Product]))

--
Duane Hookom
Microsoft Access MVP


Joanne said:
Thanks that worked. I have one more expression I am having problems with.
In the same report I have 6 different products but 2 of the products need to
say something different just for this report. For example if I have product
names one, two, three, four, five, and six. I need Product five to read as
product one and product six needs to read as product two in this report only.
I was thinking something like this, but I can't get it to work??

IIf([Product]="five" or "Six", "One" or "Two", [Product])

Any suggestions?

Duane Hookom said:
Try a text box with a control source like:
="Summary for Vats 13-" & Max([Vat])

--
Duane Hookom
Microsoft Access MVP


:

I got that expression to work. Now I need help with another expression. I
need it to say Summary for Vats 13-??. I know the one for vats 1-12 but
since there is always a different number of total vats I don't know the
expression for vats 13-??

For Vats 1-12 I used ="Summary for Vats 1-12"

:

The value is a number but the table that the value comes from says text. I
tried the formula and it averages all of my vats??

:

Assuming the value of Vat is a number field then the expression would be

=AVG(IIF(Vat>=1 and Vat<=12,Moisture,Null))

=AVG(IIF(Vat>12,Moisture,Null))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Joanne wrote:
I have a cheese database that I need to create an expression in a report to
calculate the Average Moisture for only Vats 1-12 and then the remaining Vats
13-??. Can anyone help me? I can get it to average all of the vats
moistures but not 1-12.
Click to expand...
Click to expand...
Click to expand...
 
How can I delete 2 products from the table without messing up my form where I
enter the product type (all the records that had those 2 product type should
now be one of the other product types now) Would I need to manually go
through and change them in the entry form?

Joanne said:
Okay thanks.
Duane Hookom said:
I would probably attempt to store this type of information in your table(s)
rather than hard-coding product names inside expressions. Your products are
bound to change some time and you shouldn't have to edit an expression. You
should edit data in tables.

However try something like:
IIf([Product]="five","One",IIf([Product]= "Six","Two", [Product]))

--
Duane Hookom
Microsoft Access MVP


Joanne said:
Thanks that worked. I have one more expression I am having problems with.
In the same report I have 6 different products but 2 of the products need to
say something different just for this report. For example if I have product
names one, two, three, four, five, and six. I need Product five to read as
product one and product six needs to read as product two in this report only.
I was thinking something like this, but I can't get it to work??

IIf([Product]="five" or "Six", "One" or "Two", [Product])

Any suggestions?

:

Try a text box with a control source like:
="Summary for Vats 13-" & Max([Vat])

--
Duane Hookom
Microsoft Access MVP


:

I got that expression to work. Now I need help with another expression. I
need it to say Summary for Vats 13-??. I know the one for vats 1-12 but
since there is always a different number of total vats I don't know the
expression for vats 13-??

For Vats 1-12 I used ="Summary for Vats 1-12"

:

The value is a number but the table that the value comes from says text. I
tried the formula and it averages all of my vats??

:

Assuming the value of Vat is a number field then the expression would be

=AVG(IIF(Vat>=1 and Vat<=12,Moisture,Null))

=AVG(IIF(Vat>12,Moisture,Null))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Joanne wrote:
I have a cheese database that I need to create an expression in a report to
calculate the Average Moisture for only Vats 1-12 and then the remaining Vats
13-??. Can anyone help me? I can get it to average all of the vats
moistures but not 1-12.
Click to expand...
Click to expand...
Click to expand...
 
Start a new thread probably in a more appropriate news group and a better
description of what you want to do.
--
Duane Hookom
Microsoft Access MVP


Joanne said:
How can I delete 2 products from the table without messing up my form where I
enter the product type (all the records that had those 2 product type should
now be one of the other product types now) Would I need to manually go
through and change them in the entry form?

Joanne said:
Okay thanks.
Duane Hookom said:
I would probably attempt to store this type of information in your table(s)
rather than hard-coding product names inside expressions. Your products are
bound to change some time and you shouldn't have to edit an expression. You
should edit data in tables.

However try something like:
IIf([Product]="five","One",IIf([Product]= "Six","Two", [Product]))

--
Duane Hookom
Microsoft Access MVP


:

Thanks that worked. I have one more expression I am having problems with.
In the same report I have 6 different products but 2 of the products need to
say something different just for this report. For example if I have product
names one, two, three, four, five, and six. I need Product five to read as
product one and product six needs to read as product two in this report only.
I was thinking something like this, but I can't get it to work??

IIf([Product]="five" or "Six", "One" or "Two", [Product])

Any suggestions?

:

Try a text box with a control source like:
="Summary for Vats 13-" & Max([Vat])

--
Duane Hookom
Microsoft Access MVP


:

I got that expression to work. Now I need help with another expression. I
need it to say Summary for Vats 13-??. I know the one for vats 1-12 but
since there is always a different number of total vats I don't know the
expression for vats 13-??

For Vats 1-12 I used ="Summary for Vats 1-12"

:

The value is a number but the table that the value comes from says text. I
tried the formula and it averages all of my vats??

:

Assuming the value of Vat is a number field then the expression would be

=AVG(IIF(Vat>=1 and Vat<=12,Moisture,Null))

=AVG(IIF(Vat>12,Moisture,Null))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Joanne wrote:
I have a cheese database that I need to create an expression in a report to
calculate the Average Moisture for only Vats 1-12 and then the remaining Vats
13-??. Can anyone help me? I can get it to average all of the vats
moistures but not 1-12.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top