Re: Problem Importing from an Excel spread sheet to an Access 2003 table.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Casey (anonymous_at_discussions.microsoft.com)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 10:46:51 -0800

Hello John,

Is it possible in Access to format all zip codes to
display 5 digits with a single command?

This would get me away from the problem of importing it
correctly. I would just have to apply a change
afterwards.

If there is a way to format a field with in Access after
the fact would work as well. To have the output change
from what ever is in the Zip code field for data to now be
displayed as 5 digits and 5 digits only.

I have the field property data type currently set to text.
I can change it to what ever it needs to be.

Casey,

>-----Original Message-----
>Hi Casey,
>
>As others have said, it's not possible to do this in one
step. It is
>possible in two stages or by writing custom VBA code, in
any of these
>ways:
>
>1) Modify the Excel sheet before import by prefixing an
apostrophe ' to
>the Zip code field. This can be done manually or under
program control
>from Access via Automation.
>
>2) Import the data to a temporary table, losing the
leading zeros. Then
>use an append query to move the data into a "permanent"
table, with a
>text field for the Zip codes. Use an expression or custom
VBA function
>in a calculated field in the query to restore the leading
zeros.
>
>3) Write VBA code that uses Automation to extract the
data from
>individual cells in the Excel sheet and append it a
record at a time to
>the Access table.
>
>Of these, (2) is probably the simplest.
>
>
>
>On Wed, 17 Mar 2004 09:38:36 -0800, "Casey"
><anonymous@discussions.microsoft.com> wrote:
>
>>
>>Hello,
>>
>> I am not sure if I should be posting this question here!
>> Or in Tables and Database Design. I will start here
since
>> this is a question about importing data.
>>
>> My problem is that when I attempt to import data from
an
>>Excel spread sheet into an Access 2003 table. The first
>>zeros that I have in a zip code field are not getting
>>carried over to the access table.
>>
>> Now I would like to correct this in one shot. I tried
>>setting up a custom imput mask such as \00000\-00000
>>
>> This added a zero to the front of all the zip codes.
>> Not all of my zip code entries start with zero.
>>
>> I just want to copy over what is actually in my excel
>>spread sheet.
>>
>> And perhaps latter automatically add all zeros for the
>>last five digits.
>>
>> For right now how ever. All that there is are five
>>digits.
>>
>> Some start with zero. Some do not.
>>
>> I would really appreaciate some help on this. I keep
>>coming back to this issue over the past couple of weeks.
>>
>> Currently I am no closer to figuring this out from my
>>refrence manual.
>>
>> Thank you,
>>
>> Casey,
>
>--
>John Nurick [Microsoft Access MVP]
>
>Please respond in the newgroup and not by email.
>.
>

Relevant Pages

  • Re: How do I display number field in Access?
    ... "Jeff Boyce" wrote: ... If your input number is preceeded by any number of leading zeros, ... text characters that happen to be digits. ... I am attempting to get the field to display when I go into the Table view ...
    (microsoft.public.access.externaldata)
  • Re: display contents of preceeding cells consecutively
    ... You're *still* going to have a problem since you're working with 16 digits ... the display is more important to me at this point ... If you want leading zeros then you'll have to accept the ... If the resulting string wll be longer than that then ...
    (microsoft.public.excel.misc)
  • Re: How do I display number field in Access?
    ... If your input number is preceeded by any number of leading zeros, ... text characters that happen to be digits. ... is that when Access imports the data, that numbers will not display, I get ...
    (microsoft.public.access.externaldata)
  • Re: view leading zeros
    ... type and display formatting. ... > starting with a zero or two zeros the zeros are not displayed, ... > is it possible to display all 4 digits Inc the leading zeros. ...
    (microsoft.public.access.gettingstarted)
  • Re: How to Force Field Size
    ... will not *store* leading zeros, ... Public Function LeftPadToLength(strVal As String, intLength As Integer, ... I have decided to change my numbers into the 10 digits ... >>End Sub ...
    (microsoft.public.access.modulesdaovba)