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

Replace 2 characters in an access query

I have a formula to replace "-" but i also have spaces to remove the formula i am using is
Expr7: IIf(Not IsNull([Address Book]![Certificate]),Replace([Address Book]![Certificate],"-",""),Null)
How would i repace " "(blank space) as well?
  • 2
1 Solution
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:

try this
Expr7: IIf(Not IsNull([Address Book]![Certificate]),Replace(Replace([Address Book]![Certificate],"-","") ," ",""),Null)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can do multiple replacements by cascading as show above

Replace(Replace(Replace(Replace ([SomeField], "-",""), " ",""),"?",""),"/")

and so on ...

Of course make a backup before testing any action queries...

Are you looking to remove ALL spaces in the certificate field?  MX's suggestion will do that.  Or are you looking to get rid of only spaces around the dash you are removing?  

You can specify character combinations to replace.  For example " - " can be used for a "space dash space" combination.

Expr7: IIf(Not IsNull([Address Book]![Certificate]),Replace([Address Book]![Certificate]," - ",""),Null)

Open in new window

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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now