• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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!!!
0
KrissQ8
Asked:
KrissQ8
  • 5
  • 3
  • 3
  • +1
3 Solutions
 
Jim HornMicrosoft 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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 MVP, Access and Data Platform)Commented:
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
 
GRayLCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
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 MVP, Access and Data Platform)Commented:
It means "May I offer you some popcorn?"

mx
0
 
GRayLCommented:
What are you smoking;-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now