Problem with expression

[DevilDog1978]

Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

I am using this expression and after running a query my records drop from
6618 to 4756. Some of the part numbers have a combination of the above
characters so is this deleting them? I need some type of code or expression
that will leave only alpha-numeric characters in the part_nbr field.

 

[KARL DEWEY]

Is this in a totals query? Removing these characters from your part number
where you are using Group By will reduce the record count.

 

[DevilDog1978]

It is a select query. How do I change it so it does not delete the records?

Here is the SQL for the Query:

SELECT standards_t3.item_nbr, standards_t3.part_nbr, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WHERE (((standards_t3.part_nbr)=StripString([part_nbr])))
WITH OWNERACCESS OPTION;

And the StripString:

Function StripString(strInput As String) As String
Dim objReg As Object

Set objReg = CreateObject("VBScript.RegExp")

With objReg
.IgnoreCase = True
.Multiline = False
.Global = True
.Pattern = "[^a-z|^0-9]"
StripString = .Replace(strInput, "")
End With

Set objReg = Nothing

End Function

Is this in a totals query? Removing these characters from your part number
where you are using Group By will reduce the record count.

Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

I am using this expression and after running a query my records drop from
6618 to 4756. Some of the part numbers have a combination of the above
characters so is this deleting them? I need some type of code or expression
that will leave only alpha-numeric characters in the part_nbr field.

 

[KARL DEWEY]

It is not 'deleting' records but just not pulling them.

You can not.

Your query says to pull only those records where the part number equals the
srtipped part number. Any record whose part number had any of those
characters would not be pulled.

Maybe this is what you want --
SELECT standards_t3.item_nbr,
Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","") AS Part_num, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WITH OWNERACCESS OPTION;

If not, then what are you trying to accomplish?

It is a select query. How do I change it so it does not delete the records?

Here is the SQL for the Query:

SELECT standards_t3.item_nbr, standards_t3.part_nbr, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WHERE (((standards_t3.part_nbr)=StripString([part_nbr])))
WITH OWNERACCESS OPTION;

And the StripString:

Function StripString(strInput As String) As String
Dim objReg As Object

Set objReg = CreateObject("VBScript.RegExp")

With objReg
.IgnoreCase = True
.Multiline = False
.Global = True
.Pattern = "[^a-z|^0-9]"
StripString = .Replace(strInput, "")
End With

Set objReg = Nothing

End Function

Is this in a totals query? Removing these characters from your part number
where you are using Group By will reduce the record count.

Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

I am using this expression and after running a query my records drop from
6618 to 4756. Some of the part numbers have a combination of the above
characters so is this deleting them? I need some type of code or expression
that will leave only alpha-numeric characters in the part_nbr field.

 

[Hans Up]

It is a select query. How do I change it so it does not delete the records?

Here is the SQL for the Query:

SELECT standards_t3.item_nbr, standards_t3.part_nbr, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WHERE (((standards_t3.part_nbr)=StripString([part_nbr])))
WITH OWNERACCESS OPTION;

Just to confirm ... your WHERE clause excludes any records whose
part_nbr includes "special" characters. It that what you want?

 

[DevilDog1978]

YOU ARE AWESOME!!!!!!!! Thank you very much.

It is not 'deleting' records but just not pulling them.

You can not.

Your query says to pull only those records where the part number equals the
srtipped part number. Any record whose part number had any of those
characters would not be pulled.

Maybe this is what you want --
SELECT standards_t3.item_nbr,
Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","") AS Part_num, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WITH OWNERACCESS OPTION;

If not, then what are you trying to accomplish?

It is a select query. How do I change it so it does not delete the records?

Here is the SQL for the Query:

SELECT standards_t3.item_nbr, standards_t3.part_nbr, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WHERE (((standards_t3.part_nbr)=StripString([part_nbr])))
WITH OWNERACCESS OPTION;

And the StripString:

Function StripString(strInput As String) As String
Dim objReg As Object

Set objReg = CreateObject("VBScript.RegExp")

With objReg
.IgnoreCase = True
.Multiline = False
.Global = True
.Pattern = "[^a-z|^0-9]"
StripString = .Replace(strInput, "")
End With

Set objReg = Nothing

End Function

Is this in a totals query? Removing these characters from your part number
where you are using Group By will reduce the record count.

:

Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

I am using this expression and after running a query my records drop from
6618 to 4756. Some of the part numbers have a combination of the above
characters so is this deleting them? I need some type of code or expression
that will leave only alpha-numeric characters in the part_nbr field.

 

[DevilDog1978]

No. I am a newby so I am still trying to figure this all out. Could you help
with this one:

SELECT pcinventry.model,
Replace(Replace(Replace(Replace(Replace([model],"-",""),"/",""),"\",""),",",""),".","")
AS model, pcinventry.fscm, pcinventry.serial, pcinventry.ecn, pcinventry.nomen
FROM pcinventry
WITH OWNERACCESS OPTION;

I keep getting an error that says there is a circular referece caused by a
'model' in my definitions SELECT list

It is a select query. How do I change it so it does not delete the records?

Here is the SQL for the Query:

SELECT standards_t3.item_nbr, standards_t3.part_nbr, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WHERE (((standards_t3.part_nbr)=StripString([part_nbr])))
WITH OWNERACCESS OPTION;

Just to confirm ... your WHERE clause excludes any records whose
part_nbr includes "special" characters. It that what you want?

 

[Hans Up]

No. I am a newby so I am still trying to figure this all out. Could you help
with this one:

SELECT pcinventry.model,
Replace(Replace(Replace(Replace(Replace([model],"-",""),"/",""),"\",""),",",""),".","")
AS model, pcinventry.fscm, pcinventry.serial, pcinventry.ecn, pcinventry.nomen
FROM pcinventry
WITH OWNERACCESS OPTION;

I keep getting an error that says there is a circular referece caused by a
'model' in my definitions SELECT list

Yeah, I think I can help with that one. All them Replaces are
distracting. The first field in your SELECT list is model. The next
one is an expression which you're as trying to call model.

Don't do that! Give the second one a different name. model_2?