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!!!
KrissQ8Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
"Some versions of access "
That would be A97 and prior.


"it will show #Error for any Null values"
Try this:

FieldName:  IIF(Not IsNull([FIELD],  Replace(FIELD, "|", "-") , Null)

mx
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
It's already a function, you don't need to create your own.  

FieldName:  replace(FIELD, "|", "-")
0
 
KrissQ8Author Commented:
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]))
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
KrissQ8Author Commented:
FIELD: Trim(IIf(InStr([TABLE].[FIELD],"|")>0,Left([TABLE].[FIELD],InStr([TABLE].[FIELD],"|")-1) & "-" & Mid([TABLE].[FIELD],InStr([TABLE].[FIELD],"|")+1),[TABLE].[FIELD]))
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I guarantee my proposed solution works.
Exactly where do you have this code?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
GRayLConnect With a Mentor Commented:
Some versions of access do not recognize the VBA function Replace() in a query.  From the question change:

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

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

to:

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

  Function ReplaceChar(myString AS String) AS String
     ReplaceChar = Replace(myString,"!","-")
  End Function
0
 
KrissQ8Author Commented:
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]))
0
 
KrissQ8Author Commented:
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!!!!
0
 
KrissQ8Author Commented:
Arigato Gozai Mas!!!!
0
 
GRayLCommented:
Arigato Gozai Mas??
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
It means "May I offer you some popcorn?"

mx
0
 
GRayLCommented:
What are you smoking;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.