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
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
D B
SELECT SUBSTRING(COSTCENTER, 4, 3) AS OFFICE
Sorry-
!OFFICE = SubString(Trim(vArr(2), 4, 3))
!OFFICE = SubString(Trim(vArr(2), 4, 3))
ASKER
!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
--------------------------
For some reason, the compiler highlights TRIM and sends the following error:
Compile Error:
Wrong number of arguments or invalid property assignment
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SubString comes back with a message that it is not supported.
However, Mid works.
!OFFICE = Mid(Trim(vArr(2)), 4, 3)
However, Mid works.
!OFFICE = Mid(Trim(vArr(2)), 4, 3)
Glad I could help and you got it figured out. It's hard to keep syntax straight between 10 different languages sometimes.