Linked Field Format 1

[palnud]

I'm importing a text field, and the negative numbers are in parenthesis. They come into the table field as an error, #Num!. How do handle this?

Thanks.

 

[WildHare]

Yes, it's odd that Access can't recognize an Excel number format like (1234.56) as a negative, but, that's what makes life interesting.

You'll need to import the field as pure text, then run an update query to strip off the parentheticals and add a dash to the front and then you can go in to table design to convert the field back to a Number datatype.

The tricky part is stripping off the two parens. I'll assume that they are the FIRST and LAST characters of the field..

Your update query will need a function like this:

MyNumber = "-" & Mid(MyNumber, 2, Len(MyNumber)-1)

Before: MyNumber = (1234.56)
After: MyNumber = -1234.56

Place the above expression in the UPDATE TO line of your query, and you should be set.






Remember, you're unique - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[palnud]

Perfect! Thanks for your timely response.