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

Posted on 2007-10-01
Medium Priority
Last Modified: 2013-12-05
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
        !EMPNUM = Trim(vArr(0))
        !EMPNAME = Trim(vArr(1))
        !COSTCENTER = Trim(vArr(2))
        !JOBCODE = Trim(vArr(3))
        !JOBDESC = Trim(vArr(4))
    End If
    End With
End If

Question by:zimmer9
  • 4
  • 2
LVL 15

Expert Comment

ID: 19992409
LVL 15

Expert Comment

ID: 19992442
!OFFICE = SubString(Trim(vArr(2), 4, 3))

Author Comment

ID: 19992535
!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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 15

Accepted Solution

dbbishop earned 2000 total points
ID: 19992570
!OFFICE = SubString(Trim(vArr(2)), 4, 3)

Author Comment

ID: 19992745
SubString comes back with a message that it is not supported.
However, Mid works.

!OFFICE = Mid(Trim(vArr(2)), 4, 3)
LVL 15

Expert Comment

ID: 19992887
Glad I could help and you got it figured out. It's hard to keep syntax straight between 10 different languages sometimes.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question