Remove 1st or 2nd characters in a field if they start with a "full stop" or a space

I have a database of end-of-term school reports that the staff have completed a new record for each child.
In the general comments field, some of them have left a space in, and some have a full stop and a space before the text starts.

Is there a way of saying, in this column/field, search through and if the 1st character is a fullstop or a space then remove it??

The files are in access.

Any help would be great.

Best Wishes
Matt
LVL 1
matthewsampsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

will_scarlet7Commented:
You could do it via a Query. Something like:

UPDATE YourTable
SET YourTable.YourField = Mid(YourTable.YourField, Instr(YourTable.YourField, " ")+1)
WHERE ((Left(YourTable.YourField,1) = " ") OR Left(YourTable.YourField,2)= ". ";
dannywarehamCommented:
In a query:

IIF(Left([yourfield],1) = " " OR Left([yourfield],1) = ".", Mid([yourfield],2), [yourfield])

flavoCommented:
Hi matthewsampson,

2 update query's like this should do it for you
Run this first

UPDATE myTable
SET myField = Trim(myField)

Then this

UPDATE myTable
Set MyField =Trim(IIf(Left(myField,1)=".", Right(myField,Len(myField)-1), myField))

************* MAKE SURE ITS ON A BACKUP JUST INCASE PLEASE ******************

Dave :-)
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

dannywarehamCommented:
In code:

Dim myValue as String
IF Left([yourfield],1) = " " OR Left([yourfield],1) = "." Then
myValue = Mid(Me.[yourfield],2)
Else
myValue = Me.[yourfield]
End IF

Me.Textbox = myValue
dannywarehamCommented:
You can use MID as the last arguement (length) is optional
Saves returning lengths, which speeds up your query very slightly
Sayad Aziz AhmadCommented:

        Dim dbs As Database
        Dim rst As Recordset
       
        Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset("urtablename", DB_OPEN_DYNASET)
       rst.MoveFirst

       Do Until rst.EOF()
       
            If Left(rst!urcommentfieldname,1) = "" Or Left(rst!urcommentfieldname,1) = "." Then
                rst.Edit
                rst!urcommentfieldname = Mid(rst!urcommentfieldname, 2, Len(rst!urcommentfieldname))
            End If
           
            rst.MoveNext
       
        Loop

Aziz
matthewsampsonAuthor Commented:
Do you know of a way to do it in Crystal Reports???
I've got about 100 database files (i.e: one from each teacher)
or perhaps a batch file to work through a set of .mdb files in a directory?

M
dannywarehamCommented:
Trim is used to remove BLANKs or SPACES form the right and left of a string.

You could use something like (different syntax in Crystal - ibelieve uses {} too):

