Trying to maintain value as a String

Mr. B

"Doctor Access"
Local time
Today, 08:03
Joined
May 20, 2009
Messages
1,932
I am using MS Access 2010.

I am creating code that will ultimately save the specified file from the SharePoint site to a local file.

I have a form where the user is required to provide the filename that is to be saved to a local drive. We are forced to use the file naming convention that is already defined and do not have the ability to change the naming convention.

Due to the fact that when retreiving the file from the SharePoint site, the file name cannot simply contain spaces but must be formated with "%20" where spaces exist, I am attempting to replace all spaces with the "%20". Here is what I am doing:

Code:
'declare variables
Dim strSecFileName as String
Dim strNetFileName As String
 
'read the filename provided by user in variable
strSecFileName = Me.txtSecurityFileName
'the line of code above stores the following string in the variable
'    "ST Flat file 05_26_11"
'use the Replace funciton to insert the "%20" 
strNetFileName = Replace(strSecFileName, " ", "%20")
'the line of code above stores the following string in the variable
'   "ST%20Flat%20file%2005_26_11"

Please notice that in the value stored in the "strNetFileName" variable, the "05", which is the month part of the date in the original filename, has been converted to be "2005".

I have tried forcing the format of this "date" string to be a formated string but I have found no way to keep this value from being converted to the "2005".

I have tried to read only the left part of the filename (ST Flat file ) in to a variable using the InStrRev funciton and then read the right part of the filename "05_26_11" from the filename into another variable like this:

Code:
Dim strLeftStr As String
Dim strRightStr As String
Dim lngChrLoc As Long

lngChrLoc = InStrRev(strSecFileName, " ")
strLeftStr = Replace(strLeftStr, " ", "%20")
strRightStr = Format(Right(strSecFileName, Len(strSecFileName) - lngChrLoc), "mm_dd_yy")
'concatenate the left and right together
strSecFileName = strLeftStr + strRightStr
Again when the code is run the "strSecFileName" variable will contain the "2005" value.

Any suggestions for how to retain the "05" as part of the filename string would be appreciated.
 
Because it's a string, I would say you may need to get into some if statements.
Pull the string appart and if it starts with 00 - 11, append a 20 to the front, if however it's an 76 (Or whenever your data starts) to a 99 then append a 19 to the front.
You have to treat it as a string field, not a date field.
 
Thanks for the reply.

I have also tried to isolate the "05" and then append that to the end of the left part of the fieldname provided by the user. The "05" gets converted to the "2005" every time I assign it to the end of the left part of the fieldname, no matter how I have tied to manage it.

I have even tried formatting the "05" again as a string but even that will not maintain the "05" as the string I need.
 
