I need your help in removing unwanted blanks and hyphens

[Gilbert Noetzel]

Guys & Gals

Apparently my previous message is either being ignore or not being
considered worthy of anyone challenge in this forum:

Below is the data in the field CHRDES

CHRDES = " - Comp. Fee"
CHRDES = " - Finance Fee"
CHRDES = " Auto Miles"
CHRDES = "Accounts Receivables"

With the above data in the fields CHRDES there are three things I need
to remove, the "blank,blank,hypen,blank", "blank,hyphen,blank' and "blank"

Here is the script I tought would work:

IIf(Left([CHCHDS],4)=" - ",Right([CHCHDS],26),[CHCHDS]) or
IIf(Left([CHCHDS],3)=" - ",Right([CHCHDS],27),[CHCHDS]) or
IIf(Left([CHCHDS],1)=" ",Right([CHCHDS],29),[CHCHDS])

But I am getting the result -1 and not the intended result.

End result would be the following: (removing the left most blanks and
hyphens)

"Comp. Fee"
"Finance Fee"
"Auto Miles"
.....

Can anyone see where I am I am doing wrong...

Gil

 

[Rick B]

By performing a search in google, I found a replace function by Brendan
Reynolds that let's you specify what to replace (" - ") and what to replace
it with(""). Give it a try...

If this is Access 2000, you can't use the Replace function directly in a
query (you can in Access 2002). In Access 2000, you need to create your own
VBA function that calls the Replace() function, then call your function in
the query:

Public Function Rep(RepInThis As String, RepThis As String, RepWithThis As
String) As String


Rep = Replace(RepInThis, RepThis, RepWithThis)


End Function


Then in your query:


ReplacedValue: Rep([YourFieldName], " - ", "")



Note that you can very often find answers by searching previous posts in
google or a similar search engine.

 

[Vloed]

Can you use a standard update query like this?
UPDATE Table SET Table.CHRDES = "Comp Fee"
WHERE (((Table.CHRDES)="- Comp. Fee"));

 

[Gilbert Noetzel]

Rick -

I am using Access 2003 and in the Query, it does not allow Rep in the
Expression Builder.

Your suggestion sounds what I am looking for, however, As my examples
below show that I just wanted to remove the left most of the data that
contains either a space or hypens. There are several varriation and the
example was the 3 most common ones.

Gil

 

[Rick B]

Did you create the function "Rep" first?

You may have to run three or four queries with the various options that
could be found.

I would probably add a criteria to my query to only pull items where the
left three characters are " - ", and build my replace with those characters.
I might then pull all records where the first four are " - ", etc.

That way you won't pull items where there is a hyphen in the middle of the
text.



Another option...

Pull all records where the left is " - " and replace it using a MID
statement to grab everything to the right of the four.
Then, pull all records where the left is " - " and replace it using MID...

In other words, run the report several times.

--
Rick B

Rick -

I am using Access 2003 and in the Query, it does not allow Rep in the
Expression Builder.

Your suggestion sounds what I am looking for, however, As my examples
below show that I just wanted to remove the left most of the data that
contains either a space or hypens. There are several varriation and the
example was the 3 most common ones.

Gil

By performing a search in google, I found a replace function by Brendan
Reynolds that let's you specify what to replace (" - ") and what to
replace it with(""). Give it a try...

If this is Access 2000, you can't use the Replace function directly in a
query (you can in Access 2002). In Access 2000, you need to create your
own
VBA function that calls the Replace() function, then call your function
in
the query:

Public Function Rep(RepInThis As String, RepThis As String, RepWithThis
As
String) As String


Rep = Replace(RepInThis, RepThis, RepWithThis)


End Function


Then in your query:


ReplacedValue: Rep([YourFieldName], " - ", "")



Note that you can very often find answers by searching previous posts in
google or a similar search engine.

 

[Gilbert Noetzel]

Rick -

So you are saying that I am unable to use the IIF statement to create 3
different possibilites?

I really do not want to create multiple report runs to achieve the
desire result.

Here is what we are doing. We are migrating to a new computer system.
The old system contains DB2 files on an AS/400 system. I was able to
Import the file into MS Access 2003. Now, I need to create a query to
format the data according to the new system data base layout. However,
the management do not want those left most spaces or hyphens to be
included in the migration. Therefore, I am trying to use the Query to
generate the desire result. Once the result is achieved, then I create
the Report which will export to an Excel/CVS data file for importing
into the new system.

