Results 1 to 8 of 8
  1. #1
    chmeee_ab_cdn is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    4

    MS-Access 2010 - Permanently set default to Leading Zeros in Dates - Is it possible?

    I recently moved from Access 2003 to Access 2010. Frequently (about 20 times
    per week) I import .CSV files from several different data sources. One of the fields
    is a date, stored as text with a leading zero.

    In Access 2003, I had no trouble as the text appended to an empty copy of the
    table where all fields were defined as Text.

    In Access 2010, even though the table has all fields defined as text, the


    checkbox for Leading Zeros in Dates must be ticked EACH TIME or Access will drop
    the zero.

    I've looked through Options and browsed through the registry but have found
    nothing that suggests a permanent default.

    Has anyone discovered a way to set this ON permanently?

    Thanks for your attention.
    -C

    Windows 8 Pro with Media Centre, MS-Access 2010 - 64 bit


    (Screenshot.jpg)
    ---

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Maybe use an import specification http://office.microsoft.com/en-us/ac...101836402.aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    chmeee_ab_cdn is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    4
    Thanks, but I thought of that. The .CSV files have multiple
    layouts and names change constantly. Unless I've approached
    it all wrong, I would have to maintain many specs and blank
    tables, then rename each table to match the filename after
    each import. I will if I have to, but the method I've used has
    worked since Access 97 (95?) and has only failed me now
    with 2010.

    Just looking for the most efficient way to get it working the
    same way it did before.

    Thanks again for taking time.
    -C

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Confused, if the CSV files are constantly changing, how do you import to the same table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    chmeee_ab_cdn is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    4
    Let's say last week I had a file "Week2.mdb" with several imports
    in it. I would import a table (Definition Only) from the matching
    type to my "Week3.mdb" file that matched the current CSV. I would
    then rename it and import the CSV to append to the empty table.
    In previous versions I would not need a spec, or have to check the
    "Leading Zeros" box each time.

    In 2010 it looks like I could keep a reference MDB with empty tables
    & matching specs, and import each pair (table & spec) as I needed
    them. It just seems like a whole lot of extra steps when it would
    be so simple to have an option to set "Leading Zeros In Dates" to
    be permanently ON.

    -C

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    There are always risks with upgrades. Far as I know, there is no way to modify any wizard defaults.

    Still confused. If you are using established tables as templates for the CSV imports, then structure of the CSV files are known, it's the filenames that change. According to the referenced link, can select a different source file during the import operation.

    There would be a one-time investment to create the various specifications. First need to verify that specification will save the checked Leading Zeros parameter.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I recently moved from Access 2003 to Access 2010. Frequently (about 20 times
    per week) I import .CSV files from several different data sources.
    If I was importing CSV filed that many times per week, I would set up the import specs and have VBA code to select the CSV file name, then do the import, using the import spec.

    Or I would use VBA to select the CSV file, open the file, loop through the lines in the CSV file, parsing the fields as necessary.


    My $0.02.....

  8. #8
    chmeee_ab_cdn is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    4
    Thanks Steve & June7 for taking time to look at this. As I
    expressed before, I will try (again) to work out a system
    using the specs to handle my workflow, but after doing this
    for 18 years, I have yet to build a process for the diverse
    bunch of files I get that produces results I trust.

    I act as a service bureau for over 60 data collection
    organizations to which the term "herding cats" would apply
    very aptly. Also, our central SQL database (3rd party)
    has export specs that vary from compliance with ISO 8601
    date format to just about any date format you can imagine.
    Standardizing this IS ongoing, but glacial in it's pace.

    I repeat, I will try the specs again, but what I would really
    prefer is an option (or registry hack) to permanently make
    Access 2010 handle leading zeros in dates in the same way
    it has for all it's previous versions.

    Thanks again for your time.
    -C

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Trying to get leading zeros to show
    By Pegasus_Angel in forum Access
    Replies: 3
    Last Post: 10-25-2012, 11:40 AM
  2. Leading Zeros in Access 2010
    By aa_weber in forum Access
    Replies: 3
    Last Post: 10-16-2011, 11:17 PM
  3. Leading Zeros
    By dirtbiker1824 in forum Access
    Replies: 1
    Last Post: 03-14-2011, 02:16 PM
  4. Adding Leading Zeros
    By jo15765 in forum Access
    Replies: 13
    Last Post: 11-20-2010, 11:11 PM
  5. leading 'Zeros' in data
    By wasim_sono in forum Forms
    Replies: 3
    Last Post: 04-06-2009, 11:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums