Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting upper to lower case characters (2003)

    Hello,

    I have a table (called ids) which contains the following data:

    ATVL0065

    ATVM0002

    ATV01001

    ATV01002

    ATV01003

    ATV01004

    ATV01005

    l would like to convert the uppercase letters in this table into lowercase
    using the Lcase command.I don't know quite how to do this in VBA,any help
    would be appreciated.


    This is as far as l have got with regards to resolving the problem:


    Dim UpperCase, LowerCase

    UpperCase = "<table name needs to go here>"

    LowerCase = LCase(UpperCase)

    End Sub


    I would like to call the coding from a macro so it runs automatically.
    I think it needs to go into a function but l don't know how to do this.

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Converting upper to lower case characters (2003)

    Why not just run an update query updating the field to LCase. Something like this:

    UPDATE TableName SET TableName.ids = LCase([ids]);

    You could then have a macro call your query to do this.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Converting upper to lower case characters (2003)

    Does this have anything to do with the problem you had before when importing data from a text file.

    Did you end up using the form i sent to solve that problem.

    If you did and this is an extra requirement to change the upper to lower case, then why not add Garry query solution to that form as well. The reason i say this is that i stay away from macros like the plague.

  4. #4
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting upper to lower case characters (2003)

    It has something to do with the earlier problem. I will add Garys solution to your own.
    Thanks for that advice.

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting upper to lower case characters (2003)

    Thats great thanks for that

  6. #6
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting upper to lower case characters (2003)

    I have tried running the update query using the following syntax
    (userids being the table and id being the field) but l get an error ,
    see attachment.

    Any help would be appreciated.


    UPDATE userids SET userids.id = Lcase([id])

    WHERE userids.id is NOT NULL;

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Converting upper to lower case characters (2003)

    If you are trying to run this via VBA code then you need to have the proper syntax as shown below

    Dim strsql As String

    strsql = "UPDATE userids SET userids.id = Lcase([id]) WHERE userids.id is NOT NULL;"
    DoCmd.RunSQL strsql

    If using a Macro, the other option would be to create an update query and then call the query from the Macro.

    HTH
    Regards,

    Gary
    (It's been a while!)

  8. #8
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting upper to lower case characters (200

    Hi,

    Thanks for that.

    I tried to use an update query but could not get it to work, as l got an error, see previous posting. So l will have to try the VBA method.
    Can you tell me how l can call the VBA code so that it runs automatically.
    i.e where do l put the coding?
    how do l call it from a macro?
    Do l put it in an event procedure?
    Is the coding in a new procedure or funtion?

    I do apoligise for asking these questions but l only know the basics of VBA.

    Justin

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Converting upper to lower case characters (200

    Justin,

    The answer to your question is it depends on what you are doing. You originally said you were trying to run a macro. My question to you is how would you execute the macro or "trigger" it to happen. If you are trying to run via code, lets say, you want this code to run each time a person makes a selection in a list box or presses a command button, or activates an object, then you would need to set the event in the objects properties. For example, on a form you have a listbox. Go into design mode of the form and right click on the listbox and select properties then the event tab. The event tab allows code to trigger each time the particular event is met. Lets say you want the code to run each time someone clicks on the listbox. Select the On Click event, use the pulldown to select event procedure and click the little dots to launch the vba editor. Input the code you want to execute there then exit vba. Now, assuming your code is valid, each time someone clicks the listbox that particular code will trigger.

    Does this help?
    Regards,

    Gary
    (It's been a while!)

  10. #10
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting upper to lower case characters (200

    Yes, that helps a lot many thanks for that indeed.

    Ahy probs l will let you know.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Converting upper to lower case characters (2003)

    You may have a missing reference.

    To check this go into VBA behind a form, then Tool/References. If you see the word MISSING in any of the references, uncheck the reference, OK the references, exit VBA and try the query again.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Converting upper to lower case characters (200

    UCase and LCase are standard VBA functions, so they are (or should be) available in expressions and in queries in Access.

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting upper to lower case characters (200

    Problem here is that you're using an Excel command...you need to use StrConv in Access. See screenshot.

    The proper way to run SQL from a form's event (most likely on Open, would be:

    DoCmd.RunSQL "YOUR QUERY'S SQL STATEMENT GOES HERE. DONT FORGET QUOTES AND THE SEMICOLON;"

    Also:

    You may replace the 2 with:
    1 for UPPERCASE
    2 for lowercase
    3 for Proper case

    HTH <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  14. #14
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting upper to lower case characters (200

    I'm sorry then; I've just never used them!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Converting upper to lower case characters (200

    Maybe it's because you're using Access 200... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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