How to modify a SQL Statement to create a new field which is a substring of an existing field ?

I am developing an Access application with an ADP file. I am using Access as the front end and SQL Server as the back end database. How would you modify the following routine to add a new field named
OFFICE which is derived from positions 4 to 6 of the COSTCENTER field ?

Open "C:\psttest.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, s
If Len(s) > 0 Then
    s = Replace(s, Chr(34), "")
    vArr = Split(s, "|")
    txJobDesc = Trim(vArr(4))
    With rs
    If txJobDesc = "Complex Service Manager" Or _
       txJobDesc = "Complex Manager" Or _
       txJobDesc = "Branch Manager, Producing" Or _
       txJobDesc = "Branch Manager, Non Prod" Then
        .AddNew
        !EMPNUM = Trim(vArr(0))
        !EMPNAME = Trim(vArr(1))
        !COSTCENTER = Trim(vArr(2))
        !JOBCODE = Trim(vArr(3))
        !JOBDESC = Trim(vArr(4))
        .Update
    End If
    End With
End If

Loop
zimmer9Asked:
Who is Participating?
 
dbbishopCommented:
!OFFICE = SubString(Trim(vArr(2)), 4, 3)
0
 
dbbishopCommented:
SELECT SUBSTRING(COSTCENTER, 4, 3) AS OFFICE
0
 
dbbishopCommented:
Sorry-
!OFFICE = SubString(Trim(vArr(2), 4, 3))
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.

 
zimmer9Author Commented:
!OFFICE = SubString(Trim(vArr(2), 4, 3))
--------------------------------------------------
For some reason, the compiler highlights TRIM and sends the following error:

Compile Error:
Wrong number of arguments or invalid property assignment
0
 
zimmer9Author Commented:
SubString comes back with a message that it is not supported.
However, Mid works.

!OFFICE = Mid(Trim(vArr(2)), 4, 3)
0
 
dbbishopCommented:
Glad I could help and you got it figured out. It's hard to keep syntax straight between 10 different languages sometimes.
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.

All Courses

From novice to tech pro — start learning today.