Removing left most Blanks & Hypens in Data

[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

 

[Jeff Boyce]

The folks who help out in these newsgroups are largely volunteers. You may
have hundreds looking at your question, or ...?!

Take another look in Access HELP at how you'd embed IIF() statements ... I
suspect your use of " or " between them is the cause of your "-1" result
(incidentally, in Access, this implies a "True"). Unless you really want to
test for one condition OR another OR a third, you use commas, not "or"s.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Albert D. Kallal]

Is this needed in an expression, or are we talking about code?

I would do the following:

eg:

CHRDES = " - Comp. Fee"

CHRDES = trim(CHRDES)

The trim command removes any repeating blanks, beginning, or trailing.

expression is now: "- Comp. Fee"

It is a simle matter to check if the first char is a "-"

if left(CHRDES,1) = "-" THEN
CHRDES = TRIM(MID(CHRDES,2))
end if

The above will thus produce your results....


You can create a public function that allows you to use this function in
query, or reports...or even on a text box on a form.

The function we need is this

Public Funciton MyTrim(vData as Varient) as varient

if isnull(vData) then exit function

MyTrim = trim(vData)
if left(MyTrim,1) = "-" then
MyTrim = trim(mid(MyTrim,2))
end if

end Function


Now, in your report, or query...you can use the above. Eg: in the query
builder you could type in:


MyTrimedResults:MyTrim([NameOfField])