Solved: Access 2003 - using the LEN calculation in a form

[boscolil]

I have a table with the text field "keyword". In my form I have a calculated field called "NumberOfChars", which contains the formula "=LEN([keyword])". This works perfectly, but I really want the result to be the number of characters less any spaces. For example, if the keyword is "New Jersey", the NumberOfChars result should read "9" and not "10".

Is there a way of doing this within the LEN formula? I have tried various Excel-type formulas, but get error results for each one.

I would be happy to create another field that copies the keyword and strips out the spaces (and then using the LEN formula to count this), but I can't work out how to do that either.

Any help on working round this problem would be very much appreciated.

 

[OBP]

It would be fairly straight forward to use VBA code to do what you want. You have to work through all of the Characters of the field and Count them if they are not a Space. You would put the Code in the keyword Field's After Update Event so when someone entered or edited a value in the keyword field it would do the calaculation and put the value in the "NumberOfChars" field.
The code would look like this

Dim count as integer, count1 as integer, data as string
data = me.keyword
count1 = 0
for count = 1 to len(data)
if mid(data, count,1) <> " " then count1 = count1 + 1
next count
me.NumberOfChars = count1

 

[boscolil]

Thanks for such a quick response. I've tried doing as you have said (I copied and pasted your code and put it into the "After update" event for keyword). I have removed the =LEN([keyword]) from the Control Source for NumberOfChars, but am not getting a result in the NumberOfChars field. I have tried leaving the Control Source for the chars field blank, and I have tried using "NumberOfChars" (the Number field in the table). Should I be doing something else at the NumberOfChars field?

 

[OBP]

Did you add or change a value in the keyword field?

 

[boscolil]

Everything seems to be working OK today. When I was trying it yesterday I managed to get a figure in the NumberOfChars field, but it was the same number for every record. I don't know how, but it seems to be sorted today.

Thank you VERY much for your help.