Incorrect Left/Right Parsing Results

[jaaret]

I'm getting some strange update query results when parsing a field in Access 2002. I'm parsing a property tax ID so it can successfully join with the tax ID field in a linked table. For example, an original value in the Kparcel text field is: K0324059002

The code I'm using in the query is:

Parcel: Left(Right([kparcel],10),6) & " " & Right([kparcel],4)

On *most* tax id's it returns the correct result:
032405 9002

But on others it returns 59002 (and six spaces afterwards).

I can't see any pattern to why some results are correct and others are not. The format of the original value is identical for all records. The incorrect results show up in both select and update queries.
Help!

 

[jaaret]

Another clue: When I cut and paste the query results into Excel (pasted as unicode text)the original [kparcel] values for the records that return the odd result will not center or right align. The cells are formatted as text in Excel as well. The records that parse orrectly in Access will center or right align but the ones that parse incorrectly in Access will not.

 

[PHV]

And this ?
Parcel: Mid([Kparcel],2,6) & " " & Mid([Kparcel],8,4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Another thing to try:
Parcel: Left(Right(Trim([kparcel]),10),6) & " " & Right(Trim([kparcel]),4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886