Set the length of a string variable - Access 2

[dja1]

Is it possible to set the length of a string in Access VBA?
I am inspecting a string, and whenever there are consecutive double-quotes Chr$(34), I want to remove one of them. However, after having done this, I want to reset the length of the string to reflect the new length after deleting the characters. I am using Access 97.

 

[SgtJarrow]

You can set a fixed length string with the following format....may be what you are looking for.


Dim strString As Strign * 100 '(where 100 is the size) Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@ssmb.com

 

[dja1]

Thanks for the response, but I really wanted to set the length dynamically - i.e., dependent upon the number of double-quote characters deleted.

 

[SgtJarrow]

Can I ask for what reason you need a fixed string length??? I can only think of a couple of times this would even be necessary....
Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@ssmb.com

 

[dja1]

I am converting an Access database table to a COBOL copybook, e.g., Access table entry - Col1 = FRED Col2 = ""01" Col3 = YES.
Resulting text lines look like
03 Name PIC X(10) VALUE 'FRED'.
03 Number PIC X(4) VALUE '01'.
03 YorN PIC X(10) VALUE 'YES'.

Notes;
1). The trailing single-quote needs to follow the last character directly.
2). Not all Col2 entries have two sets of double-quotes.
3). Obviously, it's possible to do this programmatically, but if I could set the length of the variable, then it would be a matter of concatenating the final single-quote.

 

[SgtJarrow]

OK...I am not familiar with the COBOL stuff, but what I see is this...

You have a table with data as such:

Fred ""01" Yes


You need to be able to end up with:

'Fred' '01' 'Yes'

My suggestion would be to use a string of code to remove ALL quotes and any white space from each field, then simply populate your new with the remaining text and enclose this with single quotes...

I have some sample code for this if you would like....just let me know...

Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@ssmb.com

 

[uberpudge]

dja1,

Try this:

Public Sub Test()
Dim strSomeString As String

strSomeString = Strings.Chr(34) + "Wow!" + Strings.Chr(34) + " " + Strings.Chr(34) + "There sure are a " + Strings.Chr(34) + "LOT" + Strings.Chr(34) + " of " + Strings.Chr(34) + "quotes" + Strings.Chr(34) + " in this string!"

MsgBox Conversion.CStr(Strings.Len(strSomeString))

strSomeString = StripQuotes(strSomeString)

MsgBox Conversion.CStr(Strings.Len(strSomeString))
End Sub

Public Function StripQuotes(ByVal strRawName As String)
Dim strTmp As String
Dim strNewName As String
Dim intLength As Integer
Dim intDex As Integer

intLength = Strings.Len(strRawName)

For intDex = 1 To intLength
strTmp = Strings.Left(strRawName, 1)
strRawName = Strings.Right(strRawName, Strings.Len(strRawName) - 1)

If strTmp <> Strings.Chr(34) Then
strNewName = strNewName + strTmp
End If
Next

StripQuotes = strNewName
End Function


StripQuotes will return the string without any quotes in it.
You can easily get the new length from that although, if you set the old string to the return value you could use:

strSomeString = strSomeString + &quot;'&quot;

to place a trailing single quote on the end.

Hope this helps,
Pete

 

[dja1]

Thanks to both mstrmage1768 and uberpudge.

I tried uberpudge's suggestion, and it worked really well - thanks very much.
I have a further question, however - in the solution &quot;Conversion.Cstr...&quot; and &quot;Strings.Len...&quot; were coded.
What is the significance of the &quot;Conversion&quot; and &quot;Strings&quot; -where can I find an explanation of these?