Spacing Question

[npierson]

1. I created a combo box that contains values queried from a table field.

2. I created another combo box that auto populates with the corresponding value of the one selected in the first combo box from the same table (just of a different field).

3. This works EXCEPT: Since some of the cells in the table have a lot of spacing between certain characters, the combo box that queries these values has a lot of spacing well. If the characters are really far apart, occasionally the combo box that is supposed to auto populate will not do so. I could go through and change the spacing, but the table is very large, and that would take a long time. Is there a simpler way to solve the problem as to why the combo box that is supposed to auto populate doesn't recognize some values where characters are spaced far apart?

Thanks,

Nick

[This message has been edited by npierson (edited 06-08-2001).]

[This message has been edited by npierson (edited 06-08-2001).]

 

[raskew]

It would probably be beneficial to go ahead and fix the table, rather than messing with designing workarounds.

To assist, the following function will remove all spaces from a string. You could call it from an update query to eliminate unwanted spaces.

Function despace(thestuff As String)
'*******************************************
'Name: despace (Function)
'Purpose: Removes all spaces in a string
'Inputs: From debug window:? despace("the quick brown fox")
'Output: "thequickbrownfox"
'*******************************************

Dim strHold As String
strHold = RTrim(thestuff)
Do While InStr(strHold, " ") > 0
strHold = Left(strHold, InStr(strHold, " ") - 1) & Mid(strHold, InStr(strHold, " ") + 1)
Loop
despace = strHold
End Function

 

[npierson]

For "The Stuff", would I just put cbodescription if this contains the values where I would like to eliminate the spacing?

Thanks,

Nick

[This message has been edited by npierson (edited 06-15-2001).]

 

[npierson]

This is probably the last question that I will ask for a while, but I really need to figure this out, so if you can, please help me figure this out.

Thanks

 

[npierson]

Where would I put the function? Also, would I have to call it from another function?

Thanks,

Nick

 

[Atomic Shrimp]

To utilise the function that raskew has given you, you need to paste it into a new module in the 'modules' tab of your database, save tham module (call it 'Public functions' if you like.

(you might need to change:

Function despace(thestuff As String)

to read

Public Function despace(thestuff As String)

but I'm not sure about this)

Then, wherever in your code/recordsource you need to strip spaces out of [FieldName] you just need to use DeSpace([FieldName]) instead.


[This message has been edited by Mike Gurman (edited 06-21-2001).]

 

[npierson]

Mike and raskew,

Thank you very much for the help. The function you gave me does eliminate the spacing between characters that are close to each other, but the original field typically has one really big space (as it is a memo field), and the function does not eliminate this space. Those are the values that the query is not recognizing. Any suggestions? . . . Contrary to what someone else posted, I do recognize that this is purely volountary and you have no obligation to help me answer my questions, and I appreciate any help that I get from anyone on this board because of that.

Thanks,

Nick

 

[Atomic Shrimp]

Stupid question probably, but does it need to be a memo field?

I'm not entirely sure what you mean by 'one big space' - are we talking about a row of space characters, or something else?

You might like to try the opposite approach; rather than removing unwanted characters(spaces in this case), you could filter the string through a list of permitted characters, with a function like this one:

Public Function PermitOnly(yourstring As String)
Dim Allowed As String
Allowed = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim MyLoop As Integer
For MyLoop = 1 To Len(yourstring)
    If InStr(Allowed, Mid(yourstring, MyLoop, 1)) > 0 Then
        PermitOnly = PermitOnly & Mid(yourstring, MyLoop, 1)
    End If
Next MyLoop

End Function

Usage is the same as the other function you've tried; the important thing is to put a complete list of your allowed characters into the 'allowed' string.

HTH

Mike

[This message has been edited by Mike Gurman (edited 06-21-2001).]

 

[npierson]

Once again, thank you for your time and efforts in trying to help me solve this problem. I tried using the new function, and I included every possible character that would be in the database as "allowed", but nothing showed up. As far making the memo field a text box, if it would allow me to eliminate this problem, yes I would definitely make it a text box; however, my supervisor told me he preferred that it would be memo field because of the pototential for really long entries. This memo field (in the table) is updated through a text box on a form. In this text box, the users tend to press enter a few times after each line they type, hence the big spacing differences in the memo field in the table.

Thanks,

Nick

 

[Atomic Shrimp]

I have to confess that i'm still rather unclear on the precise details of what you're attempting.

I know that in certain circumtances, memo fields can't be used just as if they were big text fields, although I'm a little fuzzy on the details.

I don't know if this will help, but sometimes if I've got, say 'mailing address' in a memo field (i.e. the whole name and address in one field), I also create a text field and call it 'short name' and use that for searches and sorts.

 

[Atomic Shrimp]

I think it would help me to grasp the problem if you could tell me what sort of data you're storing in the memo field.

 

[D-Fresh]

Just an opinion here, not too sure, but Could this have anything to do with Carriage Returns... Getting rid of space won't get rid of Carriage Returns, right? Just a thought.

Doug

 

[Atomic Shrimp]

Quite right Doug, but the function I posted should eliminate them (unless INSTR can't see 'em - anyone?)

[This message has been edited by Mike Gurman (edited 06-22-2001).]

 

[npierson]

I changed the Data Type to text instead of memo, but it still doesn't work. I am storing product descriptions in this field. The field is updated through a text box on a form. The users tend to enter data like:
TEXT BOX
******************************************
Product[A345X989]

-Requested that amount on balance page be moved
******************************************

which causes a really big space in the table field since they press enter between the data.

This is very generous of you to take the time to help me solve this problem, please let me know if you need any more information.

Thanks,

Nick

 

[D-Fresh]

Just tested Mike's function with a textbox with spaces and carriage returns and it worked for me.. Are you sure you copied the code correctly? Are you sure you're passing the right string to the function? It's usually the little things that get me.

Doug

 

[npierson]

I tried the function yesterday, but it did not work. I typed in virtually every possible character for "Allowed" but it wouldn't work; however, I just tried it again, and, I don't know what I did differently, but it finally works. You people (Mike, D-Fresh, Raskew, and all the other people who willingly take the time to help others figure figure out their problems) are saviors. Thanks you very much for your help, and I will try not to ask another question for a while, as I have been asking too many.

I can't thank you enough,

Nick