If (Left({[yourfield]},1 = ".", Mid({yourfield},2,Length({yourfield}) - 1), Trim({[yourfield]}))
flavoCommented:
>>I've got about 100 database files (i.e: one from each teacher)

If they are all in the same directory try this (assuming my querys above worked)

Sub fix100sOfDatabases()

    '// Needs references to Microsoft DAO Object Library
    '// And Microsoft Scripting Runtime
   
    Dim db As DAO.Database
   
    Dim fso As Scripting.FileSystemObject
    Dim fld As Scripting.Folder
    Dim f As Scripting.File
   
    Set fso = New FileSystemObject
    Set fld = fso.GetFolder("c:\AllMyDbs\")  '****************** Your Path
   
    For Each f In fld.Files
        If f.Type = "Microsoft Access Database" Then
            Set db = OpenCurrentDatabase(f.Path)
            db.Execute "UPDATE myTable " & _
                        "Set myField = Trim(myField)"
            db.Execute "UPDATE myTable " & _
                        "Set myField = Trim(IIf(Left(myField, 1) = ""."", Right(myField, Len(myField) - 1), myField))"
            db.Close
        End If
    Next
   
    Set db = Nothing
    Set f = Nothing
    Set fld = Nothing
    Set fso = Nothing
   
End Sub

Dave
matthewsampsonAuthor Commented:
what language dave?
I presume I create it is VS2003 and run it from there?

Cheers

M
flavoCommented:
Nope.. Regular VBA.  Just run it from blank Access DB (In a diferent directory)

I could do it in VS 2003 if you really wanted...
dannywarehamCommented:
>>I could do it in VS 2003 if you really wanted...
Now you're just showing off

:-)
flavoCommented:
Danny,
VB .Net or C#? :-)
dannywarehamCommented:
99 Flake or Tongue Twister?

will_scarlet7Commented:
Why use code though, if you can do it via a query?
d_a_hCommented:
Will,

NOTE: I've got about 100 database files (i.e: one from each teacher)
or perhaps a batch file to work through a set of .mdb files in a directory?

One query will not open the database and manipulate the data
flavoCommented:
>> perhaps a batch file to work through a set of .mdb files in a directory?

That is what my last code did.

I think will_scarlet7 was advising that you just use a query from within CR to do it, not update the data.

Dave
matthewsampsonAuthor Commented:
Hi Dave,

I've used your code, but it fails at this point:
      Set db = OpenCurrentDatabase(f.Path)
Saying "Expected Function or Variable"

Thanks Dave.

Matt

flavoCommented:
My mistake

Sub fix100sOfDatabases()

    '// Needs references to Microsoft DAO Object Library
    '// And Microsoft Scripting Runtime
   
    Dim db As DAO.Database
    Dim wJet As DAO.Workspace
   
    Dim fso As Scripting.FileSystemObject
    Dim fld As Scripting.Folder
    Dim f As Scripting.File
   
    Set fso = New FileSystemObject
    Set fld = fso.GetFolder("c:\AllMyDbs\")  '****************** Your Path
   
    Set wJet = CreateWorkspace("", "admin", "", dbUseJet)
   
    For Each f In fld.Files
        If f.Type = "Microsoft Access Database" Then
            Set db = wJet.OpenDatabase(f.Path)
            db.Execute "UPDATE myTable " & _
                        "Set myField = Trim(myField)"
            db.Execute "UPDATE myTable " & _
                        "Set myField = Trim(IIf(Left(myField, 1) = ""."", Right(myField, Len(myField) - 1), myField))"
            db.Close
        End If
    Next
   
    wJet.Close
    Set wJet = Nothing
    Set db = Nothing
    Set f = Nothing
    Set fld = Nothing
    Set fso = Nothing
   
End Sub
matthewsampsonAuthor Commented:
am I missing something?
I can't get it to do anything.
I set it up as a button to when it's clicked on a form it fires this event.

All the files have the same table name "AllGroups" and the fields to check are "GeneralComments" and "Achievements".



Private Sub Command0_Click()
   
    Dim db As DAO.Database
    Dim wJet As DAO.Workspace
   
    Dim fso As Scripting.FileSystemObject
    Dim fld As Scripting.Folder
    Dim f As Scripting.File
   
    Set fso = New FileSystemObject
    Set fld = fso.GetFolder("D:\BYMT\2005\testdb\")  '****************** Your Path
   
    Set wJet = CreateWorkspace("", "admin", "", dbUseJet)
   
    For Each f In fld.Files
        If f.Type = "Microsoft Access Database" Then
            Set db = wJet.OpenDatabase(f.Path)
            db.Execute "UPDATE AllGroups " & _
                        "Set myField = Trim(GeneralComments)"
            db.Execute "UPDATE AllGroups " & _
                        "Set myField = Trim(IIf(Left(GeneralComments, 1) = ""."", Right(GeneralComments, Len(GeneralComments) - 1), GeneralComments))"
            db.Close

        End If
    Next
   
    wJet.Close
    Set wJet = Nothing
    Set db = Nothing
    Set f = Nothing
    Set fld = Nothing
    Set fso = Nothing

   
     
End Sub
flavoCommented:
          Set db = wJet.OpenDatabase(f.Path)
'// Update GeneralComments
            db.Execute "UPDATE AllGroups " & _
                        "Set GeneralComments= Trim(GeneralComments)"
            db.Execute "UPDATE AllGroups " & _
                        "Set GeneralComments= Trim(IIf(Left(GeneralComments, 1) = ""."", Right(GeneralComments, Len(GeneralComments) - 1), GeneralComments))"
'// update Achievements
            db.Execute "UPDATE AllGroups " & _
                        "Set Achievements= Trim(Achievements)"
            db.Execute "UPDATE AllGroups " & _
                        "Set Achievements= Trim(IIf(Left(Achievements, 1) = ""."", Right(GeneralComments, Len(Achievements) - 1), Achievements))"
            db.Close
d_a_hCommented:
Same vein but see if this works:

Dim wkrJet as Workspace
Dim dbsCurr as DAO.Database
dim intCount as Integer

Private Sub Command0_Click()

    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
    intCount = 0
    With Application.FileSearch
        .NewSearch
        .LookIn = "D:\BYMT\2005\testdb\"
        .SearchSubFolders = False
        .FileName = "*.mdb"
        .MatchAllWordForms = True
        If .Execute() > 0 Then
            For i = 1 To .FoundFiles.Count
                intCount = intCount + 1
                Set dbsCurr = wrkJet.OpenDatabase(.FoundFiles(i))
                dbsCurr.Execute "UPDATE AllGroups " & _
                        "Set myField = Trim(GeneralComments)"
                dbsCurr.Execute "UPDATE AllGroups " & _
                        "Set myField = Trim(IIf(Left(GeneralComments, 1) = '.', Right(GeneralComments, Len(GeneralComments) - 1), GeneralComments))"
                dbsCurr.Close
        End If
    End With
    MsgBox "Processed " & intCount & " files"
    Set wkrJet = Nothing
End Sub
matthewsampsonAuthor Commented:
It doesn't appear to work?
Any thoughts?
Would it work better if I fired it from a VB or C# event?
If so how would I do that?

Thanks Dave, much appeciated.

Matt
flavoCommented:
>> Would it work better if I fired it from a VB or C# event?

Keep it in VBA.  It'll need a full write to go into the managed word.

Matt,

Are you getting any errors?
flavoCommented:
>> managed word.
managed world.
matthewsampsonAuthor Commented:
It doesn't throw up any errors. I click on the button and it does nothing!
Almost as if the button doesn't do anything.

I've got it under the "onClick" fire.

M
matthewsampsonAuthor Commented:
Just tried it as a new button and the following error pops up:

"The expression OnClick you entered as the event property setting produced the following error: Ambiguous name detected: Command0_Click."
-------
Visual Basic for Applications (VBA) encountered a problem while attempting to access a property or method. The problem may be one of the following:
A reference is missing.
For help restoring missing references, see the Microsoft Knowledge Base article 283806.
An Expression is misspelled.
Check all expressions used in event properties for correct spelling.
A user-defined function is declared as a sub or as a private function in a module.
Expressions can resolve a user-defined function only if the function is declared as one of the following:
- A public function in a module
- A public or private function in a code module of the current form or report
Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8 update is not installed.
A more recent verion of Jet 4.0 must be installed for Access to function properly when security is set to Medium or High. To obtain the latest version of Microsoft Jet, go to Windows Update.
-------
matthewsampsonAuthor Commented:
I tried running the code from  D_E_H  (see above),
This time, the code runs, but it gets to the end of the If statement and says;
"Compile Error" End If without block If.

Sub Command1_Click()
Dim wkrJet As Workspace
Dim dbsCurr As DAO.Database
Dim intCount As Integer

    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
    intCount = 0
    With Application.FileSearch
        .NewSearch
        .LookIn = "D:\BYMT\2005\testdb\"
        .SearchSubFolders = False
        .FileName = "*.mdb"
        .MatchAllWordForms = True
        If .Execute() > 0 Then
            For i = 1 To .FoundFiles.Count
                intCount = intCount + 1
                Set dbsCurr = wrkJet.OpenDatabase(.FoundFiles(i))
                dbsCurr.Execute "UPDATE AllGroups " & _
                        "Set myField = Trim(GeneralComments)"
                dbsCurr.Execute "UPDATE AllGroups " & _
                        "Set myField = Trim(IIf(Left(GeneralComments, 1) = '.', Right(GeneralComments, Len(GeneralComments) - 1), GeneralComments))"
                dbsCurr.Close
        End If
    End With
    MsgBox "Processed " & intCount & " files"
    Set wkrJet = Nothing
End Sub
GRayLCommented:
Assuming all the mdb's are in one directory try this code in a module including the mdb from which you are running this.  this will remove any leading period and/or leading and trailing spaces from the field myFld in all tables named myTable in all the directories in the path <enter path to directory here>

Private Sub delStopSpace()

dim mymdb as string
mymdb = c:<enter path to directory here>\*.mdb
do while len(mymdb)>0
  mysql = "Update myTable in '" & mymdb & "' where myFld = Trim(replace  (myFld,""."","""",1,1));"
  mymdb=dir
loop

End Sub
GRayLCommented:
Sorry, mymdb line and dim line:

Private Sub delStopSpace()

dim mymdb as string, mysql as string
mymdb = dir("c:<enter path to directory here>\*.mdb")
do while len(mymdb)>0
  mysql = "Update myTable in '" & mymdb & "' where myFld = Trim(replace  (myFld,""."","""",1,1));"
  mymdb=dir
loop

End Sub
d_a_hCommented:
My fault.  Hacked it a bit too much from an existing db.  There should be a Next (For.....Next)

After dbsCurr.Close AND before End If insert

Next i
flavoCommented:
Still not sure why mine didn't work, for that matter dah's or Ray's!

Personally, I find the FileSystemObject much easier to work with than the Application.FileSearch

I see 3 valid solutions. They should all work!

Are we  sure the Update query's are actually updating the tables?  I think this is the key.
GRayLCommented:
matthewsampson:  Did my bit of code not work?
matthewsampsonAuthor Commented:
Hi Guys,
I got DAH's working in the end like so below.

Any ideas on how to search through the whole field of "General Comments" and remove and & signs and change them to the word and.

Thanks guys.

Matt

----------------
Dim wkrJet As Workspace
Dim dbsCurr As DAO.Database
Dim intCount As Integer

    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
    intCount = 0
    With Application.FileSearch
        .NewSearch
        .LookIn = "D:\BYMT\2005\testdb\"
        .SearchSubFolders = False
        .FileName = "*.mdb"
        .MatchAllWordForms = True
        If .Execute() > 0 Then
            For i = 1 To .FoundFiles.Count
                intCount = intCount + 1
                Set dbsCurr = wrkJet.OpenDatabase(.FoundFiles(i))
                dbsCurr.Execute "UPDATE AllGroups " & _
                        "Set Achievements = Trim(Achievements)"
                dbsCurr.Execute "UPDATE AllGroups " & _
                        "Set Achievements = Trim(IIf(Left(Achievements, 1) = '.', Right(Achievements, Len(Achievements) - 1), Achievements))"
                Next i
                dbsCurr.Close
        End If
    End With
    MsgBox "Processed " & intCount & " files"
    Set wkrJet = Nothing
----------------------------

GRayLCommented:
<Any ideas on how to search through the whole field of "General Comments" and remove and & signs and change them to the word and.>

Not if you're not going to try mine!
d_a_hCommented:
Next i  should be AFTER dbsCurr.Close following rules etc. though as you have proven it does still run!

ie

dbsCurr.Close
Next i
End If

Quick answer for the find and replace is enclose the & in [ ] brackets

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
matthewsampsonAuthor Commented:
Hi Dah,

What would the line be to do the swap of & for and?
I've had a go, but starting to loose hair now!

Thanks

Matt
flavoCommented:
Add this

dbsCurr.Execute "UPDATE AllGroups " & _
                        "Set [GeneralComments] = Replace([GeneralComments], "&", "and");"

Dave
flavoCommented:
Damn!  sorry

dbsCurr.Execute "UPDATE AllGroups " & _
                        "Set [GeneralComments] = Replace([GeneralComments], ""&"", ""and"");"
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.