Editting fields of a table using VBA

[Guest]

Hi all; I am relatively new with VBA. I have coding experience (through
school) so I know some of the basics, but I haven't the slightest clue how to
do this. I am trying to write a function or procedure that will go through
one column of a table that I have and format its contents. You see, I have a
huge list of serial numbers (formatted as text) that I want to use in a
query. I want to find serial numbers that match between this table and
another table that I have. Some of the serial numbers from the first table
start with an "S" and then have some numbers. The second table only has the
numbers. What I would like to do is have a function that will go through each
field of the "Serial Number" column and delete the S if there is one
(sometimes there is no "S" present in the first table). Thank you for any of
your help. Again, I am pretty new, so anything simple would be great (if at
all possible).

 

[Douglas J. Steele]

Permanently, or temporarily?

Either way, you don't really need code: a query is sufficient.

Permanently, you'd want something like:

UPDATE MyTable
SET MyField = Mid([MyField], 2)
WHERE MyField LIKE "S*"

Temporarily, you can put something like the following into your query
instead of MyField:

IIf(Left([MyField], 1) = "S", Mid([MyField], 2), [MyField])

In general, it's almost always more efficient to use a query rather than VBA
when they're both capable of doing the same thing.

 

[Guest]

Hey, thanks a lot. Using a query is much easier (I should of thought of it
earlier). Thank you for the quick response as well; I can't tell you how
releved I am that I don't have to code.

Permanently, or temporarily?

Either way, you don't really need code: a query is sufficient.

Permanently, you'd want something like:

UPDATE MyTable
SET MyField = Mid([MyField], 2)
WHERE MyField LIKE "S*"

Temporarily, you can put something like the following into your query
instead of MyField:

IIf(Left([MyField], 1) = "S", Mid([MyField], 2), [MyField])

In general, it's almost always more efficient to use a query rather than VBA
when they're both capable of doing the same thing.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all; I am relatively new with VBA. I have coding experience (through
school) so I know some of the basics, but I haven't the slightest clue how
to
do this. I am trying to write a function or procedure that will go through
one column of a table that I have and format its contents. You see, I have
a
huge list of serial numbers (formatted as text) that I want to use in a
query. I want to find serial numbers that match between this table and
another table that I have. Some of the serial numbers from the first table
start with an "S" and then have some numbers. The second table only has
the
numbers. What I would like to do is have a function that will go through
each
field of the "Serial Number" column and delete the S if there is one
(sometimes there is no "S" present in the first table). Thank you for any
of
your help. Again, I am pretty new, so anything simple would be great (if
at
all possible).