How to do a conversion from Access to txt

Posted on 2009-12-16
Last Modified: 2013-11-28
I am working with a client that dispenses drugs and needs to send a report at the end of the day.  We have the data needed but it needs to be in ASAP 2005 format.  We need to be able to convert the data to the ASAP format.  We are using Access 2007 and can export a query to txt, but cannot get it to look like the ASAP 2005 format.

Here is the website that requires the format,

I have provided the format below.



IS*9164540168*ABCD PHARMACY\ 

IR*8005393370*CA CURES\ 

PHA***AC1234567*ABCD PHARMACY*4 TEST CENTER DR**SACRAMENTO*CA*95820*9165410500*PHY12345\ 

PAT*******ASAP*TEST****3220 TEST WAY**SACRAMENTO*CA*95620*9161231231**19610101*M\ 








TT* PHY12345_010109_01.DAT*15*\

Open in new window

Question by:northernknight
    LVL 39

    Accepted Solution

    I had to do something singular a while back - format output to a HCFA 1500 form for medical billing. I formatted text boxes like this:

    ="  " & FixedLenStr([Patient Information.City],23) & FixedLenStr([Patient Information.State],9) & IIf([Marital  Status]="S","X    ","     ") & IIf([Marital  Status]="M","X      ","       ") & IIf([Marital  Status]<>"S" And [Marital  Status]<>"M","X    ","     ")

    The function FixedLenStr is very useful for stuff like this. It takes an input string and adds spaces to output a fixed length. Here is the FixedLenStr function:

    Public Function FixedLenStr(PassedStr, DesiredLen As Long, Optional LTPad As String) As String
          ' Pass the field to be padded in "PassedStr"
          ' Pass the desired length of the field in "DesiredLen"
          ' Pass a "L" in LTPad for leading blanks, or pass a "T" for trailing blanks- if nothing is passed then "T" is assumed
          ' If the passed variable is longer than the desired length, then it will be truncated
          Dim PassedStrLen As Long
          Dim PadLen As Long

    10    PassedStrLen = Len(Nz(PassedStr, "")) ' convert null to ""
    20    If PassedStrLen < DesiredLen Then
    30        PadLen = DesiredLen - PassedStrLen ' # of spaces in pad
    40        If LTPad = "L" Then ' pad leading
    50            FixedLenStr = Space$(PadLen) & PassedStr
    60        Else ' pad trailing
    70            FixedLenStr = PassedStr & Space$(PadLen)
    80        End If
    90    Else
    100       FixedLenStr = Left(PassedStr, DesiredLen)
    110   End If

    End Function
    LVL 39

    Expert Comment

    Correction: I had to do something similar a while back
    LVL 1

    Author Comment

    I will check with my client to see if this will help with his situation

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    761 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

    8 Experts available now in Live!

    Get 1:1 Help Now