• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

How to do a conversion from Access to txt

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, http://www.4infinitesolutions.com/cures/ASAP2005/index.htm 

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

  • 2
1 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
Correction: I had to do something similar a while back
northernknightAuthor Commented:
I will check with my client to see if this will help with his situation

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now