Field formatting

[cjany]

I have a field which holds a serial number. Some of these serial numbers have "A/B" either at the front of the field, in the middle or at the end. I need to remove the "/B" from this field. I'm working with Access 2000. Any suggestions?

 

[Golom]

Replace([SerialNumber],"/B","")

 

[abaldwin]

Try this in your test environment
Not having your field names or table names you need to modify this with your table and field names.

UPDATE Table1 SET Table1.myField = replace([myfield],"/b","")
WHERE ((InStr([myField],"/b")>"0"));



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[cjany]

OK, let's take this a step further. This serial number field, [MHSERIALNM], could have a combination of A/B, A/, B/A.

 

[lespaul]

well you told us you wanted to strip off the /B and that's what was shown. what do you want to do if it's:

A/B
A/
B/A


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[dhookom]

Andy's syntax should have removed the quotes from around the 0 since the value will be numeric:

UPDATE Table1 SET Table1.myField = replace([myfield],"/b","")
WHERE ((InStr([myField],"/b")>0));

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Melagan]

I've been working on a UDF that will look at a string and parse out given patterns. For this exersize, one could call the function like so:

SerialParse([yourField], "a/b", "a/", "b/a")

...where the red are elements of a ParamArray. My VB skills aren't as sharp as I'd like, thus I am having trouble getting this function to work, but it's an idea that I believe would work here.

psuedo code:

Public Function SerialParse(myField as Variant, ParamArray ParseMe()) as String

dim newString as String

'Here I want to say:
'"For each myField, Replace anything in ParseMe with ""

So if Field0 = "1054A/B551"
This is what I'd like to see in Immediate window:

?SerialParse(Field0, "a/b")
1054551

Or,
Field1 - "A/B105/A5904A/B"

?SerialParse(Field1, "A/B", "/A", "A/")
1055904

Any thoughts? I realize this is a VB solution to a JET question and therefore might be in the wrong forum, but again I believe this could be a powerful solution to the given question.

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Perhaps this expression as a starting point ?
Replace(Replace(Replace([MHSERIALNM],'A/B','A'),'A/','A'),'B/A','A')

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

 

[adalger]

If '/' and 'B' never appear other than as part of these constructs:

Replace(Replace([MHSERIALNM],"B",""),"/","")

 

[Melagan]

Thanks to PH's help in the VB forum, we were able to come up with this function that would offer a good solution here:

New Module:

Public Function SerialParse(ByVal myField As String, ParamArray myArgs() As Variant) As String
Dim newValue As String
Dim x As Variant
newValue = myField

For Each x In myArgs
 newValue = Replace(newValue, x, "")
Next x

SerialParse = Trim(newValue)

End Function

Test environment:
ID MHSERIALNM
1 A/B1024596-2
2 A/1024599-2
3 /B149502334
4 A/159294-B2

SQL Code:

SELECT t.MHSERIALNM
, SerialParse(t.[MHSERIALNM], "A/B", "A/", "/B", "A/") as myParsed

FROM tblTable as t

Results:
MHSERIALNM myParsed
A/B1024596-2 1024596-2
A/1024599-2 1024599-2
/B149502334 149502334
A/159294-B2 159294-B2


~Melagan
______
"It's never too late to become what you might have been.

 

[lespaul]

that's a nice function Melagan, but are you sure that's what the OP needed? There was never a reply to what the OP wanted in the other situations.

leslie

 

[Melagan]

This serial number field, [MHSERIALNM], could have a combination of A/B, A/, B/A.

I just assumed he wanted any / all of those elements taken out of his serial number string. Indeed though, I've been keeping an eye on this thread for the OP to respond for that very reason.

~Melagan
______
"It's never too late to become what you might have been.