ComboBox Selection

[learning_codes]

Hi,

I am trying to put CHR(34) on the combo box but I did not get any
results. Here is the code that I try to set up. Can you help me if I
did the right thing or did I miss something?

strYear = "='" & Me.cboYear.Value & "'"
strlocation = "=' & Chr(34) & Me.cboLocation.Value & Chr(34)'"
strsport = "=' & Chr(34) & Me.cboSport.Value & Chr(34)'"

DoCmd.SetWarnings False

strFilter = "[Location] " & strLocation & " and [Sport] " &
strSport

I'm only having trouble running the report when I select the name of
the location that has single quoate and also the name of sport that
also has single quaote.

Your feedback would be much appreciated.
Thanks

 

[Wayne Morgan]

You will run into problems if your string contains the same character you
are using as a delimiter (the same problem occurs with double quotes). When
that is the case, to get the delimiter character to be taken as a literal
character and not as a delimiter, you need to double it.

Example:
strTest = 'O'Hare'
will fail, but
strTest = 'O''Hare'
will work. That's 2 single quotes, not one double quote. If you then issue
the command
Debug.Print strTest
you will get
O'Hare
in the debug window.

The simplest answer in this situation is to use the Replace function to
change any single quotes in the string to 2 single quotes.

Example:
strlocation = "=' & Chr(34) & Replace(Me.cboLocation.Value, "'", "''") &
Chr(34)'"

This will double up any single quotes that may be in the string so that they
will taken as literal characters and not delimiters.

To test the results, use a Debug.Print statment on the variable, as in the
example above, to see if you're getting the value and syntax desired
assigned to the variable.

 

[learning_codes]

Hi

Thanks for getting back to me.

I tried and it works with all list with single quoate. When I select
"Team B" but I did not get any result but I select "Team B's Hockey", I
got result.

Is there a way for either single quoate or without quoate when
selecting a list from the combo box?

Your help would be much appreciated.

Thanks

 

[Wayne Morgan]

I don't understand what you're asking. If there are no quotes embedded in
the string or if the type of quotes imbedded in the string are different
than the type of quotes you're using as a delimiter, there should be no
problem. Therefore, there would be nothing to do. Can you give me a better
example? What exactly is being selected and what exactly do you want the
output to be?