Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    17

    Unanswered: Count amount of string

    In form (datasheet) I have a three columns "RightColors", "LeftColors" and "AmountOfColors"

    I want to do something like this:

    If I fill 'RightColors' "red;green;blue;", and fill 'LeftColors' "orange;" then in 'AmountOfColors' column should be "4". Sometimes I can fill only 'RighColors' or 'LeftColors;

    I think I should add code in after update event. Is this possible to build code which can count colors using ";"? I need loop for this, right?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    Dim var As Variant
    Dim str As String
    
    If Len(Nz(Me.RightColors.Value, "")) > 0 Then str = Me.RightColors.Value
    If Len(Nz(Me.LeftColors.Value, "")) > 0 then
        If Len(str) > 0 then
            If Right(str, 1) <> ";" Then str = str & ";"
        End If
        str = str & Me.LeftColors.Value
    End If
    If Len(str) > 0 Then
        var = Split(str, ";")
        Me.AmountOfColors.Value = UBound(var) + 1
    End If
    Have a nice day!

  3. #3
    Join Date
    Apr 2013
    Posts
    17
    Thanks! It's works great!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    this is a bad design.....
    Sinndho's suggestion works (as you'd expect) but the reality is its sticking a plaster over a festering sore. it may solve your immediate symptoms, but it doesn't actually 'cure' the underlying problem

    for me the 'cure' is to store the colours in a 'child' table, one row for each colour/product combination. then you can use the SQL count funstion to do what you want easily and simply.

    doing it the way you are requires that you remember to use that function each and everytime there is the possibility of a change of the colour data. unless you can implement your code inside a stored procedure/trigger then its going to cause problems. you are storing derived data for no good reason. You will have a vulnerability for data to be out of step unless you use a stroed procedure/trigger. you will have a vulnerability for data to be out of step if you let userrs direct access to the table or use a datasheet view.

    Im certainbly not criticising Sinndho's code, it does what you want, but it has an underlying vulnerability if userrs enter data using a different separator
    users could inadvertantly use say a comma, colon or other form of separator, or even a space
    storing multiple values in the same column is a recipie for disaster.. dont do it. granted it may be OK for the current use, you may feel you are so far down the path on this app that the fix is 'good enough' and the redesign would cost too much time, which ius your call. but in future don't store derived data
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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