Expression in a report

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

 

[John Spencer MVP]

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]

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??

 

[Joanne]

I got it to work.

Thank you!!

 

[Joanne]

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"

 

[Duane Hookom]

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

 

[Joanne]

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"

 

[Duane Hookom]

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.

 

[Joanne]

Okay thanks.

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.

 

[Joanne]

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?

Okay thanks.

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.

 

[Duane Hookom]

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

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?

Okay thanks.

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.