Textbox used to specify delimiter, can't use space in procedure

[mreinsmith]

I want to split some strings and I want to specify what delimiter to use.

I put a space in the delimiter field and click to send it to the click event

Dim strDelimiter As String
strDelimiter = Me.txtDelimiter.Value

msgbox strDelimiter

and I get a Null value error

I want to be able to use other characters of course, I tested a pipe and a dash etc. and they work, but the space is not

How can I do that

Thanks

 

[theDBguy]

Hi. Welcome to AWF!

I think that's happening because Access is maybe cleaning up the empty Textbox before it uses it. If you want to use a space, one possible approach is to use a Combobox. That may work, but it may not be an acceptable solution. The only other approach I could think of is to maybe use the ASCII code rather than the actual character itself, which you can just convert back into the equivalent character in your Split code.

Just a thought...

 

[CJ_London]

Because access usually trims any spaces at the end of the entry you probably are ending up with a zls. Easy enough to check using the Len function

Depends on your code but try using chr(32)

 

[theDBguy]

Oh, I just thought of another possible approach. Perhaps you could ask the user to enclose the delimiter in quotes and just trim them out in your code?

Just thinking out loud...

 

[DHookom]

Have the user enter “s” for space and then handle it in you code.

 

[MajP]

I think I do it with a combo box as suggested. Most likely choices and let them type in others (limit to list = false)

Space
-
;
,
.

 

[GPGeorge]

I think I do it with a combo box as suggested. Most likely choices and let them type in others (limit to list = false)

Space
-
;
,
.

As a general rule, when you want a closed domain of values to be used, a combo box is the most effective way to accomplish that.

A freeform text box control not only has the problem you encountered here, with Access default trimming of spaces, it accepts any old character the user can think up, including some you'd be surprised to see.

If your users want delimiters beyond those you provide in the list of values for the combo box, ask the users to justify including those other delimiters, according the the rules for acceptable delimiters in your environment.

 

[mreinsmith]

Combo box is what I was thinking

I didn't know access did that with a text box, very good to know that, thanks

The only problem would be using multiple characters for the delimiter, for example " - " (space dash space)

I think I'll use a combo that uses a table, that way it will leave the option to type in something as well

A big help, thanks again