Access 2010 fixed width specification file

Posted on 2012-09-03
Last Modified: 2012-09-03
I upgraded from Access 2003 to 2010 and have an issue with my specification file format for one of the DB's.  The format looks good but in 2010, the second column in the exported txt file gets shifted throwing all off all of the other roles.  The first stops on column 11 with the second field starting on column 12.  I even recreated the spec file in 2010 thinking the 2003 files was corrupted.   Currently, I have to run the file in Access 2003 just so I get the correct output format.
See attached image file showing the two outputs and the Spec File format.

Also, is there a way to edit the spec file outside of the DB?

Thank you…
Question by:rwarren99
    LVL 39

    Accepted Solution

    Seems Access 2010 can't cut field if it's length more then width of export field. Can you look at data in previous column? May be there are spaces or anything else?
    You can write to file manually in VBA, checking real width of each field before writing to file or take only neserrary quantity of chars in query with left function.

    Author Comment

    So maybe a trim in columne one to 11 characters may work?  My VBA is limited..  

    Author Comment

    LTrim to 11 characters worked..  Don't know how I missed it but THANK YOU...

    Author Closing Comment

    The LTRIM worked...  Don't know how I missed it but thank you.....

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now