Results 1 to 10 of 10

Thread: Character count

  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: Character count

    Hi

    I have the following text (Kevin/Lance/Gill) I need to count the "/" in the field ie. 2, in an access query, what expression do I use and what would it look like.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suppose you could replace the / character and do a comparison with the number of characters


    eg
    select len(mycolumn)-len(replace(mycolumn,"/","")) as NoSlashes from mytable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good thinking Healdem!

    I thought I had seen a function that had the ability to count instances of one string within another, but I couldn't find it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jun 2009
    Posts
    5
    Hi Healdem

    I think you are on the right trek as I get a result but just not the correct one. It keeps giving me a 0 result for all. I tried "substitute" instead of "replace" in excel and that seems to work but for access I need a different expression as "replace" and "substitute" ain't working. PLEASE HELP!!!!! Thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one of the problems that often occurs is that each variant of SQL has slightly different syntax

    for access/JET what you need is something like
    Code:
    SELECT DT_Prod.Prod_Desc, 
    (Len([Prod_desc])-Len(replace([Prod_Desc],"a",""))) AS NoAs,
    (Len([Prod_desc])-Len(replace([Prod_Desc],"e",""))) AS NoEs,
    (Len([Prod_desc])-Len(replace([Prod_Desc],"i",""))) AS NoIs,
    (Len([Prod_desc])-Len(replace([Prod_Desc],"o",""))) AS NoOs, 
    (Len([Prod_desc])-Len(replace([Prod_Desc],"u",""))) AS NoUs
    FROM DT_Prod;
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2009
    Posts
    5

    Character Count

    still does not like the "a" as it is a letter and as soon as I replace the "a" with "/" it gives me an error. HELP ME PLEASE!!!!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Mickydm
    still does not like the "a" as it is a letter and as soon as I replace the "a" with "/" it gives me an error.
    so what are you trying
    what attempts have you made to debug the code
    what attempts have you made to try and resolve this your self

    the reason I'm asking is twofold
    1) I know the sql works, I've tried it on a test database I have
    2) one of the things any developer needs to develop is good approach to debugging, analysing the problem, diagnosing the fault and developing a solution
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2009
    Posts
    5
    I have been working on this for the last week. I am an amateur when it comes to access query building and my sql is very limited but I get by. I know =Len(Name)-Len(substitute(name),"/","")) works in excel but I am trying to convert my data from excel to access and I need to build this formula into access and I am really battling with it. I don't know how to go about debugging? Really sorry if I sound stupid or ignorant but I am still learning here and I am self tought.

  9. #9
    Join Date
    Jun 2009
    Posts
    5

    Character Count

    Healdem

    I apologise. You are a genius, you really made me LOOK at the code I was inputting. I had made a mistake with the code, corrected it and now it is working. thank you soooooooooo much.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nae bother, thats what we (dBForums) are here for.. to help

    thats why I prefer to give vaguer answers, to encourage people to think about what they are doing. an off the cuff solution provided rarely etends the developers skill set.

    you are not the first, heck I doubt any developer can genuinely say that they can't aget a piece of code to work it must tbe the compiler or the whatever. its often down to what you know in your mind you typed as opposed to what you actually typed. the mind can be a tricky beast the eyes may see one thing the minds sees it as something else.

    debugging is all about detail, going through line by line, clause by clause. its about comparing what it should do with what it is doing and trying to understand why it isn't doing what you want.

    developing good debugging approach is transferable no matter what development environment you are in. they are the foundation of a the job, and they are transferable outside the IT world
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •