1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by boscolil, Sep 27, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. boscolil

    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.
     
  2. OBP

    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
     
  3. boscolil

    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?
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Did you add or change a value in the keyword field?
     
  5. boscolil

    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.
     
  6. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/753872

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice