Type Mismatch in Expression with text fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create an expression that will multiply one field times one of
several fields depending on a text criteria. I've checked all my table and
the join fields are all identical in their formatting. Here is the formula I
currently have (sorry it's so long), if anyone could help me figure out where
the problem is, I'd appreciate it! Thank you!!!!!!!!!

NewMils Rev: (([Master Data]![Avg Revenue])*IIf([Master Data]![Eff
Date]="Jan",[Volumes]![NewJan],IIf([Master Data]![Eff
Date]="Feb",[Volumes]![NewFeb],IIf([Master Data]![Eff
Date]="Mar",[Volumes]![NewMar],IIf([Master Data]![Eff
Date]="Apr",[Volumes]![NewApr],IIf([Master Data]![Eff
Date]="May",[Volumes]![NewMay],IIf([Master Data]![Eff
Date]="Jun",[Volumes]![NewJun],IIf([Master Data]![Eff
Date]="Jul",[Volumes]![NewJul],IIf([Master Data]![Eff
Date]="Aug",[Volumes]![NewAug],IIf([Master Data]![Eff
Date]="Sep",[Volumes]![NewSep],IIf([Master Data]![Eff
Date]="Oct",[Volumes]![NewOct],IIf([Master Data]![Eff
Date]="Nov",[Volumes]![NewNov],[Volumes]![NewDec]))))))))))))/1000000
 
Check these data types in your table design:
Avg Revenue - Numeric
Eff Date - Text
NewJan through NewDec - Numeric
Also, make sure none of the fields contain Null values.

Your structure sure resembles a spreadsheet. Have you considered
normalizing?
 
What is the field type of this fields
[Master Data]![Avg Revenue] Should be number
[Master Data]![Eff Date] Should be text
[Volumes]![NewJan] Up to [Volumes]![NewNov] Should be number
[Volumes]![NewDec] should be number
 
[Master Data]![Avg Revenue] is Number-field size double
[Master Data]![Eff Date] is Text - field size 255
[Volumes]![NewJan] through [Volumes]![NewDec] is Number - field size double

Avg Revenue and Eff Date do not contain null values, but [NewJan] through
[NewDec] could be but not in the month that matches up. Would this make a
difference?

The tables are linked based on product and year, and the for the combination
of product/year the value in [NewJan] through [NewDec] will not be null for
the given eff date.

Ofer said:
What is the field type of this fields
[Master Data]![Avg Revenue] Should be number
[Master Data]![Eff Date] Should be text
[Volumes]![NewJan] Up to [Volumes]![NewNov] Should be number
[Volumes]![NewDec] should be number


bbishop222 said:
I'm trying to create an expression that will multiply one field times one of
several fields depending on a text criteria. I've checked all my table and
the join fields are all identical in their formatting. Here is the formula I
currently have (sorry it's so long), if anyone could help me figure out where
the problem is, I'd appreciate it! Thank you!!!!!!!!!

NewMils Rev: (([Master Data]![Avg Revenue])*IIf([Master Data]![Eff
Date]="Jan",[Volumes]![NewJan],IIf([Master Data]![Eff
Date]="Feb",[Volumes]![NewFeb],IIf([Master Data]![Eff
Date]="Mar",[Volumes]![NewMar],IIf([Master Data]![Eff
Date]="Apr",[Volumes]![NewApr],IIf([Master Data]![Eff
Date]="May",[Volumes]![NewMay],IIf([Master Data]![Eff
Date]="Jun",[Volumes]![NewJun],IIf([Master Data]![Eff
Date]="Jul",[Volumes]![NewJul],IIf([Master Data]![Eff
Date]="Aug",[Volumes]![NewAug],IIf([Master Data]![Eff
Date]="Sep",[Volumes]![NewSep],IIf([Master Data]![Eff
Date]="Oct",[Volumes]![NewOct],IIf([Master Data]![Eff
Date]="Nov",[Volumes]![NewNov],[Volumes]![NewDec]))))))))))))/1000000
Click to expand...
Click to expand...
 
The type are OK, you need to take of fields that have null values, just incase.
other wise it will give you Null instead of adding up.
So before every value add nz([Volumes]![NewJan],0)

Now you mantioned someting about "The tables are linked" so are the fields
you link the tables with, are they the same type?

bbishop222 said:
[Master Data]![Avg Revenue] is Number-field size double
[Master Data]![Eff Date] is Text - field size 255
[Volumes]![NewJan] through [Volumes]![NewDec] is Number - field size double

Avg Revenue and Eff Date do not contain null values, but [NewJan] through
[NewDec] could be but not in the month that matches up. Would this make a
difference?

The tables are linked based on product and year, and the for the combination
of product/year the value in [NewJan] through [NewDec] will not be null for
the given eff date.

Ofer said:
What is the field type of this fields
[Master Data]![Avg Revenue] Should be number
[Master Data]![Eff Date] Should be text
[Volumes]![NewJan] Up to [Volumes]![NewNov] Should be number
[Volumes]![NewDec] should be number


bbishop222 said:
I'm trying to create an expression that will multiply one field times one of
several fields depending on a text criteria. I've checked all my table and
the join fields are all identical in their formatting. Here is the formula I
currently have (sorry it's so long), if anyone could help me figure out where
the problem is, I'd appreciate it! Thank you!!!!!!!!!

NewMils Rev: (([Master Data]![Avg Revenue])*IIf([Master Data]![Eff
Date]="Jan",[Volumes]![NewJan],IIf([Master Data]![Eff
Date]="Feb",[Volumes]![NewFeb],IIf([Master Data]![Eff
Date]="Mar",[Volumes]![NewMar],IIf([Master Data]![Eff
Date]="Apr",[Volumes]![NewApr],IIf([Master Data]![Eff
Date]="May",[Volumes]![NewMay],IIf([Master Data]![Eff
Date]="Jun",[Volumes]![NewJun],IIf([Master Data]![Eff
Date]="Jul",[Volumes]![NewJul],IIf([Master Data]![Eff
Date]="Aug",[Volumes]![NewAug],IIf([Master Data]![Eff
Date]="Sep",[Volumes]![NewSep],IIf([Master Data]![Eff
Date]="Oct",[Volumes]![NewOct],IIf([Master Data]![Eff
Date]="Nov",[Volumes]![NewNov],[Volumes]![NewDec]))))))))))))/1000000
Click to expand...
Click to expand...
Click to expand...
 
That worked - Thank you!

Ofer said:
The type are OK, you need to take of fields that have null values, just incase.
other wise it will give you Null instead of adding up.
So before every value add nz([Volumes]![NewJan],0)

Now you mantioned someting about "The tables are linked" so are the fields
you link the tables with, are they the same type?

bbishop222 said:
[Master Data]![Avg Revenue] is Number-field size double
[Master Data]![Eff Date] is Text - field size 255
[Volumes]![NewJan] through [Volumes]![NewDec] is Number - field size double

Avg Revenue and Eff Date do not contain null values, but [NewJan] through
[NewDec] could be but not in the month that matches up. Would this make a
difference?

The tables are linked based on product and year, and the for the combination
of product/year the value in [NewJan] through [NewDec] will not be null for
the given eff date.

Ofer said:
What is the field type of this fields
[Master Data]![Avg Revenue] Should be number
[Master Data]![Eff Date] Should be text
[Volumes]![NewJan] Up to [Volumes]![NewNov] Should be number
[Volumes]![NewDec] should be number


:

I'm trying to create an expression that will multiply one field times one of
several fields depending on a text criteria. I've checked all my table and
the join fields are all identical in their formatting. Here is the formula I
currently have (sorry it's so long), if anyone could help me figure out where
the problem is, I'd appreciate it! Thank you!!!!!!!!!

NewMils Rev: (([Master Data]![Avg Revenue])*IIf([Master Data]![Eff
Date]="Jan",[Volumes]![NewJan],IIf([Master Data]![Eff
Date]="Feb",[Volumes]![NewFeb],IIf([Master Data]![Eff
Date]="Mar",[Volumes]![NewMar],IIf([Master Data]![Eff
Date]="Apr",[Volumes]![NewApr],IIf([Master Data]![Eff
Date]="May",[Volumes]![NewMay],IIf([Master Data]![Eff
Date]="Jun",[Volumes]![NewJun],IIf([Master Data]![Eff
Date]="Jul",[Volumes]![NewJul],IIf([Master Data]![Eff
Date]="Aug",[Volumes]![NewAug],IIf([Master Data]![Eff
Date]="Sep",[Volumes]![NewSep],IIf([Master Data]![Eff
Date]="Oct",[Volumes]![NewOct],IIf([Master Data]![Eff
Date]="Nov",[Volumes]![NewNov],[Volumes]![NewDec]))))))))))))/1000000
Click to expand...
Click to expand...
Click to expand...
 
What was the problem, the null or the linked field were different type, or
anything

bbishop222 said:
That worked - Thank you!

Ofer said:
The type are OK, you need to take of fields that have null values, just incase.
other wise it will give you Null instead of adding up.
So before every value add nz([Volumes]![NewJan],0)

Now you mantioned someting about "The tables are linked" so are the fields
you link the tables with, are they the same type?

bbishop222 said:
[Master Data]![Avg Revenue] is Number-field size double
[Master Data]![Eff Date] is Text - field size 255
[Volumes]![NewJan] through [Volumes]![NewDec] is Number - field size double

Avg Revenue and Eff Date do not contain null values, but [NewJan] through
[NewDec] could be but not in the month that matches up. Would this make a
difference?

The tables are linked based on product and year, and the for the combination
of product/year the value in [NewJan] through [NewDec] will not be null for
the given eff date.

:

What is the field type of this fields
[Master Data]![Avg Revenue] Should be number
[Master Data]![Eff Date] Should be text
[Volumes]![NewJan] Up to [Volumes]![NewNov] Should be number
[Volumes]![NewDec] should be number


:

I'm trying to create an expression that will multiply one field times one of
several fields depending on a text criteria. I've checked all my table and
the join fields are all identical in their formatting. Here is the formula I
currently have (sorry it's so long), if anyone could help me figure out where
the problem is, I'd appreciate it! Thank you!!!!!!!!!

NewMils Rev: (([Master Data]![Avg Revenue])*IIf([Master Data]![Eff
Date]="Jan",[Volumes]![NewJan],IIf([Master Data]![Eff
Date]="Feb",[Volumes]![NewFeb],IIf([Master Data]![Eff
Date]="Mar",[Volumes]![NewMar],IIf([Master Data]![Eff
Date]="Apr",[Volumes]![NewApr],IIf([Master Data]![Eff
Date]="May",[Volumes]![NewMay],IIf([Master Data]![Eff
Date]="Jun",[Volumes]![NewJun],IIf([Master Data]![Eff
Date]="Jul",[Volumes]![NewJul],IIf([Master Data]![Eff
Date]="Aug",[Volumes]![NewAug],IIf([Master Data]![Eff
Date]="Sep",[Volumes]![NewSep],IIf([Master Data]![Eff
Date]="Oct",[Volumes]![NewOct],IIf([Master Data]![Eff
Date]="Nov",[Volumes]![NewNov],[Volumes]![NewDec]))))))))))))/1000000
Click to expand...
Click to expand...
Click to expand...
 
Back
Top