1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: MS Access Query To Change Case

Discussion in 'Business Applications' started by snorkytheweasel, Jul 23, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. snorkytheweasel

    snorkytheweasel Thread Starter

    Joined:
    May 3, 2006
    Messages:
    215
    Help! Because I'm the DBA, my employer assumes that I know (or even care about) Access. Because of some personnel changes, I've inherited an Access flat-file DB - with a "request" to clean up the data for the next person who will use and maintain this DB

    If I can get some help getting started, then I can figure out how to do other data clean-up tasks.

    First (and hopefully only) call for help: in Access 2003, how do I convert to upper case, all letters in the "room" field in all records in the table "main"?

    In SQL: UPDATE main SET room=UPPER(room);

    I have zero understanding of VBA (although I used VB 12-15 years ago), and don't know how to get started writing a query using VBA. I've tried reading some books, but they tend to assume prior knowledge, e.g., how to invoke a code editor in which to write the code.

    I could probably use sql and odbc, or export to CSV, import into SQL, fix, and import back into Access. There are probably other possibilities, too. But I have an awful feeling that this is not a temporary situation, so I may as well suck it up and learn Access.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    In Access SQL it is
    UPDATE tblCustomers SET tblCustomers.Customer = Format([Customer],">");
     
  3. snorkytheweasel

    snorkytheweasel Thread Starter

    Joined:
    May 3, 2006
    Messages:
    215
    That fixed it! Thanks.

    The books that I have don't mention "Access SQL." The Access help system sort of alludes to the notion, but isn't helpful to someone who doesn't know anything about the Access SQL that it doesn't mention.

    In Access SQL, are the expressions on the "left side" of the query the same as ANSI SQL? If not, what is a good source of information about the differences between ANSI & Access?

    Is there a good cheatsheet that explains and gives examples for expressions like

    Format([abcd],">");

    I come from a standards-based, cross-platform environment. I try to avoid MS products because of Microsoft's "embrace(unwillingly), and extend(to make it proprietary)" policy that passes for product development.

    My boss understands my preferences for generics, but said "you have to admit that Access's reporting capability is pretty good." I answered "Crystal Reports. Works with any database."
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    I can't provide any answer to the SQL information source apart from the various Forums.
    There is at least one SQL orientated guy on here who may be able to help you, he is called Jimmy the Hand and is Ace at SQL.
    I have a feeling that there are a few others as well, but I am not really sure, possibly Rollin and The villan.
    My forte is in Query development in design mode, but that is nowhere near as good as SQL and I haven't done any training in SQL since the 1990s.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/845802

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice