"Translate" Excel formula into Access

[adam_neb]

I have a formula in excel that changes a 5-digit zip code into a 3-dight zip code:

'=IF(LEN(C2)=5,LEFT(C2,3),IF(LEN(C2)=4,CONCATENATE("0",LEFT(C2,2)),""))

Where c2 is Field Name CUCODS.

I need to take the CUCODS (zip code field) in the table and get the corresponding 3 digit zip code.

Some records may have a 4 digit zip code, but there is a leading 0 that is not showing ex: Springfield, MA is zip 01101, but may show 1101. I need the output to recognize that the three digit zip is 011 NOT 110.

Any help appreciated. My data set is too large for my excel formual to work 250,000+ records.

Adam

 

[buckeye_td]

=IIF(LEN([CUCODS])=5,LEFT([CUCODS],3),IIF(LEN([CUCODS])=4, "0"& LEFT([CUCODS],2),""))

TD

 

[adam_neb]

Thanks for the help. Now I have one more issue to work around: When I enter the formula you gave, I get the following error:

"You tried to execute a query that does not include the specified expression "IIf(Len([CUCODS])=5,Left([CUCODS],3),IIf(Len([CUCODS])=4,"0" & Left([CUCODS],2),""))' as part of an aggregate function.

What does that mean, and how do I get this to fire up and work?

Thanks again, Adam.

 

[Cbrine]

Make sure you have totals option set to groupby and do not have a sort on this field.

HTH
Cal

 

[adam_neb]

Cal:

I changed the total field to Group By and Ran the query, and this time I did not get the error code. However, in the the datasheet view the column is blank! Arrg! Is this a number format/text format issue?My orignal table has that field a text data type.
Adam

 

[Andrew Fergus]

Hi Adam

A couple of questions :
Is this a update query or a select query? (An update query will not show the query results)
What results does your query show if you change the formula to something like this :

IIf(Len([CUCODS])=5,Left([CUCODS],3),"0" & Left([CUCODS],2))

I suspect the 2nd else condition is being "tripped" in your original formula hence nothing is showing. You might want to look at the raw data in the CUCODS field - in particular what is the length of the field? Test it with this in your query :

Len([CUCODS])

You might find there are some trailing spaces or other characters that results in the length of [CUCODS] being a value other than 4 or 5.

HTH, Andrew.