Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Thanked 0 Times in 0 Posts

    replacing values - best method (Access 2000)

    I'm working with a survey database. There is one survey per table, one question per field, and values are a-f (text) for each question. I must change all of the letter values to numbers. (Text numbers, rather than integers, are OK). I see that I can use an update query to replace each value one value at a time. That is, I can select each field I need to change, list the criterion as "a" and the update as "1" and then run the update query. Then I can change the criterion for each field to "b" and change the update to "2," and re-run the query, and so on... Is there a more efficient method for doing this? I have yet to find a useful update query expression for my purposes. Many thanks in advance for your advice and expertise.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Arlington, Virginia, USA
    Thanked 3 Times in 3 Posts

    Re: replacing values - best method (Access 2000)

    One method would be to use an update query like this example. I created a dummy table with 52 records, where Field1 is letters A thru Z, both upper & lower case. Query SQL:

    UPDATE Table1 SET Table1.Field2 = CStr(Asc(UCase$([Field1]))-64)
    WHERE (((Table1.Field1) Is Not Null));

    The ASC function converts letter to ASCII character code, while UCase function converts letter to upper case first so you can use same formula for upper & lower case. Since ASCII code for "A" is 65, "B" is 66, etc thru "Z" is 90, you simply subtract 64 from result to get correct number. The CStr function is necessary to convert number to string if Field2 is a text field. If Field2 is a number field (Integer, Long, etc) then omit the CStr function from formula. With my test table this query updated both upper & lower case letters correctly.


Posting Permissions

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