asked on

Replacing characters if present referencing vba function during a query

Hello:
I need to establish a replace characters function that can be referenced during a query. I am not updating a table, but rather just displaying the desired replacement character in the query results. I will basically display the field in the query like this--

FIELD_NAME:ReplaceChar(Trim([TABLE].[FIELD]))

Function ReplaceChar()
'Replaces the "|" with "-" If the "|" character is present
End Function

Help is appreciated!!!

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KrissQ8

ASKER

That one just gives the big
#Error
anywhere the condition not met...

But actually I figured out what does work for me on this front...
Here it is!!
Trim(FIELD: IIf(InStr([TABLE].[FIELD],"|")>0,Left([TABLE].[FIELD],InStr([TABLE].[FIELD],"|")-1) & "-" & Mid([TABLE].[FIELD],InStr([TABLE].[FIELD],"|")+1),[TABLE].[FIELD]))

KrissQ8

ASKER

FIELD: Trim(IIf(InStr([TABLE].[FIELD],"|")>0,Left([TABLE].[FIELD],InStr([TABLE].[FIELD],"|")-1) & "-" & Mid([TABLE].[FIELD],InStr([TABLE].[FIELD],"|")+1),[TABLE].[FIELD]))

Jim Horn

I guarantee my proposed solution works.
Exactly where do you have this code?

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Expanding on jHo's expression ... try:

FieldName: replace([FIELD], "|", "-")

Also, FIELD is a Reserved Word in Access. I would strongly suggest changing that name to something else - like StrawberryFIELD or whatever.

mx

SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KrissQ8

ASKER

I am using an Access query.
With the standard
FieldName:Replace(FIELD, "|", "-")
works perfect for updating a table, but I that's not what I'm doing. I am running a query that interprets the values and displays them a bit different than they actually are.
When using the FieldName:Replace(FIELD, "|", "-") in a Select query, it will show #Error for any Null values and there will always be some - I can't have that #Error in my qry results.
I like that FieldName: replace(FIELD, "|", "-") but it does not achieve the required result as my solution does.
The FieldName:Replace(FIELD, "|", "-") works if I updated all Null to "" first in the table..

FIELD: Trim(IIf(InStr([TABLE].[FIELD],"|")>0,Left([TABLE].[FIELD],InStr([TABLE].[FIELD],"|")-1) & "-" & Mid([TABLE].[FIELD],InStr([TABLE].[FIELD],"|")+1),[TABLE].[FIELD]))

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KrissQ8

ASKER

mx:
You have a really good concept.
Here is what I am going to use:
FieldName: Trim(IIf([TableName].[FieldName] Is Not Null,Replace([TableName].[FieldName],"|","-"),Null))
Works as good as my orig solution w/ less mud. I need the tablename in there because w/o it is a circular ref.

GaryL- your solution also displays the #Error for Null values as well.

Thank you everyone for so much help!!!!

KrissQ8

ASKER

Arigato Gozai Mas!!!!

GRayL

Arigato Gozai Mas??

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

It means "May I offer you some popcorn?"

mx

GRayL

What are you smoking;-)