Remove space from text

[Acipenser]

Depending on the value of a combo box, I want to open one of a few different forms. I have a case function set up to do this, but the problem is that the combo box value is a text string with a space in it. It appears that the case function will not work due to the space.

What I would like to do is temporarily remove the space in the value of the combo box so that the case function works properly. Any suggestions?

 

[yummy7]

Use trim function.Like
Trim(Combo.Text)

 

[Remou]

The space should not be a problem. For example:
[tt]Private Sub cboCombo_AfterUpdate()
Select Case Me.cboCombo
Case "with space"
MsgBox "With space"
Case "NoSpace"
MsgBox "NoSpace"
Case Else
MsgBox "Whatever"
End Select
End Sub[/tt]

You can permanently or temporarily remove any character with Replace, but I do not think that is the problem.

 

[PHV]

Which case function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Acipenser]

I thought trim would remove leading and trailing spaces. I am interested in removing a space within a single field.

For example:
Value = Red Hat
I want:
Value1 = RedHat

I don't think trim will do that.

 

[PHV]

[tt]strValue1 = Replace(strValue, " ", "")[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Acipenser]

Remou - I tried that and it does not work with the space. When I remove the space, it does.

PHV - I tried that and it does not work either.

Here is a copy of the code in the OnClick Event to open the form. This works if Me.Species is Scaphirhynchusalbus, but not if Scaphirhynchus albus:
Dim stDocName As String
Dim SpeciesSelect As String

SpeciesSelect = Replace(Me.Species, " ", "")

Select Case SpeciesSelect
Case "Scaphirhynchusalbus"
stDocName = "frmSampleViewPallid"
Case Else
stDocName = "frmSampleView"
End Select

DoCmd.OpenForm stDocName

What am I doing wrong?

 

[Remou]

As I mentioned, I do not think it is the space that is causing the problem. What happens when you say (?):
[tt]MsgBox "Selected: " & SpeciesSelect[/tt]
I suspect that SpeciesSelect contains either nothing or a number.

Just to be sure, have you checked that:
[tt]DoCmd.OpenForm stDocName "frmSampleViewPallid"[/tt]
works ok?

 

[Acipenser]

Remou,

I tried the MsgBox and SpeciesSelect is showing the still has the space in the string (Schaphirhynchus albus). So obviously the replace is not working correctly. Thanks for the MsgBox tip. I would not have thought of that.

Well I got it working. It must have been a typo, although I could not find it. I went back and copied and pasted the species name from the table into the code, and it is now working fine!!

You were right from the beginning. Thanks. I checked the spelling a few times. I don't no what I missed!!

Thanks again.