extracting the last 4, 1, 7 characters from a field

Delores_C
Delores_C used Ask the Experts™
on
I am not sure how to start this process.  I need to write a script that extract, the last four character, the first 7 characters and the first character from various fields.  I need to automatically saved the results from these records to a text file and automatically have it saved to a folder.

example
accountNo  = 123456789   results desired 1234567
ID = 78569   results desired 7856
Location = 7878  results desired 8

I need the results to display exact as displayed with spaces and line breaks


1234567(7 spaces)7856(line break)
8(line break)

All help is appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<I need to write a script that extract, the last four character, the first 7 characters and the first character from various fields.>
Then this should be the main point here.

The other things you requested are really not directly related to the text extraction and really should be separate new questions.
As you know, you are only allowed one distinct question per post.
Sound fair?

JeffCoachman

Commented:
In a VBA function (or sub), open a recordset of the records to send to a text file.

Dim db As DAO.Database, rst As DAO.Recordset, SQLSelect As String
Set db = CurrentDb
SQLSelect = "SELECT accountNo, ID, Location FROM table_name;"
Set rst = db.OpenRecordset (SQLSelect, dbOpenForwardOnly)
Open a file for writing

Open "C:\Temp\textfile.txt" For Output As #1
Step through the recordset writing the text lines to the open file

With rst
Do While Not .EOF
    Print #1, Left(!accountNo, 7) & Spc(7) & Left(!ID, 4)
    Print #1, Right(!Location, 1)
    .MoveNext
Loop
.Close
End With
Close the text file

Close #1

I may not have the exact formatting of the Open, Close, and Print statements correct.

Lee

Author

Commented:
This is what I have so far, bot its not working, please advise.

Private Sub RunClientText_Click()
Private Sub Command3_Click()
Dim db As DAO.Database, rst As DAO.Recordset, SQLSelect As String
Set db = CurrentDb
SQLSelect = "SELECT tblClientInfo.AccountNo,tblClientInfo.ID, tblClientInfo.Location FROM tblClientInfo;"
Set rst = db.OpenRecordset(SQLSelect, dbOpenForwardOnly)

Open "C:\Users\Dee\Clients\textfile.txt" For Output As #1
Step through the recordset writing the text lines to the open file


With rst
Do While Not .EOF
    Print #1, Left(!AccountNo, 7) & Spc(7) & Left(!ID, 4)
    Print #1, Right(!Location, 1)
    .MoveNext
Loop
.Close
End With


Close the text file


Close #1



End Sub
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:
Is that really a copy/paste of your code? If so, no, it won't work.

You should only have 1 "Private Sub ButtonName_Click()" statement, not 2.

These lines should be commented with a single quote:

'Step through the recordset writing the text lines to the open file
'Close the text file
If I'm mistaken, please post your button's Click event code exactly, and we'll work from there.

Lee

Author

Commented:
Private Sub RunClientText_Click()
Dim db As DAO.Database, rst As DAO.Recordset, SQLSelect As String
Set db = CurrentDb
SQLSelect = "SELECT tblClientInfo.AccountNo,tblClientInfo.ID, tblClientInfo.Location FROM tblClientInfo;"
Set rst = db.OpenRecordset(SQLSelect, dbOpenForwardOnly)

Open "C:\Users\Dee\Clients\textfile.txt" For Output As #1
Step through the recordset writing the text lines to the open file


With rst
Do While Not .EOF
    Print #1, Left(!AccountNo, 7) & Spc(7) & Left(!ID, 4)
    Print #1, Right(!Location, 1)
    .MoveNext
Loop
.Close
End With


Close the text file


Close #1



End Sub

Commented:
The 2 lines

Step through the recordset writing the text lines to the open file
Close the text file
still need to be commented out with a single quote or deleted.

Also, I did not include error handling or cleanup initially. See the attached code. You should always employ error handling to prevent your app from crashing badly and to show helpful (sometimes if you're lucky ;-) ) troubleshooting information. Error handling is especially important with file system access--there are many ways for this to fail (file not found, file locked, permissions, etc.).

Paste this code over yours and try it. We may still need to tweak the file access code.
Private Sub RunClientText_Click()
On Error Goto Err_RunClientText_Click

    Dim db As DAO.Database, rst As DAO.Recordset
    Set db = CurrentDb

    Dim SQLSelect As String
    SQLSelect = "SELECT tblClientInfo.AccountNo, tblClientInfo.ID, tblClientInfo.Location FROM tblClientInfo;"
    Set rst = db.OpenRecordset (SQLSelect, dbOpenForwardOnly)

    Open "C:\Users\Dee\Clients\textfile.txt" For Output As #1

    'Step through the recordset writing the text lines to the open file
    With rst
    Do While Not .EOF
        Print #1, Left(!AccountNo, 7) & Spc(7) & Left(!ID, 4)
        Print #1, Right(!Location, 1)
        .MoveNext
    Loop
    End With

    'Close the text file
    Close #1

Exit_RunClientText_Click:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub

Err_RunClientText_Click:
    MsgBox "Error # " & Err.Number & " : " & Err.Description
    Resume Exit_RunClientText_Click

End Sub

Open in new window

Author

Commented:
Sorry it took me so long to get back.  Emergency!  I will paste and try this now.

Author

Commented:
I am getting an error, at the
Print #1, Left(!AccountNo, 7) & Spc(7) & Left(!ID, 4)

Commented:
Sorry it took so long to respond.

In the Print #1 statement, replace the "&" with";" to make the statement read

Print #1, Left(!AccountNo, 7) ; Spc(7) ; Left(!ID, 4)

Author

Commented:

I am still getting this error:  Error #52:  Bad file name or number
See the code I am using below, which is attached to a command button


Private Sub RunClientText_Click()
On Error GoTo Err_RunClientText_Click

    Dim db As DAO.Database, rst As DAO.Recordset
    Set db = CurrentDb

    Dim SQLSelect As String
    SQLSelect = "SELECT tblClientInfo.AccountNo, tblClientInfo.ID, tblClientInfo.Location FROM tblClientInfo;"
    Set rst = db.OpenRecordset(SQLSelect, dbOpenForwardOnly)

    Open " C:\textfile.txt" For Output As #1

    'Step through the recordset writing the text lines to the open file
    With rst
    Do While Not .EOF
        Print #1, Left(!AccountNo, 7); Spc(7); Left(!ID, 4)
        Print #1, Right(!Location, 1)
        .MoveNext
    Loop
    End With

    'Close the text file
    Close #1

Exit_RunClientText_Click:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub

Err_RunClientText_Click:
    MsgBox "Error # " & Err.Number & " : " & Err.Description
    Resume Exit_RunClientText_Click

End Sub
Commented:
On which line is the error occurring? The same Print #1 statement?

Delete the space in front of the C: in the line

Open " C:\textfile.txt" For Output As #1

Commented:
Was that the problem? What fixed it?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial