Replace using wildcards??

  • Thread starter Thread starter D
  • Start date Start date
D

D

I have a text field that contains both numbers and text. At the end of some
of the data in this field , it has a partial date -- such as 12/24 - 01/23.
This date could be different for each record that contains it. I would
like this date portion stripped out completely as it is not needed and
interferes with queries when grouping. I have been unable to accomplish this
using wildcards. Is there another alternative?
 
If you could figure out a defining pattern in the string, such as the date
starting at position 25, you could use the Right, Mid, or Left functions to
strip out the part that you don't need.
 
At the end of some of the data in this field , it has a partial date
What I would do is to temporarily add a new field for a flag. Run an
update query on that field where you find '/' 3 characters from the end.
Run a select query to view the flagged records for unwanted date.
Then run update query to remove the unwanted characters on the end of the
record.
 
Thank you for your response. I have been playing with your suggestion but it
still needs some tweaking. Here is what I have:

SELECT Replace([ItemDescription],"*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9] ","") AS NewDescription
FROM [tblItemDescription]
WHERE (((Replace([ItemDescription],"*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9] ","")) Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

It is returning the data that matches but it is not replacing the date
characters. Any thoughts?


Clifford Bass via AccessMonster.com said:
Hi,

You can accomplish it with wild cards:

where YourFieldName like "*[0-9][0-9]/[0-9][0-9]"

Clifford Bass
I have a text field that contains both numbers and text. At the end of some
of the data in this field , it has a partial date -- such as 12/24 - 01/23.
This date could be different for each record that contains it. I would
like this date portion stripped out completely as it is not needed and
interferes with queries when grouping. I have been unable to accomplish this
using wildcards. Is there another alternative?
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Thank you Jerry,

Unfortunately, the field has no defining pattern and it can be of any
length. The only constant is that "some" of the data contains the mm/dd -
mm/dd at the end of the text. I agree that a Left, Right or Mid would have
worked if there had been some kind of common denominator in length or pattern.
 
Replace cannot use wild cards.

IF you want to do this permanently, you could use
UPDATE tblItemDescription
SET ItemDescription = Left([ItemDescription],Len([ItemDescription]-13)
WHERE [ItemDescription] Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

If you need to calculate the NewItemDescription then

SELECT IIF([ItemDescription] & "" Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]",Left([ItemDescription],Len([ItemDescription]-13),[ItemDescription])
FROM tblItemDescription


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thank you for your response. I have been playing with your suggestion but it
still needs some tweaking. Here is what I have:

SELECT Replace([ItemDescription],"*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9] ","") AS NewDescription
FROM [tblItemDescription]
WHERE (((Replace([ItemDescription],"*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9] ","")) Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

It is returning the data that matches but it is not replacing the date
characters. Any thoughts?


Clifford Bass via AccessMonster.com said:
Hi,

You can accomplish it with wild cards:

where YourFieldName like "*[0-9][0-9]/[0-9][0-9]"

Clifford Bass
I have a text field that contains both numbers and text. At the end of some
of the data in this field , it has a partial date -- such as 12/24 - 01/23.
This date could be different for each record that contains it. I would
like this date portion stripped out completely as it is not needed and
interferes with queries when grouping. I have been unable to accomplish this
using wildcards. Is there another alternative?
Click to expand...
--
Message posted via AccessMonster.com


.
Click to expand...
Click to expand...
 
On your first example, I am not getting it to work. Have tried several
things but keep coming up with a Syntax error -- Missing Operator.

Second example, getting the same error.

I am sure it is something simple that I am just mentally missing.

John Spencer said:
Replace cannot use wild cards.

IF you want to do this permanently, you could use
UPDATE tblItemDescription
SET ItemDescription = Left([ItemDescription],Len([ItemDescription]-13)
WHERE [ItemDescription] Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

If you need to calculate the NewItemDescription then

SELECT IIF([ItemDescription] & "" Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]",Left([ItemDescription],Len([ItemDescription]-13),[ItemDescription])
FROM tblItemDescription


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thank you for your response. I have been playing with your suggestion but it
still needs some tweaking. Here is what I have:

SELECT Replace([ItemDescription],"*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9] ","") AS NewDescription
FROM [tblItemDescription]
WHERE (((Replace([ItemDescription],"*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9] ","")) Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

It is returning the data that matches but it is not replacing the date
characters. Any thoughts?


Clifford Bass via AccessMonster.com said:
Hi,

You can accomplish it with wild cards:

where YourFieldName like "*[0-9][0-9]/[0-9][0-9]"

Clifford Bass

D wrote:
I have a text field that contains both numbers and text. At the end of some
of the data in this field , it has a partial date -- such as 12/24 - 01/23.
This date could be different for each record that contains it. I would
like this date portion stripped out completely as it is not needed and
interferes with queries when grouping. I have been unable to accomplish this
using wildcards. Is there another alternative?
--
Message posted via AccessMonster.com


.
Click to expand...
Click to expand...
.
Click to expand...
 
MY ERROR. I left out a closing parenthesis. Try one of these. You do
realize that the first will PERMANENTLY change the data in the field.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.
UPDATE tblItemDescription
SET ItemDescription = Left([ItemDescription],Len([ItemDescription])-13)
WHERE [ItemDescription] Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

This one is non-destructive:
SELECT IIF([ItemDescription] & "" Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]",Left([ItemDescription],Len([ItemDescription])-13),[ItemDescription])
FROM tblItemDescription

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
On your first example, I am not getting it to work. Have tried several
things but keep coming up with a Syntax error -- Missing Operator.

Second example, getting the same error.

I am sure it is something simple that I am just mentally missing.

John Spencer said:
Replace cannot use wild cards.

IF you want to do this permanently, you could use
UPDATE tblItemDescription
SET ItemDescription = Left([ItemDescription],Len([ItemDescription]-13)
WHERE [ItemDescription] Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

If you need to calculate the NewItemDescription then

SELECT IIF([ItemDescription] & "" Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]",Left([ItemDescription],Len([ItemDescription]-13),[ItemDescription])
FROM tblItemDescription


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thank you for your response. I have been playing with your suggestion but it
still needs some tweaking. Here is what I have:

SELECT Replace([ItemDescription],"*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9] ","") AS NewDescription
FROM [tblItemDescription]
WHERE (((Replace([ItemDescription],"*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9] ","")) Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

It is returning the data that matches but it is not replacing the date
characters. Any thoughts?


:

Hi,

You can accomplish it with wild cards:

where YourFieldName like "*[0-9][0-9]/[0-9][0-9]"

Clifford Bass

D wrote:
I have a text field that contains both numbers and text. At the end of some
of the data in this field , it has a partial date -- such as 12/24 - 01/23.
This date could be different for each record that contains it. I would
like this date portion stripped out completely as it is not needed and
interferes with queries when grouping. I have been unable to accomplish this
using wildcards. Is there another alternative?
--
Message posted via AccessMonster.com


.
Click to expand...
.
Click to expand...
Click to expand...
 
FABULOUS!!!

This solves many things and will make my life much easier!! That's what it
is all about .. right!!

John Spencer said:
MY ERROR. I left out a closing parenthesis. Try one of these. You do
realize that the first will PERMANENTLY change the data in the field.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.
UPDATE tblItemDescription
SET ItemDescription = Left([ItemDescription],Len([ItemDescription])-13)
WHERE [ItemDescription] Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

This one is non-destructive:
SELECT IIF([ItemDescription] & "" Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]",Left([ItemDescription],Len([ItemDescription])-13),[ItemDescription])
FROM tblItemDescription

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
On your first example, I am not getting it to work. Have tried several
things but keep coming up with a Syntax error -- Missing Operator.

Second example, getting the same error.

I am sure it is something simple that I am just mentally missing.

John Spencer said:
Replace cannot use wild cards.

IF you want to do this permanently, you could use
UPDATE tblItemDescription
SET ItemDescription = Left([ItemDescription],Len([ItemDescription]-13)
WHERE [ItemDescription] Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

If you need to calculate the NewItemDescription then

SELECT IIF([ItemDescription] & "" Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]",Left([ItemDescription],Len([ItemDescription]-13),[ItemDescription])
FROM tblItemDescription


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

D wrote:
Thank you for your response. I have been playing with your suggestion but it
still needs some tweaking. Here is what I have:

SELECT Replace([ItemDescription],"*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9] ","") AS NewDescription
FROM [tblItemDescription]
WHERE (((Replace([ItemDescription],"*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9] ","")) Like "*[0-9][0-9]/[0-9][0-9] -
[0-9][0-9]/[0-9][0-9]"));

It is returning the data that matches but it is not replacing the date
characters. Any thoughts?


:

Hi,

You can accomplish it with wild cards:

where YourFieldName like "*[0-9][0-9]/[0-9][0-9]"

Clifford Bass

D wrote:
I have a text field that contains both numbers and text. At the end of some
of the data in this field , it has a partial date -- such as 12/24 - 01/23.
This date could be different for each record that contains it. I would
like this date portion stripped out completely as it is not needed and
interferes with queries when grouping. I have been unable to accomplish this
using wildcards. Is there another alternative?
--
Message posted via AccessMonster.com


.

.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top