Help with a code

[DevilDog1978]

I am using the following replace expression:
Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

My records no longer have any of those special characters yet my database
has dropped from 6618 to 4756 records. Is there an easier way to do this? Is
there a problem with my replace command? Any help would be appreciated.

 

[John Spencer MVP]

Are you sure that your database has lost records? That should not occur when
you use the replace function.

Is it possible that the special characters made a difference in the part
numbers. That is 12-A-34 is not the same part as 12A-34.

If you are looking at unique numbers after you replace the characters than
those two part numbers would be rolled into one part 12A34.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[DevilDog1978]

is there a way to keep Access from rolling them into similar part numbers? I
have other fields like CAGE and nomenclature that should identify the part
number. Any way to include them?

Are you sure that your database has lost records? That should not occur when
you use the replace function.

Is it possible that the special characters made a difference in the part
numbers. That is 12-A-34 is not the same part as 12A-34.

If you are looking at unique numbers after you replace the characters than
those two part numbers would be rolled into one part 12A34.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am using the following replace expression:
Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

My records no longer have any of those special characters yet my database
has dropped from 6618 to 4756 records. Is there an easier way to do this? Is
there a problem with my replace command? Any help would be appreciated.

 

[John Spencer MVP]

POST the SQL of the query you are using. (View: SQL from the menu).

That will help us determine what is happening. If your query starts with
SELECT DISTINCT ...
Then removing the word DISTINCT may be the solution.

If you are Grouping, summing, counting, etc. Then you may need to do other
things such as add additional fields to the SELECT clause of your query.n

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

is there a way to keep Access from rolling them into similar part numbers? I
have other fields like CAGE and nomenclature that should identify the part
number. Any way to include them?

Are you sure that your database has lost records? That should not occur when
you use the replace function.

Is it possible that the special characters made a difference in the part
numbers. That is 12-A-34 is not the same part as 12A-34.

If you are looking at unique numbers after you replace the characters than
those two part numbers would be rolled into one part 12A34.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am using the following replace expression:
Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

My records no longer have any of those special characters yet my database
has dropped from 6618 to 4756 records. Is there an easier way to do this? Is
there a problem with my replace command? Any help would be appreciated.

 

[DevilDog1978]

SELECT standards_t3.item_nbr, standards_t3.part_nbr, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WHERE
(((standards_t3.part_nbr)=Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\","")))
WITH OWNERACCESS OPTION;

POST the SQL of the query you are using. (View: SQL from the menu).

That will help us determine what is happening. If your query starts with
SELECT DISTINCT ...
Then removing the word DISTINCT may be the solution.

If you are Grouping, summing, counting, etc. Then you may need to do other
things such as add additional fields to the SELECT clause of your query.n

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

is there a way to keep Access from rolling them into similar part numbers? I
have other fields like CAGE and nomenclature that should identify the part
number. Any way to include them?

Are you sure that your database has lost records? That should not occur when
you use the replace function.

Is it possible that the special characters made a difference in the part
numbers. That is 12-A-34 is not the same part as 12A-34.

If you are looking at unique numbers after you replace the characters than
those two part numbers would be rolled into one part 12A34.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DevilDog1978 wrote:
I am using the following replace expression:
Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

My records no longer have any of those special characters yet my database
has dropped from 6618 to 4756 records. Is there an easier way to do this? Is
there a problem with my replace command? Any help would be appreciated.

 

[John Spencer MVP]

AHA! That will only give you part numbers that don't have any special
characters in them.


You are trying to match the part_nbr as it exists to what the part_nbr is
after you remove any -, /, or \ that is in the part-nbr

A-23-41 is not going to match A2341 and therefore records with A-23-41 as a
part number will be eliminated from the results of the query.

Can you tell us WHAT you are trying to do in words? If you are trying to
standardize the part numbers then move the expressions into the SELECT clause
of the query and drop the WHERE clause.

SELECT standards_t3.item_nbr
, standards_t3.part_nbr
, standards_t3.cage
, standards_t3.mfr_desc
, Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""))) as CleanPartNum
FROM standards_t3
WITH OWNERACCESS OPTION;




John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

SELECT standards_t3.item_nbr, standards_t3.part_nbr, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WHERE
(((standards_t3.part_nbr)=Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\","")))
WITH OWNERACCESS OPTION;

POST the SQL of the query you are using. (View: SQL from the menu).

That will help us determine what is happening. If your query starts with
SELECT DISTINCT ...
Then removing the word DISTINCT may be the solution.

If you are Grouping, summing, counting, etc. Then you may need to do other
things such as add additional fields to the SELECT clause of your query.n

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

is there a way to keep Access from rolling them into similar part numbers? I
have other fields like CAGE and nomenclature that should identify the part
number. Any way to include them?

:

Are you sure that your database has lost records? That should not occur when
you use the replace function.

Is it possible that the special characters made a difference in the part
numbers. That is 12-A-34 is not the same part as 12A-34.

If you are looking at unique numbers after you replace the characters than
those two part numbers would be rolled into one part 12A34.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DevilDog1978 wrote:
I am using the following replace expression:
Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

My records no longer have any of those special characters yet my database
has dropped from 6618 to 4756 records. Is there an easier way to do this? Is
there a problem with my replace command? Any help would be appreciated.

 

[DevilDog1978]

Ok the short and sweet version...I am working with two different databases.
We will call one Standards and one Users. Standards lists the part numbers
correctly. Users has many different variations of the part number in their
database. The easiest way I could figure to match these two was to remove the
"special characters," and then match them up. This will fix a large portion
of the problems with Users.

AHA! That will only give you part numbers that don't have any special
characters in them.


You are trying to match the part_nbr as it exists to what the part_nbr is
after you remove any -, /, or \ that is in the part-nbr

A-23-41 is not going to match A2341 and therefore records with A-23-41 as a
part number will be eliminated from the results of the query.

Can you tell us WHAT you are trying to do in words? If you are trying to
standardize the part numbers then move the expressions into the SELECT clause
of the query and drop the WHERE clause.

SELECT standards_t3.item_nbr
, standards_t3.part_nbr
, standards_t3.cage
, standards_t3.mfr_desc
, Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""))) as CleanPartNum
FROM standards_t3
WITH OWNERACCESS OPTION;




John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

SELECT standards_t3.item_nbr, standards_t3.part_nbr, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WHERE
(((standards_t3.part_nbr)=Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\","")))
WITH OWNERACCESS OPTION;

POST the SQL of the query you are using. (View: SQL from the menu).

That will help us determine what is happening. If your query starts with
SELECT DISTINCT ...
Then removing the word DISTINCT may be the solution.

If you are Grouping, summing, counting, etc. Then you may need to do other
things such as add additional fields to the SELECT clause of your query.n

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DevilDog1978 wrote:
is there a way to keep Access from rolling them into similar part numbers? I
have other fields like CAGE and nomenclature that should identify the part
number. Any way to include them?

:

Are you sure that your database has lost records? That should not occur when
you use the replace function.

Is it possible that the special characters made a difference in the part
numbers. That is 12-A-34 is not the same part as 12A-34.

If you are looking at unique numbers after you replace the characters than
those two part numbers would be rolled into one part 12A34.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DevilDog1978 wrote:
I am using the following replace expression:
Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

My records no longer have any of those special characters yet my database
has dropped from 6618 to 4756 records. Is there an easier way to do this? Is
there a problem with my replace command? Any help would be appreciated.

 

[John Spencer]

OK.

Then it sounds as if Users table is the one with the extraneous
characters. So that one should be the one you fix with the nested
replace code.

You can then join on the CleanPartNum of the query and the good part
number in the standards table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Ok the short and sweet version...I am working with two different databases.
We will call one Standards and one Users. Standards lists the part numbers
correctly. Users has many different variations of the part number in their
database. The easiest way I could figure to match these two was to remove the
"special characters," and then match them up. This will fix a large portion
of the problems with Users.

AHA! That will only give you part numbers that don't have any special
characters in them.


You are trying to match the part_nbr as it exists to what the part_nbr is
after you remove any -, /, or \ that is in the part-nbr

A-23-41 is not going to match A2341 and therefore records with A-23-41 as a
part number will be eliminated from the results of the query.

Can you tell us WHAT you are trying to do in words? If you are trying to
standardize the part numbers then move the expressions into the SELECT clause
of the query and drop the WHERE clause.

SELECT standards_t3.item_nbr
, standards_t3.part_nbr
, standards_t3.cage
, standards_t3.mfr_desc
, Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""))) as CleanPartNum
FROM standards_t3
WITH OWNERACCESS OPTION;




John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

SELECT standards_t3.item_nbr, standards_t3.part_nbr, standards_t3.cage,
standards_t3.mfr_desc
FROM standards_t3
WHERE
(((standards_t3.part_nbr)=Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\","")))
WITH OWNERACCESS OPTION;


:

POST the SQL of the query you are using. (View: SQL from the menu).

That will help us determine what is happening. If your query starts with
SELECT DISTINCT ...
Then removing the word DISTINCT may be the solution.

If you are Grouping, summing, counting, etc. Then you may need to do other
things such as add additional fields to the SELECT clause of your query.n

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DevilDog1978 wrote:
is there a way to keep Access from rolling them into similar part numbers? I
have other fields like CAGE and nomenclature that should identify the part
number. Any way to include them?

:

Are you sure that your database has lost records? That should not occur when
you use the replace function.

Is it possible that the special characters made a difference in the part
numbers. That is 12-A-34 is not the same part as 12A-34.

If you are looking at unique numbers after you replace the characters than
those two part numbers would be rolled into one part 12A34.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DevilDog1978 wrote:
I am using the following replace expression:
Replace(Replace(Replace(Replace(Replace([part_nbr],"-",""),"/",""),"\",""),",",""),".","")

My records no longer have any of those special characters yet my database
has dropped from 6618 to 4756 records. Is there an easier way to do this? Is
there a problem with my replace command? Any help would be appreciated.