Update problem

[jbphoenix]

I have a linked customer address table in my DB. In this table I have thousands of customer names that start with an *. I * was used in the past to distinguish between ship to and regular addresses. The * is no longer used and is causing problems with searches. What a surprise, I know. I'm trying to figure out a way to update the records and remove the *. However since the * is a wildcard I'm having a hard time isolating just the records that start with the *. Any ideas?

 

[ajetrumpet]

When you say this... *.I* , is the asterik representing part of the customer name that preceeds the ".I" , or is the asterik an actual character in the name string?

Regarless of what you mean, you will need a combination of the Instr() , Left() or Right() , and Len() functions to do this in an update query.

I'm trying to figure out a way to update the records and remove the *. However since the * is a wildcard I'm having a hard time isolating just the records that start with the *. Any ideas?

If you're looking to delete all the characters to the left of the period, plus the period, and end up with field values that contain the characters to the right of the period, use this...

Right([field], (Len([field]) - Instr([field], ".")))

 

[jbphoenix]

Oops I had a type-o. Customer names appear like this "* ABC Company" or some are like this " * DEF Company" and even some are like this "*Company".

 

[ajetrumpet]

Oops I had a type-o. Customer names appear like this "* ABC Company" or some are like this " * DEF Company" and even some are like this "*Company".

So what do you want out of "DEF company"? how about "*company"? What do you want the end result to look like?

 

[jbphoenix]

I need to remove all the "*". All I want is the company name.


Example - this is what is currently in DB
* ADT SECURITY SERVICES

I want ADT SECURITY SERVICES

 

[ajetrumpet]

I need to remove all the "*". All I want is the company name.


Example - this is what is currently in DB
* ADT SECURITY SERVICES

I want ADT SECURITY SERVICES

Is there always a space after the asterik? If there is, this will do...

Right([field], (Len([field]) - Instr([field], "*")) + 1)

 

[jbphoenix]

Thanks for you help Adam!

 

[boblarson]

If you want it out of there period, use the Replace function

 

[jbphoenix]

Awesome, thanks Bob. I want it gone.