OHHH. sorry i was thinking about it backwards! hahaha.. ok that's odd.
If i were in your position, i would step through the code.
place a stop mark at the start of your code and use F8 to step through. as you get to each line, check the variable value by either placing your mouse over the variable name or adding a "watch" to that variable. (right click on the variable and you'll see the option)
 
My question is why are you trying to use the Format function with month, day, and year? Just build the string and leave it as such.
 
Bob,

I was just trying the format function as a last ditch effort to see if I could force the "05" to remain as "05" and not being converted to "2005"

Here is code that can be copied and pasted into a new module and then test the various things that I have tried in an effort to be able to retain the desired string format.

Code:
Option Compare Database
Option Explicit
Public Function CreateStringIncludingData()
'the purpose of theis function is to test the ability to maintain
'a filename as the same sting
'To try the tests I have tried, place a breakpoint
'at the start of Test1
'Note: At the end of each test, please notice that the
'      end of each test, the "strNetFileName" the "05"
'      has been converted to "2005"
'all variables defined here
Dim strSecFileName As String
Dim strNetFileName As String
Dim lngChrLoc As Long
Dim strLeftStr As String
Dim strRightStr As String
Dim strBeginingOfDate As String

'Test1
'value provided by user
strSecFileName = "ST Flat File 05_26_11"
'just replace the spaces with the "%20" string
strNetFileName = Replace(strSecFileName, " ", "%20")

'Test2
'value provided by user
strSecFileName = "ST Flat File 05_26_11"
'locate the last space in the filename
lngChrLoc = InStrRev(strSecFileName, " ")
'extract the part of the file name prior to the date part
strLeftStr = Left(strSecFileName, lngChrLoc)
strLeftStr = Replace(strLeftStr, " ", "%20")
strRightStr = Right(strSecFileName, Len(strSecFileName) - lngChrLoc)
'try to concatenate the date part back to the string
strNetFileName = strLeftStr + strRightStr

'Test3
'value provided by user
strSecFileName = "ST Flat File 05_26_11"
'locate the last space in the filename
lngChrLoc = InStrRev(strSecFileName, " ")
'extract the part of the file name prior to the date part
strLeftStr = Left(strSecFileName, lngChrLoc)
strLeftStr = Replace(strLeftStr, " ", "%20")
strRightStr = Format(Right(strSecFileName, Len(strSecFileName) - lngChrLoc), "mm_dd_yy")
'try to concatenate the date part back to the string
strNetFileName = strLeftStr + strRightStr

'Test4
'value provided by user
strSecFileName = "ST Flat File 05_26_11"
'locate the last space in the filename
lngChrLoc = InStrRev(strSecFileName, " ")
'extract the part of the file name prior to the date part
strLeftStr = Left(strSecFileName, lngChrLoc)
strLeftStr = Replace(strLeftStr, " ", "%20")
'try to concatenate the date part back to the string
strNetFileName = strLeftStr + Format(Right(strSecFileName, Len(strSecFileName) - lngChrLoc), "mm_dd_yy")
'Test5
'value provided by user
strSecFileName = "ST Flat File 05_26_11"
lngChrLoc = InStrRev(strSecFileName, " ")
strLeftStr = Left(strSecFileName, lngChrLoc)
strLeftStr = Replace(strLeftStr, " ", "%20")
strRightStr = Right(strSecFileName, Len(strSecFileName) - lngChrLoc)
'extract only the "05" part of the date part of the filename
strBeginingOfDate = Left(strRightStr, 2)
'try to concatenate the beginning part of date part back to the string
strNetFileName = strLeftStr + strBeginingOfDate

'Test6
'value provided by user
strSecFileName = "ST Flat File 05_26_11"
lngChrLoc = InStrRev(strSecFileName, " ")
strLeftStr = Left(strSecFileName, lngChrLoc)
strLeftStr = Replace(strLeftStr, " ", "%20")
strRightStr = Right(strSecFileName, Len(strSecFileName) - lngChrLoc)
'extract only the "05" part of the date part of the filename
strBeginingOfDate = Left(strRightStr, 2)
'try to concatenate the beginning part of date part back to the string
'specifically formatting the two characters as a string
strNetFileName = strLeftStr + Format(strBeginingOfDate, "00")
End Function

I really do appreciate any input.
 
You might have better luck if you use the Access Concatenation character (&) instead of the SQL Server concatenation character (+).
 
Bob,

Actually I have tried both methods for concatenating the values together. The only reason that the SQL Server concatenation character (+) was in the code that I posted was because that was the last concatenation type character that I tried. Neither method has made any difference.

Thanks for the thought anyway.
 
Rather than:-

"ST%20Flat%20file%2005_26_11"

what do you want to see?
 
Okay, I finally understand what the output is and the only thing I need to know is - does it not work for you? What is the actual problem. LOOKING at the value it LOOKS like 2005 but it isn't. It is actually %2005_26_11 so it really should be deciphered properly by the web browser when using it as a hyperlink. So is it not working like it should?
 
Well, Bob, all I can say is that my old eyes (and I guess I really do mean "Old") are just not what they should be.

I cannot believe that I did not see that.

Thanks for the insight. LOL

Kinda makes me feel a little dumb. :o
 

Users who are viewing this thread

Back
Top Bottom