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,424
    Provided Answers: 8
    in one line

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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •