Solved

Replacing characters if present referencing vba function during a query

Posted on 2008-10-01
13
245 Views
Last Modified: 2013-11-29
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
Comment
Question by:KrissQ8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 83 total points
ID: 22618286
It's already a function, you don't need to create your own.  

FieldName:  replace(FIELD, "|", "-")
0
 

Author Comment

by:KrissQ8
ID: 22618676
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
 

Author Comment

by:KrissQ8
ID: 22618712
FIELD: Trim(IIf(InStr([TABLE].[FIELD],"|")>0,Left([TABLE].[FIELD],InStr([TABLE].[FIELD],"|")-1) & "-" & Mid([TABLE].[FIELD],InStr([TABLE].[FIELD],"|")+1),[TABLE].[FIELD]))
0
Industry Leaders: 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!

 
LVL 66

Expert Comment

by:Jim Horn
ID: 22618724
I guarantee my proposed solution works.
Exactly where do you have this code?
0
 
LVL 75
ID: 22619060
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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 83 total points
ID: 22619086
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
 

Author Comment

by:KrissQ8
ID: 22619105
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 84 total points
ID: 22619171
"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
 

Author Comment

by:KrissQ8
ID: 22619405
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
 

Author Closing Comment

by:KrissQ8
ID: 31502130
Arigato Gozai Mas!!!!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22619751
Arigato Gozai Mas??
0
 
LVL 75
ID: 22619784
It means "May I offer you some popcorn?"

mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22620688
What are you smoking;-)
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question