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

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)= ". ";

In a query:

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

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 :-)

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

You can use MID as the last arguement (length) is optional
Saves returning lengths, which speeds up your query very slightly

        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

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

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]}))

>>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

what language dave?
I presume I create it is VS2003 and run it from there?

Cheers

M

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...

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

:-)

Danny,
VB .Net or C#? :-)

99 Flake or Tongue Twister?

Why use code though, if you can do it via a query?

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

>> 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

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

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

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

          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

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

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

>> 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?

>> managed word.
managed world.

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

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.
-------

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

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

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

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

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.

matthewsampson:  Did my bit of code not work?

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
----------------------------

<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!

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

Add this

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

Dave