How do I create a text file to meet specifications in MS Access? 2

[LedouxMJ]

I'm building a database to bill for NYS Medicaid. The user's will be able to enter all billable visits into this system and the end result will be to write a text file to a floppy. Medicaid has provided specifications for this text file. My questions is has anyone done anything like this? I have never done this before and I'm not sure where to start.

 

[p27br]

do you mean you need access to output a text file with a specific format ?
You can use the OPEN INPUT to write data from a DB to a file.
you should post the specs so we have an idea of how it can be done

 

[thevillageinn]

is your file a flat text file or does it have a header and then records and a trailer at the bottom? Where are you storing the data that you want to write to the file? In a table, or do you determine the data with a query? is it comprised of some text boxes on a form?

Outputting data to text files is fairly straightforward, but I have had good results with different methods depending on where/how I'm compiling the data for the file.
-Dan

 

[annsolomon]

Exporting the text file is pretty straight forward. The "tricky" part comes in if they require zero-filled fields for unused numerics, space-filled fields for unused text fields, heading/trailing characters, etc. I just went through that nightmare and got lots of help on this site so if that's where you're having the problem let us know. Ann

 

[LedouxMJ]

I have figured out how to do one record type but I can't get more than one to work at the same time.

The file has a header and then records and a trailer at the bottom.

The data is being stored in more than one table. The header is in one table, records are in another table and the trailer is in still another table.

The specification is a very large document, I will give you an example. The file has to include 18 Record Types from 01 to 99 (01 being the header and 99 being the trailer).

Record Type 01 -- Header
Record Type 10 -- Not Used by NYS Medicaid
Record Type 15 -- Not Used by NYS Medicaid
Record Type 20 -- Not Used by NYS Medicaid
Record Type 25 -- Not Used by NYS Medicaid
Record Type 30 -- Multiple records allowed. May be followed by Record 31 or 35
Record Type 31 -- Not always required
Record Type 35 -- May be followed by Record 40 or 41
Record Type 40 -- Not always required; multiple records allowed
Record Type 41 -- Multiple records allowed
Record Type 61 -- Multiple records allowed. May be followed by Record 65 or 70
Record Type 65 -- Not always required
Record Type 70 -- may be followed by Record 80 or 85
Record Type 80 -- Not always required
Record Type 85 -- Not Used by NYS Medicaid
Record Type 90 -- May be followed by additional sets of Records 20 thru 90 or by Record 95
Record Type 95 -- May be followed by additional sets of Records 10 thru 95 or by Reord 99
Record Type 99 -- Must be followed by End of File Indicator

Sample Specs for Header

Record Type 01 -- Alpha Numeric -- Left Justify -- Length 2 --Record Positions 1-2

File Indicator -- Numeric -- Right Justify -- Length 10 -- Record Position 3-12

Export Specification for Record Type 01 would look like this

Length Start End
Record Type '01' 2 1 2
Submitter EIN 10 3 12
File Indicator 1 13 13

Export Specification for Record Type 30 would look like this

Length Start End
Record Type '30' 2 1 2
Sequence Number 2 3 4
Patient Control No 20 5 24

I hope this helps explain a little more clearly. If you would like to see the complete spec, I can email it to you.

 

[gusbrunston]

[tt]
I have to do the same thing in order to send Form 1099's Misc. to the IRS at their computer facility in Martinsburg. (See my thread: thread705-453954)

Blanks are required in many places and spaces in others. Word trims the spaces. Have to find a text editor that will allow spaces, or figure out how to output it from Access. [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[thevillageinn]

To output a text file with a specified header and trailer, I use the following. I have recently learned to use this method so it may be sloppy, but I hope it helps.

I use an option box on my form to determine which file type I'll be outputting, this example is "Case 1"

        'get a recordset that contains the ANIs to maintain and their associated CustomerIDs as well as the SSN2
        strSQL = "SELECT tblSprintBatchMaint.UserId, tblSprintCustId_NPA.CustomerID, tblSprintCustId_NPA.SSN2 " & _
        "FROM tblSprintBatchMaint, tblSprintCustId_NPA " & _
        "WHERE ((tblSprintCustId_NPA.City)=Left(tblSprintBatchMaint.UserId,3)); "

        Set rs = db.OpenRecordset(strSQL)
        rs.MoveLast
        rs.MoveFirst
        
                
        'set the header and footer values
        strHeader = txtCompName & vbCrLf & txtSSN2 & vbCrLf & txtBatchNo & " " & rs.RecordCount & vbCrLf & txtFileType & vbCrLf & txtEmail & vbCrLf
        strFooter = "DOWNLOADSS"
        
        
        'prepare a file for output
        Open ("C:\Documents and Settings\" & Environ("username") & "\My Documents\SprintBlockAni_" & strFileDate & ".txt") For Output As #1
        Print #1, strHeader
            For rc = 1 To rs.RecordCount
                Print #1, rs!userid & rs!CustomerID & rs!SSN2
                rs.MoveNext
            Next rc
        Print #1, strFooter
        Close #1

I have stripped out some of the validation, but this is a quick example. Does this help?

How are you storing the data that needs to be output to file?

 

[gusbrunston]

Hi:

Thanks! I'll copy this and see if I can work for what I have to do.

I'm storing the data in an Access database. [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[thevillageinn]

If your data is stored in a table, and the desired text file doesn't have a special header or footer, you can easily use the 'docmd.transfertext' method which allows you to specify a file specification that you can build in Access. Have you done much importing of text files into Access tables?

 

[gusbrunston]

[tt]
Hi TheVillageInn:

Wow! Thanks for leading me through the looking glass of the Export wizard. Saved me long, long hours of either coding or entering data into a text file. Exactly what I need to prepare 1099's to upload to the IRS in accord with their specifications (each record has to be an exact length, about 740 characters long including blanks and zeroes).

Many thanks.

By the way, are you the restaurant people? [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[thevillageinn]

I'm glad to have been of help. Esp. since I'm such a novice at this whole VBA thing.

I'm not thevillageinn of the chain of restaurants, rather a cool old greasy spoon in Illinois that I frequented in college in the wee small hours of the morning. I don't work there now or anything though.