 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / August 2005Convert Field to four digits
I'm dealing with a problem and need the easiest solution as possible. I have several tables which have one field in common, let's call it ProductCode, which is set as "Text" since it use alphanumeric data. The data for the tables is obtained and pasted from Excel, but, when a code starts wiht "0" (zero), the code is shown as 3 digits (0473 is shown as 473). What I need is to have the four digits displayed in the table (0 inclusive), this is because I use the ProductCode to get information from other tables and the other tables always shows the 4 digits, so when I try to find one code with 3 digit only, information is not loaded. By now, what I've used is several (15 for each table) Update Queries to add "0" at the beginning to each possible ProductCode with 3 digits. Is there an easy way to deal with that in one single step or less steps as possible? Your suggestions are welcome. Not sure of any easy way, after-the-fact, however you may want to look at handling the issue from Excel, which is where I believe you are losing your '0'. Excel defaults to the General field data type, which will drop a leading '0' in a field only displaying numeric information. If the column is formatted to Text, the '0' will remain. Good luck. Sharkbyte > I'm dealing with a problem and need the easiest solution as possible. I have > several tables which have one field in common, let's call it ProductCode, [quoted text clipped - 12 lines] > Is there an easy way to deal with that in one single step or less steps as > possible? Your suggestions are welcome. > What I need is to have the four digits displayed in the table (0 > inclusive), this is because I use the ProductCode to get information > from other tables and the other tables always shows the 4 digits, so > when I try to find one code with 3 digit only, information is not > loaded. UPDATE MyTable SET MyAlphanumericField = "0" & MyAlphanumericField WHERE MyAlphanumericField LIKE "[0-9][0-9][0-9]" > The data for the tables is obtained and pasted from Excel, but, when a > code starts wiht "0" (zero), the code is shown as 3 digits (0473 is > shown as 473). In the future, you probably need to control your Excel file much better. Can you use an import specification file to force the field to a Text (somewhere it's being treated as a number)? Can you edit the Excel file to make the values '0471 (note the single quote at the beginning) to force a text value? Etc? Hope that helps Tim F Rather than paste the data from Excel why not import into predefined table structure? > I'm dealing with a problem and need the easiest solution as possible. I have > several tables which have one field in common, let's call it ProductCode, [quoted text clipped - 12 lines] > Is there an easy way to deal with that in one single step or less steps as > possible? Your suggestions are welcome. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.