Solved

Replacing characters if present referencing vba function during a query

Posted on 2008-10-01
13
240 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now