Calculated Field- remove spaces between concatenated string (1 Viewer)

[machumpion]

Hi,

I'm trying to create a new field based on two existing fields [f1], [f2]. [f2] are postal codes that sometimes has extra spaces between characters. How can I concatenate [f1] and [f2] where [f2] has no spaces?

Thank you!

 

[sneuberg]

Maybe

[f1] & Replace([f2], " ", "")

 

[machumpion]

Maybe

[f1] & Replace([f2], " ", "")

for some reason Access removes the Replace function when I try to return to datasheet view from Design View

 

[Eljefegeneo]

Try this:

 =([f1]) & (" "+[f2]) & (" "+[f3])

 

[sneuberg]

Ok try

=[f1] & Replace(Nz([f2])," ","")

demonstrated in the attached database

 

[sneuberg]

I think there's some confusion about what you want. Could you show us some examples of f1 and f2 and the desired result

 

[machumpion]

The calculated field i am trying to add is part of a table and not a form, could that be why the proposed solutions don't work?

When I try to revert back to datasheet view after entering the expression, the
=[f1] & Replace(Nz([f2])," ","")
reverts back to [f1]&[f2]

 

[sneuberg]

I don't think you are going to be able to do that in a calculated field. If you invoke the expression builder Replace is not in the list of functions. I also don't see any way to replicate what Replace does with the function that are there. I think you might be able to use InStr in combination with Mid to get rid of one space, but that's it; not multiple spaces.

The Replace function does work in a query expression like:

SELECT Table1.ID, Table1.f1, Table1.f2, [f1] & Replace(Nz([f2])," ","") AS f3
FROM Table1;

So you may be stuck with doing it that way.

 

[The_Doc_Man]

First, you can UPDATE a field in a table, but that kind of computation should never (OK, well... hardly ever) appear in a table.

If your problem is having spaces at the beginning or end of a field, there is always the TRIM$() function (which you can look up). If you can have a code string that contains more than one consecutive blank in the middle of the string, that is trickier.

Show us what you are using and what you want for the most pathological cases.

 

[machumpion]

I don't think you are going to be able to do that in a calculated field. If you invoke the expression builder Replace is not in the list of functions. I also don't see any way to replicate what Replace does with the function that are there. I think you might be able to use InStr in combination with Mid to get rid of one space, but that's it; not multiple spaces.

The Replace function does work in a query expression like:

SELECT Table1.ID, Table1.f1, Table1.f2, [f1] & Replace(Nz([f2])," ","") AS f3
FROM Table1;

So you may be stuck with doing it that way.

what would the Instr + Mid function look like to get rid of a space in [f2]? if some records in [f2] didn't have spaces, would it still return the record?

 

[sneuberg]

I was thinking of something like:

[f1] & Mid([f2],1,InStr(1,[f2]," ")-1) & Mid([f2],InStr(1,[f2]," ")+1,Len([f2])-InStr(1,[f2]," "))

but it produces an error when there's no space.