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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

boscolil

Thread Starter
Joined
Sep 27, 2008
Messages
7
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

Joined
Mar 8, 2005
Messages
19,895
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

Thread Starter
Joined
Sep 27, 2008
Messages
7
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?
 

boscolil

Thread Starter
Joined
Sep 27, 2008
Messages
7
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top