RE: Split Data within a column.
From: AndrewT. (AndrewT_at_discussions.microsoft.com)
Date: 03/09/05
- Next message: Michel Walsh: "Re: Not counting duplicates"
- Previous message: Sunny: "Re: Format Number Expression"
- In reply to: Ted Allen: "RE: Split Data within a column."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 9 Mar 2005 09:37:13 -0800
UPDATE smada001 SET Zip_4 =Mid(Zip_10,7)
Wow, it worked! Thanks.
I tried alot of different combinations of the queries I found on this site
(none worked)
It also left the cell blank if there wasn't data after the hyphen. Just
what I wanted. The Access help program is not very helpful. I couldn't find
what "Len" or "InStru" or "Mid" means or when to use them. Not very good
examples given.
Thanks again. I wasted hours trying to figure it out on my own.
Andy.
"Ted Allen" wrote:
> Hi Andrew,
>
> Try setting it equal to Mid(Zip_10,7). Mid will return a zero length string
> if the start position is greater than the length of the string, and it will
> return all characters to the right of the start point if the length is not
> entered (optional third argument, which you could enter as 4 if you want). I
> think that should do what you are looking for.
>
> HTH, Ted Allen
>
> "AndrewT." wrote:
>
> > I have a database with 146,000 records. I have a field named Zip_10 (text).
> > It
> > contains zipcodes with and without zip+4. the data is entered as follows:
> >
> > 48006
> > 48123-0021
> > 48323-0989
> > 48123
> > 48945-2321
> > All the records with a zip4 have a hypen. I need to make one column zip
> > (first 5 digits) and one column zip4 with the digits past the hypen.
> > I have created a new column called Zip4 but it is empty. I am below NOVICE
> > level on this stuff. I can do it in excel by using the text to column
> > command but my full database will not load. I truncates at 65,000 records or
> > so.
> >
> > UPDATE MyTable
> > SET Zip_4 = Right$(Zip_10, Len(Zip_10) - (Instr(Zip_10, "- "),
> > Zip5 = Left$(Zip_10, Instr(Zip_10, "- ")
> >
> > Any help? The above doesn't work and I got It off this site. (I change the
> > names to fit my data).
> >
- Next message: Michel Walsh: "Re: Not counting duplicates"
- Previous message: Sunny: "Re: Format Number Expression"
- In reply to: Ted Allen: "RE: Split Data within a column."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
