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

Creating ASCII text from MS Access 2003

How can I create an ascii text file from within Access VBA so that a word starting with - appears at the start of a text file.
If I export a table now as a text file or create a text using vba code it would put a word like --Caltex in with the words starting with c.
How can I get the table to export in true ascii format so that the words that start with - appear at the start of the text file.
Creating a recordset in vba using the Order By clause but it still does export in true ascii format.

Any help would be appreciated.
Regards
Bruce
0
rbscott
Asked:
rbscott
  • 4
  • 2
2 Solutions
 
Patrick MatthewsCommented:
Hello rbscott,

It would be useful to see some sample data.  EE now allows you to directly upload
files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it
may not be possible to fully and permanently delete it.  Therefore, be very careful about
posting proprietary, confidential, or other sensitive information.  If necessary, use "fake"
and/or obfuscated data in your sample.

Regards,

Patrick
0
 
rbscottAuthor Commented:
Hi Matthew
Some sample data may be
#123
-123
123
#lf3000
-lf3000
lf3000
If I do a sort ascending or export the table to a text file I get the following sort order
#123
#lf3000
123
-123
lf3000
-lf3000
True ascii sort is as follows
-123
-lf3000
#123
#lf3000
123
lf3000
Used the sort.exe command in the dos window to get the sort order above.
sort parts.txt
I put these parts into a table in an access database called table1.
Used the export command on the toolbar to export to a text file.
Also created the same file using vba code using the Order By clause in a recordset based on table on.
Acheived the same result both ways.
Hope you can do something with this information.

Thanks
Bruce



Parts.txt
0
 
rbscottAuthor Commented:
Matthew
I have attached the code that creates the text file

Cheers
Bruce
Private Sub Command0_Click()
Dim dbs As Database
Set dbs = CurrentDb
Dim rst As DAO.Recordset
Dim strTemp As String
Dim FileNo As Integer
Dim strFilename As String
Dim FN
Set rst = dbs.OpenRecordset("SELECT Table1.Part From Table1 Order By Part;")
rst.MoveFirst
FileNo = FreeFile()
 
strFilename = "D:\Downloads\Parts.txt"
 
Open strFilename For Output As FileNo
Do
        
        strTemp = rst.Fields("Part")
        Print #FileNo, strTemp
        rst.MoveNext
Loop Until rst.EOF
 
   
    Close FileNo
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Patrick MatthewsCommented:
Try an ORDER BY clause like this:


ORDER BY IIf(Left(SomeField, 1) = "-", 1, IIf(Left(SomeField, 1) = "#", 2, 3)), SomeField

That sorts everything starting with -, then everything starting with #, then everything else...
0
 
rbscottAuthor Commented:
Thankyou Matthew
I guess that would work but it may be hard to work out all the different ascii codes that someone may use.
the - and the # were just an example, they may use ^ % $ etc.
but this is a start.
I thought that there may have been a command like the DOS Sort.exe available in VBA.
It might be just as easy to say to people, do not use ASCII symbols in the part Number field.
Thanks for your help so far.

Regards
Bruce
0
 
rbscottAuthor Commented:
It did not help as I explained in my last post.
I cannot assume what ASCII charactors people are going to use and what order they may be sorted in.
The other option for me is too when I create the text file is to sheell to DOS and do a sort.exe on the text file
I accepted this and posted points at least a month ago
Regards
Bruce
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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