Removing Letters

[alastair69]

Hello All,

I a very simple question and i know this has been covered before but any help as ways.


I have a room number that is entered like this E\001 the table name [tmakProperty] and field name is [Area_Number], it could be upto two letters at the front of the \, i am saving the room number as a field name so no \ are allowed in the save name how can this be done.

Thank you for your time and help.

Alastair

 

[RuralGuy]

I'm a little confused as to what you want but would the Replace() function solve your issue by replacing the "\" with a space or some other character?

 

[alastair69]

The code below is what i am using to save a file name from access into word, the problem is that str_Area_Number contains the following value E/001.

what i think i need is a way of removing the "/" so the value would be E001, RuralGuy gave the possible answer as using the replace () comand i am not sure if this would work, if possible could some body correct the code below so i can see the answer clearly.

CODE

'check for existenc of previously saved letter in documents folder,
'and append an incremented number to save name if found
strSaveName = "Report Ref " & Str_Address & Str_Area_Number & "doc"
intCount = 2
blnSaveNameFail = True
Do While blnSaveNameFail
strSaveNamePath = strDocsPath & strSaveName
Debug.Print "Proposed save name: " _
& vbCrLf & strSaveNamePath
strTestFile = Nz(Dir(strSaveNamePath))
If strTestFile = strSaveName Then

'Create new save name with increented number

blnSaveNameFail = True
strSaveName = "Report Ref " & Str_Address & Str_Area_Number & "doc"

strSaveNamePath = strDocsPath & strSaveName
intCount = intCount + 1
Else
blnSaveNameFail = False
End If
Loop

 

[Rabbie]

Try this

'check for existenc of previously saved letter in documents folder,
'and append an incremented number to save name if found
strSaveName = "Report Ref " & Str_Address & Str_Area_Number & "doc"
intCount = 2
blnSaveNameFail = True
Do While blnSaveNameFail
    strSaveNamePath = strDocsPath & Replace(strSaveName,"/","")
    Debug.Print "Proposed save name: " _
        & vbCrLf & strSaveNamePath
    strTestFile = Nz(Dir(strSaveNamePath))
    If strTestFile = strSaveName Then
    
    'Create new save name with increented number
    
    blnSaveNameFail = True
    strSaveName = "Report Ref " & Str_Address & Replace(Str_Area_Number,"/","") & "doc"
    
    strSaveNamePath = strDocsPath & strSaveName
    intCount = intCount + 1
    Else
        blnSaveNameFail = False
    End If
Loop

 

[alastair69]

I end up with a file called Report Ref No. 19, Priory Street, York.E001docdoc, and will not open by double clicking.

'check for existenc of previously saved letter in documents folder,
'and append an incremented number to save name if found
strSaveNamePath = strDocsPath & Replace(strSaveName, "/", "") & "doc"
intCount = 2
blnSaveNameFail = True
Do While blnSaveNameFail
strSaveNamePath = strDocsPath & Replace(strSaveName, "/", "") & "doc"
Debug.Print "Proposed save name: " _
& vbCrLf & strSaveNamePath
strTestFile = Nz(Dir(strSaveNamePath))
If strTestFile = strSaveName Then

'Create new save name with increented number

blnSaveNameFail = True
strSaveName = "Report Ref " & Str_Address & Replace(Str_Area_Number, "/", "") & "doc"

strSaveNamePath = strDocsPath & strSaveName
intCount = intCount + 1
Else
blnSaveNameFail = False
End If
Loop

 

[alastair69]

I have got it working by the following code:

'check for existenc of previously saved letter in documents folder,
'and append an incremented number to save name if found
strSaveNamePath = "Report Ref " & Replace(Str_Address, ".", " ") & Replace(Str_Area_Number, "/", "") & ".doc"
intCount = 2
blnSaveNameFail = True
Do While blnSaveNameFail
strSaveName = "Report Ref " & Replace(Str_Address, ".", " ") & Replace(Str_Area_Number, "/", "") & ".doc"
Debug.Print "Proposed save name: " _
& vbCrLf & strSaveNamePath
strTestFile = Nz(Dir(strSaveNamePath))
If strTestFile = strSaveName Then

'Create new save name with increented number

blnSaveNameFail = True
strSaveName = "Report Ref " & Replace(Str_Address, ".", " ") & Replace(Str_Area_Number, "/", "") & ".doc"

strSaveNamePath = strDocsPath & strSaveName
intCount = intCount + 1
Else
blnSaveNameFail = False
End If
Loop

 

[Rabbie]

Glad to here you have got it working. I am afraid, being in a bit of a rush, I didn't check any thing outside of where the Replace command would go.

Still it was good training for you to find the problem and fix it

 

[alastair69]

Rabbie,

I am leaning so much from the have ago approuch and it seems to be working so far touch wood, it difficult to know were to put the code to get it in the right place.

Thank you so much for your help and understanding.