help modifying all records in one field

[Glen]

I have a field in a table that is a part number (PN). This table has
80000 records. I need to search the field for several different items
and format the entries accordingly. For example:
1) check if the PN is numeric
2) if it is numeric, check if there are nine numbers
3) if there are 9 numbers, insert a dash after the second and fifth
numbers
4) if there aren't 9 numbers, input leading zeroes and then insert the
dashes

I could do this without a problem in Excel but I can't seem to access
the field value I want in Access. I need to know how to get the
tables' PN value for each record into a For loop or a Do/While loop so
I can step through the recordset and check each value. Any help is
greatly appreciated.

 

[Douglas J Steele]

No reason to use a loop. A single Update query should be able to handle the
whole thing.

UPDATE MyTable
SET PN = IIf(IsNumeric([PN], IIf(Len([PN]) <= 9, Format([PN],
"00\-000\-0000"), ?1), ?2)

The question marks are because I don't know what you want to do if PN isn't
numeric (that's ?2), or if it's greater than 9 digits (?1)

Of course, this assumes that PN is a text field, set to hold at least 11
characters. If that's not the case, what is it?

BTW, make a copy of the table and run the query against the copy to ensure
that it does what you want: there's no way to undo an Update query.

 

[Glen]

Thanks a lot for your quick response Doug. It is a text field. If it
is greater than 9 charaters or alphanumeric, I want to leave it alone.
How should I handle that.

Oh, and by the way; of course I am working with a copy of the original
right now. Thanks for the warning though. ;-)

 

[Guest]

Backup the database. This will do those part number that have leading or
trailing numbers but not the ones with alpha characters in the middle.

Create a query and add the following columns to check if the PN is all
digits --

Value Check: Val([PN])

0

Left Check: Asc(Left([PN],1)
Criteria -- Between 48 And 57

Right Check: Asc(Right([PN],1)
Criteria -- Between 48 And 57

Number Check: Right(Val([PN),1)
Criteria -- Right([PN,1)

Fill to nine & insert dashes ---
Left(Right(“000000â€&[PN],9),2)&â€-“&
Right(Left(Right(“000000â€&[PN],9),5),3)&â€-“& Right(Right(“000000â€&[PN],9),4)

Use the above to update the PN field with the above criteria.

 

[Douglas J Steele]

To leave the value unchanged, simply put [PN] where I've got the question
marks, so

UPDATE MyTable
SET PN = IIf(IsNumeric([PN], IIf(Len([PN]) <= 9, Format([PN],
"00\-000\-0000"), [PN]), [PN])

 

[Glen]

Thanks Karl. I just thought of something I should have included in the
original post. About 5000 of these records have been formatted with
dashes manually. Is there a way to strip the dashes from all of the
records for character counts with SQL? The replace function works
great in VBA.

 

[Guest]

I left out a closing bracket in --
Number Check: Right(Val([PN]),1)
Criteria -- Right([PN,1)
I do not understand the question. Why strip out dashes. The number check
above will determine that the entry with dashes is not a number and not
perform an update.

 

[Glen]

You're right Karl. I have some part numbers that are alphnumeric, ex:
PL123-4546-12. These part numbers are okay and will work for what I
need. I have some part numbers that have been formatted 12-345-6789, I
could leave these alone too. I have some part numbers that have no
dashes 987654, these need to have leading zeroes placed in the front
and have dashes inserted. Finally, I have some part numbers that have
dashes placed incorrectly and don't have nine numbers, 123-45-67. I
need to get all of the numeric values into the correct format today.
It would be great if I could simply access the cell, field value, or
whatever it is called in access and check it through a loop. This is
just a one time thing and does not need to be pretty. I have been
trying to evaluate fields in a recordset but so far haven't figured out
how to increment to the next record. The part number is the eleventh
indexed field in the recordset, which is really the entire table.
There has to be a better way to get to that value, evaluate it, strip
it if necessary, change it, and go to the next value. Right now, this
is what my code looks like:

Function update_PN()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("CR_CR_MTL")
Do While rst.EOF = False
If rst(11).Value <> Null Or rst(11).Value <> "Null" Then
MsgBox rst(11).Value
' GoTo GoLoop
Else
GoTo GoLoop
End If
GoLoop:
Loop
End Function

 

[Douglas J Steele]

You cannot test values for equality (or inequality) to Null: you must use
the IsNull function.

As well, if it were possible to test for inequality to Null using <> Null,
be aware that your existing condition would generate a message box for every
single row in the record, since every single value will be guaranteed to
either Not Null or not equal to the string "Null"

Try:

If Nz(rst(11).Value, "Null") <> "Null" Then

 

[Glen]

This is the solution I have arrived at for my problem. It seems to
work with no porblems and I am able to access the fields' data record
by record. I appreciate both of you trying to help. Thanks Doug, I
did figure the Null problem out after my last post. I was really just
posting to let both of you know where I was attempting to go. I am not
very comfortable with SQL and there are a lot of function that I just
haven't learned in Access. Thanks again for your help.

Function update_PN()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim N_Val1 As String
Dim N_Val2 As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT CR_CR_MTL.ID, CR_CR_MTL.PN FROM
CR_CR_MTL " & _
"WHERE (((CR_CR_MTL.PN) Is Not Null)) ORDER BY CR_CR_MTL.ID;")
rst.MoveFirst
Do While rst.EOF = False
With rst
If IsNull(!PN) = False Then
N_Val1 = !PN
N_Val2 = Replace(N_Val1, "-", "")
If (IsNumeric(N_Val2)) Then
If (Len(N_Val2) <= 9) Then
N_Val2 = Format(N_Val2, "00\-000\-0000")
.Edit
!PN = N_Val2
.Update
End If
End If
End If
End With
rst.MoveNext
Loop
End Function

 

[Douglas J Steele]

Just be aware that it's almost always more efficient to use SQL, rather than
opening a recordset and looping through all records.

 

[Guest]

I do not see the need for code. Use what I gave you in a query.

Then create a query to look for dashes in the wrong place -- InStr([PN],"-")
will tell you the position of the dash - 0 if there is not dash. Use
criteria >0 and <>4 for not fourth position.