"Translate" Excel formula into Access

adam_neb

Board Regular
Joined
Jun 9, 2002
Messages
101
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

buckeye_td

New Member
Joined
Dec 1, 2004
Messages
16
=IIF(LEN([CUCODS])=5,LEFT([CUCODS],3),IIF(LEN([CUCODS])=4, "0"& LEFT([CUCODS],2),""))

TD
 

adam_neb

Board Regular
Joined
Jun 9, 2002
Messages
101
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Make sure you have totals option set to groupby and do not have a sort on this field.

HTH
Cal
 

adam_neb

Board Regular
Joined
Jun 9, 2002
Messages
101
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,437
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. :)
 

Forum statistics

Threads
1,147,665
Messages
5,742,502
Members
423,734
Latest member
123hmMission

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top