Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Unanswered: adding "." to a string

    Hi all i have a bit of a formatting problem, im using access:

    I have a database filled with codes.
    If i have for example :
    B22

    I need to to be formated 'B22..' also if there is a string which is 'B222' i need it to show 'B222.' An needs to be a maximum of 5 characters long.. so in other words if the string doesnt make up to the 5 maximum characters i need to add a full stops till it reaches a 5 bytes string.

    I know i can add
    Code:
    Expr2: [Field54]+".."
    Is it an iif statement maybe.
    How can i achieve this help much appriciated.

  2. #2
    Join Date
    Oct 2003
    Posts
    311
    Try select case in vba using Len(fldName)

    Select Case Len(dept)
    Case 1
    dept =dept & " ." '3spaces then the . dont know what data access ur using ado? or dao
    Case 2
    dept =dept & " ." '2spaces then the .

    End select

    I dont recall the constant for a space in vba , try looking it up to give u cleanr code

    M~

    More info would be helpful

  3. #3
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    im using access.. but if its advantage i may use ado

  4. #4
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I hope this code works for you... I just wrote it and it is untested.

    Code:
    If Len(yourfieldhere.value) < 5 Then
        Len(yourfieldhere.value) = strCount
        5 - strCount = Dots
    End If
    If Dots > 0 Then
        Select Case Dots
            Case 1
                yourfieldhere.value + "...." = yourfieldhere.value
            Case 2
                yourfieldhere.value + "..." = yourfieldhere.value
            Case 3
                yourfieldhere.value + ".." = yourfieldhere.value
            Case 4
                yourfieldhere.value + "." = yourfieldhere.value
            Case 5
                yourfieldhere.value
        End Select
    End If
    Hope that works for you.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  5. #5
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    LOL I could simplify what I just did by using the above two examples .

    Code:
    Select Case Len(yourfield.value)
        Case 5
            yourfield.value
        Case 4
            yourfield.value & "."
        Case 3
            yourfield.value & ".."
        Case 2
            yourfield.value & "..."
        Case 1
            yourfield.value & "...."
    End Select
    Hope this or the example above helps.
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  6. #6
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    thanks ... is there a way i can do this using access though throught the query designer?

  7. #7
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I dont know of one

    is there a way i can do this using access though throught the query designer?
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  8. #8
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    ok mate.. looks like ado then

  9. #9
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    what is the difference between ADO and VBA?? I dont understand what ADO DAO and stuff is. If someone could please explain.

    Thanks,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  10. #10
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,478
    Provided Answers: 11
    in one line

    Expr2: [Field54] & String(IIf(5 - Len([Field54]+"..") < 0, 0, 5 - Len([Field54]), ".")
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  11. #11
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    i think vba(visual basic for applications) is using vb code inside say access.. and ActiveX Data Objects (ADO) enables you to use the objects of say access inside a vb application... Correct me if me im wrong anyone one..

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    To throw in another alternative:

    Expr: Left([Field54] & ".....",5)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    pootle you are on a roll

    Thanks davieboy_xr for the explanation. That has been forever bothering me

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nah, just avoiding all the things I'm supposed to be doing
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    I think this may be the way to go but it comes up with ' the expression you entered has a function containing the wrong number of arguments.. .
    Code:
    Expr2: [Field54] & String(IIf(5 - Len([Field54]+"..") < 0, 0, 5 - Len([Field54]), ".")

Posting Permissions

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