Need to have a Number = to a Name

[ZeoGel]

I have a report that has an 13 digit Number but the First Eight Digits represents a City Name How can I show the Name of the city instead of the number. I have 9 different citys to show.

Iam using an experession to get an accurate average. But when there is a 0 in the Sum The average shows as a "Num#" here is the expression

=Sum([Field])/Abs(Sum([field]<>0))

Zeogel

 

[PaulBricker]

You could put a calculated textbox in the report that uses the DLookUp() function. Something like this.
=DLookUp(&quot;[CityNameField]&quot;,&quot;TableName&quot;,&quot;Left([NumberField],8) = &quot; & NumberFieldOnReport)

Just replace your field names where appropriate.

Which Sum expression gives you the trouble?


Paul

 

[dhookom]

It works best to ask only one question in a thread (unless the questions are related).
1) do you have a table that states which cities have which codes?

2)=IIf(Sum([field]<>0)=0,0,Sum([Field])/Abs(Sum([field]<>0)))


Duane
MS Access MVP

 

[ZeoGel]

Thank you Paul but it didn't work.


Duane, you Expression worked thank you as for your question no I didn't have a table. But tring what Paul had suggested and I created one.

 

[PaulBricker]

Give us a little more info about your table and post the expression you used and tell us where you used it.

Paul