Solved

Replacing characters if present referencing vba function during a query

Posted on 2008-10-01
13
241 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
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 65

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 65

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 - Access MVP) 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

770 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