Access 2003 is a great medium for migration, but formating the
information or layout is something daunting, but doable.

I will try to create the funtion Rep... but I sure would want to figure
out if there was a way to do this with IIF or any other ways.

Gil

 

[Michael Gramelspacher]

Guys & Gals

Apparently my previous message is either being ignore or not being
considered worthy of anyone challenge in this forum:

Below is the data in the field CHRDES

CHRDES = " - Comp. Fee"
CHRDES = " - Finance Fee"
CHRDES = " Auto Miles"
CHRDES = "Accounts Receivables"

With the above data in the fields CHRDES there are three things I need
to remove, the "blank,blank,hypen,blank", "blank,hyphen,blank' and "blank"

Here is the script I tought would work:

IIf(Left([CHCHDS],4)=" - ",Right([CHCHDS],26),[CHCHDS]) or
IIf(Left([CHCHDS],3)=" - ",Right([CHCHDS],27),[CHCHDS]) or
IIf(Left([CHCHDS],1)=" ",Right([CHCHDS],29),[CHCHDS])

But I am getting the result -1 and not the intended result.

End result would be the following: (removing the left most blanks and
hyphens)

"Comp. Fee"
"Finance Fee"
"Auto Miles"
....

Can anyone see where I am I am doing wrong...

Gil

UPDATE MyTable SET MyTable.my_field = Replace(Replace(Replace
(MyTable.my_field,"-","")," ",""),".","");

Removes spaces, hyphens and periods. Use your real table and
field names.

 

[John Spencer]

If you are going to use IIF you will need to nest IIF statements and you
cannot use OR.

Possibly you want the following.
IIf(Left([CHCHDS],4)=" - ",Mid([CHCHDS],5),
IIF(Left([CHCHDS],3)=" - ",Mid([CHCHDS],4),
IIf(Left([CHCHDS],1)=" ",Mid([CHCHDS],2),[CHCHDS])))

You can nest IIF up to 7 levels deep - something I would avoid. Also, if
you have a lot more of this type thing to clean up then I would look at
writing a custom function. If you can define the rules.

For instance a rule might be "Remove all spaces and dashes at the beginning
of the field" . A simple VBA function could check each character in the
string and when it found any other character it could chop the string at
that point.

'======= Untested AIRCODE ==========
'Paste into a VBA module and call in the query
' Field: fTrimStart([CHCHDS])
Public Function fTrimStart(strIN) as string
Dim iLoop as Integer

For iLoop = 1 to Len(strIn & "")
If Mid(StrIn,iLoop,1) <> " " And Mid(strIn,iLoop,1) <> "-" then
exit for
end if
Next iLoop
fTrimStart = Mid(StrIN & "",iLoop)
End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michael Gramelspacher]

Guys & Gals

Apparently my previous message is either being ignore or not being
considered worthy of anyone challenge in this forum:

Below is the data in the field CHRDES

CHRDES = " - Comp. Fee"
CHRDES = " - Finance Fee"
CHRDES = " Auto Miles"
CHRDES = "Accounts Receivables"

With the above data in the fields CHRDES there are three things I need
to remove, the "blank,blank,hypen,blank", "blank,hyphen,blank' and "blank"

Here is the script I tought would work:

IIf(Left([CHCHDS],4)=" - ",Right([CHCHDS],26),[CHCHDS]) or
IIf(Left([CHCHDS],3)=" - ",Right([CHCHDS],27),[CHCHDS]) or
IIf(Left([CHCHDS],1)=" ",Right([CHCHDS],29),[CHCHDS])

But I am getting the result -1 and not the intended result.

End result would be the following: (removing the left most blanks and
hyphens)

"Comp. Fee"
"Finance Fee"
"Auto Miles"
....

Can anyone see where I am I am doing wrong...

Gil

UPDATE MyTable SET MyTable.my_field = Replace(Replace(Replace
(MyTable.my_field,"-","")," ",""),".","");

Removes spaces, hyphens and periods. Use your real table and
field names.

I did not read well enough. You want to preserve periods and
spaces between words. Try this:

UPDATE MyTable SET MyTable.my_field = Replace(LTRIM(RTRIM
(MyTable.my_field)),"- ","");

 

[Gilbert Noetzel]

John -

Thank you for your help. the VBA works! I don't know how it worked,
but I did what you told me to do..with some trial & error, but it works!

Great little program!

Gil