ib11 ib11 - 3 months ago 13
SQL Question

Access VBA Search and Repace in Table

I have a table in an Access Database that have data filled in. It was filled in over a 25 year period so there are inconsistencies.

There is one column however that is OK. And I want to use this column to fix the data in another column. Sample follows:

Column1 | Column2
ICDS-1 | 01
ICDS-1A | 1A
ICDS-2 | 02
ICDS-3 | 3
ICDS-4A | 04
OTHER |
...


What I want to do with an Access VBA macro is simply to use the data in
Column1
and split it at the hyphen. And if no hyphen the row should be skipped:

Column1 | Column2
ICDS | 1
ICDS | 1A
ICDS | 2
ICDS | 3
ICDS | 4A
OTHER |
...


While it seems rather a simple task that I could do in no time in Excel, I cannot seem to be able to get started with the Access VBA, even after googling for some while already.

Or am I on a totally wrong track and should I use some SQL query?

Any help is appreciated. Thanks.

Update: With OpiesDad answer the following VBA code (now with the actual table and column names) does the replacement, except for it does not skip the rows with no hyphen (I updated this point in my question above):

DoCmd.RunSQL
"UPDATE [MASTER TABLE]
SET [SERIES ABB] = Left([SERIES ABB], InStr([SERIES ABB], ""-"") - 1),
[# IN SERIES] = right([SERIES ABB], Len([SERIES ABB]) - InStr([SERIES ABB], ""-""))"


Update2: Solved, see OpiesDad answer with final query and VBA code.

Answer

You don't need VBA for this.

Run this SQL:

SELECT Iif(InStr([Column1],"-") = 0, [Column1], left([Column1], InStr([Column1], "-") - 1)) As NewCol1
       , Iif(InStr([Column1],"-") = 0, "", right([Column1], Len([Column1]) - InStr([Column1], "-"))) As NewCol2
       , [Column1]
       , [Column2]
FROM myTable

The InStr function finds the value you are looking for and returns the place in the string where it is found (the first position is position 1). The Left and Right functions return the selected number of characters from the left and right of the string respectively.

this will show you the results (and not overwrite your table).

To overwrite the table do:

UPDATE myTable
SET Column1 = left([Column1], InStr([Column1], "-") - 1)
   , Column2 =  right([Column1], Len([Column1]) - InStr([Column1], "-"))
WHERE (InStr([Column1], "-") > 0 )

The VBA code to execute the update query (with your actual table and column names):

DoCmd.RunSQL 
   "UPDATE [MASTER TABLE] 
    SET [SERIES ABB] = Left([SERIES ABB], InStr([SERIES ABB], ""-"") - 1), 
        [# IN SERIES] = right([SERIES ABB], Len([SERIES ABB]) - InStr([SERIES ABB], ""-""))
    WHERE (InStr([SERIES ABB], ""-"") > 0 )"
Comments