Share via

Multiple Replace Function

Anonymous
2010-10-19T15:18:56+00:00

i have a text field that consists of spaces and leading zeros.

i want to combine the following functions into one Replace function.

Replace([Field2];" ";"")

Replace(LTrim(Replace([part number];"0";" "));" ";"0")

can i achieve that? it is important that i use only one function because i use this query in a union query.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2010-10-19T19:04:43+00:00

Try this --

Replace(LTrim(Replace(Replace([part number], " ", ""),"0"," "))," ","0")

Build a little, test a little.

Was this answer helpful?

2 people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-10-19T15:31:15+00:00

    Can you describe what you are trying to do?  

    Replace(LTrim(Replace([part number];"0";" "));" ";"0") as posted would remove leading zeroes from Part Number and then replace any spaces with zeroes.  

    Your first expression simply removes all spaces from [Field2].

    Do you want to combine the two into one string?  Or are Field2 and [Part number] related somehow?

    John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2010-10-19T18:59:34+00:00

    [part number] is [Field2], i apologize for the confusion.

    yes, i want to combine the two function in one string.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-10-19T15:30:03+00:00

    I do not see a problem.  Just try it!

    Build a little, test a little.

    Was this answer helpful?

    0 comments