DataBase - Access Query

I have a table with approx 270K records.  One colum contains an ID codes i.e. WATUA1, WAXUB0 etc.



I need to change the last to letters to AA, i.e. WATUAA, WAXUAA etc.



How would you write an update query to make the adjustment.



Thanks

Ken

  John Spencer replied to Ken Taylor

STEP 1:  BACKUP your data before attempting the following.
STEP 2:  BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [YourTable]
SET [ID Codes] = Left([Id Codes],Len([Id Codes])-2) & "AA"
WHERE [Id Codes] is Not Null AND [Id Codes] <> ""


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

longitud falsa DataBase Contador1 = 1 MsgBox "LEN (CONTADOR1) : " & Len(Contador1) & " contador1 : " & Contador1 Siempre me dice que LEN(CONTADOR1) : 2 ¿porque sale que la longitud e Contador1 es igual a 2 cuando justo 1 = > " 1"; -1 = > "-1" Si quieres saber la longitud de una variable numerica deberías hacer: LEN(CSTR(Contador1)) Saludos los campos Contador1 y CONTADOR2 son integer Contador1 = 1 CONTADOR2 = 22 MsgBox "LEN (CONTADOR1) : " & Len(Contador1) & " LEN (CONTADOR2) : " & Len(CONTADOR2) el resultado es que en los dos tienen me da la Longitud = 2 ¿como

Automazione in word DataBase Modello = CurrentDb.Name Modello = Left(Modello, Len(Modello) - Len(Dir(Modello))) & On Error Resume Next Set Wrd = GetObject(, "Word.Application") If Err.Number = 429 il modello di word = E8 la seguente: Modello = 3D CurrentDb.Name Modello = 3D Left(Modello, Len(Modello) - Len(Dir(Modello))) & On Error Resume Next Set Wrd = 3D GetObject(, "Word.Application") If Err.Number ho parole! Ciao Vladimiro. keywords: Automazione, in, word description: Modello = CurrentDb.Name Modello = Left(Modello, Len(Modello) - Len(Dir(Modello))) & Anteprima_Comunicato.dot On Error Resume Next Set Wrd = GetObject(,

way, just use DoCmd.RunSQL or is there a better way? I know the PK. - - Len _ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ _ remove nothing for valid email address. Access Discussions DoCmd.RunSQL (1) CurrentDb.Execute (1) GstrLogonName (1) LogonName (1) Gruß (1) Len (1) Hi Len, .Len B schrieb: i would use CurrentDb.Execute "DELETE * FROM . . ." because this will not generate a like DoCmd.RunSQL will do. - - Gru?, Bernd - -- Access goes Subversion - http: / / oasis.dev2dev.de | Hi Len, | | .Len B schrieb: | > I have a table whose primary key is text (length 50). | > I need RunSQL. Since I only want to kill one record I guess it makes no difference. - - Len _ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ _ remove nothing for valid email address. keywords: Delete, record, using, vba description: I have