Modifying a Table Field with Code

[shelieg]

Hi, I'm new to VB and I'm working with data already created in a different program and imported into VB. I'm creating reports and one of the problems that I am having is sorting on a field that is set up as a text field but has numbers. The number field looks this ####-# or ####-##. The problem is that a zero was not put in before where the last number is 1 through 9. (ie...4521-5 instead of 4521-05). So my sort is not sorting correctly. I'm trying to either add a zero without having to manually do it(there are 3000 records). Or get rid of the first five characters and turn it into a number field. Any suggestions on what is the best possible way to do this? I have some programming experience but am new to using visual basic.

 

[KyleS]

Hi,

Here's one way to do it:

Dim rst As dao.Recordset

Set dao = CurrentDB.OpenRecordset("TBALENAME&quot

Do until rst.EOF
If len(rst.fields("FieldName&quot) = 6 then
rst.fields("FieldName&quot = Left(rst.Fields("FieldName&quot,5) & "0" & Right(rst.Fields("FieldName&quot,1)
End If
Loop

rst.close
Set rst = nothing

Or you could create a query and place this in the SQL view:

UPDATE TABLENAME SET [TABLENAME].[FIELDNAME] =
IIF(Len([TABLENAME].[FIELDNAME])=6,Left([TABLENAME].[FIELDNAME],5) & '0' & Right([TABLENAME].[FIELDNAME],1),[TABLENAME].[FIELDNAME])


To just remove the fron five characters, open a blank query and paste this in:

UPDATE TABLENAME SET [TABLENAME].[FIELDNAME] =
IIF(Len([TABLENAME].[FIELDNAME])=6,Right([TABLENAME].[FIELDNAME],1),Right([TABLENAME].[FIELDNAME],2))



Replacing [TABLENAME].[FIELDNAME] with your table and field names..

Hope this helps Kyle :

 

[shelieg]

Thank you so much for your help! I will let you know if this works.

 

[KyleS]

No problem, let me know if you need any more detail,

I would recomend using the SQL first as Access will process that faster than the code... Kyle :

 

[shelieg]

I did use the SQL and it worked great! Thanks again.

Michele