adding underscore

[WHeisenburg]

I'm trying to write some code to loop through a field for every record in a table and perform some conversions. I'm new to VBA and could use some help.

Here's the process. It loops through field "Pin_A" and looks for entries where the format is a lowercase letter and then one number (e.g. 'c6' or 'a2' or 'b3' and so on). When it finds one it will prepend an underscore to the entry. (The new entries would be '_c6' or '_a2' or '_b3', etc.) It's important that it not change entries that have an uppercase letter (e.g. 'C6') or entries with two digits (e.g. b22).

If anyone could help me out it would be much appreciated. It doesn't seem like it would be too difficult but I'm very new to this and in over my head. Most examples I can find or using the replace function don't account for case. Thanks!

 

[alvechurchdata]

I'm trying to write some code to loop through a field for every record in a table

Are you using an update query or stepping though a ADO/DAO recordset?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[WHeisenburg]

I'm not really sure I know what either of those are....

I was trying to do it in Access 97 with either a Macro or a Module. If there is a better way I would certainly be open to using that though.

Thanks again for any help you can give me!

 

[PHV]

UPDATE yourTable
SET Pin_A = '_' & Pin_A
WHERE Pin_A Like '?#' AND (Asc(Pin_A) Between 97 And 122)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CptCrusty1]

I'm rather new at submitting responses, but I've done something similar in another life time. Try searching for the the ASCII code associated with the letters.

ASCII CODES

You can write a function that will assigne the contents of the field to a variable which is passed to the function, i.e. find_lowercase(Pin_A). Of coarse, since I'm trying to help, I can't find the function I used to do this, but... it worked really well. I hope this helps a little bit.

Rich.

 

[WHeisenburg]

Worked Great! Thanks for all your help.