Need help in removing parenthesis

[henniec]

Good day,

I am trying to create a query by form a have a slight problem when using the IN function. Selecting multiple parameters for the query automatically adds parenthesis to the text and I need to remove them as the IN function does not like them. The attached code produces the following result”

“(‘Wednesday’, ‘Thursday’, ‘Friday’)” while I only need (‘Wednesday’, ‘Thursday’, ‘Friday’).

I saw some example using Ltrim$ RTrim$ and Mid$ and are not sure how to implement it.

The code works fine as I do a multiple selection from a combo box and it is only this last step. Include in the code below is some code I copied supplied by Microsoft and the second (Function InParam(Fld, Param)) I do not think I need as acquire the information from my form.

Function GetToken(stLn, stDelim) maybe what I require but I am not sure how to link the two functions.

Thank you for your time.

Hennie

Dim declarations removed.

For Each varItm In ctl.ItemsSelected
For intI = 0 To ctl.ColumnCount - 1
Debug.Print ctl.Column(intI, varItm)
'Place "," after first item chosen
If Choice = Empty Then
Choice = Choice & "'" & ctl.Column(intI, varItm) & "'"
Else
Choice = Choice & ", '" & ctl.Column(intI, varItm) & "'"
Debug.Print Choice
End If
Next intI
Debug.Print
Next varItm

Microsoft's code

' The GetToken() function defines the delimiter character.
'============================================================


Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function

'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the In() operator.
'============================================================
Function InParam(Fld, Param)
Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = ""
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(Fld)) Then
InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function

 

[jrbarnett]

YourVariable = Mid$ (YourString, 2, Len (Yourstring)-2)

This works by removing the first and last characters from your string variable. It starts by taking everything from the 2nd character of your variable, so in your example:

Mid$ (('Wednesday', 'Thursday', 'Friday'), 2) =

'Wednesday', 'Thursday', 'Friday')

then the Len (('Wednesday', 'Thursday', 'Friday'))-2 takes the last two characters off the end.

John

 

[henniec]

Hi John,

Thanks for your reply. I will only be able to check on it next Tuesday and will let you know how the result.

Hennie

 

[henniec]

Good day,

I tried the method suggested and it do remove some characters. The problem I do see is it seems to replace the parenthesis ("") afterwards. As I see it because it’s a text field Access does not want to release the string without adding the parenthesis.

Any other ideas to get past it.

Thanks.

Hennie

 

[jrbarnett]

Hmmm, this seems to do what you asked for in your first question:

Function test()

    Const YourString = """('Wednesday', 'Thursday', 'Friday')"""
    
    Debug.Print YourString
    Debug.Print Mid$(YourString, 2, Len(YourString) - 2)

End Function

The extra quotation marks on the YourString constant are there to ensure that the leading and trailing quotations stay as part of the string

The first debug statement displays the source statement, the second displays the truncated one, which removes the first and last quotation mark but leaves the brackets in place.
Did I misunderstand your question?

John

 

[henniec]

John,

I do think you may have miss understood my original post. If you try in your own database and create a query using the IN function you will see that the format of the query require no parenthesis ("")(double quote) but only ('')(single quote). The query require the bracket followed by the single quote(') . In otherwords as the example I quoted

"('Wednesday', 'Thursday', 'Friday')" while I only need ('Wednesday', 'Thursday', 'Friday').

I sure appreciate the code you supplied as I did not know how to use it any how.

I do believe the additional code I supplied from Microsoft overcomes the problem but still I do not know how to incorporate it in my own code. The second function I don't think I need as I get the parameters from my form.

Thank you for your time and patience.

Hennie