Convert custom field with replace() to string

[Ruegen]

I have made a field in the query with a replace() function

so



Expr: replace([field],"chr(10)"," Found One ")

for some reason Expr: doesn't return as a string?!

so I can't do any criteria searches

If I try converting to str() I also get an error.

help

[Ruegen]

I've used this to get around my issue but I still don't know the reason.

[John_G]

You have an error in this expression:

Expr: replace([field],"chr(10)"," Found One ")

Chr(10) should not be in quotation marks. Putting it in quotes makes it just a string which happens to be "chr(10)", so the replace looks for that string, not a Carriage-return (or is it line-feed?) character.

HTH

John

[Ruegen]

You have an error in this expression:

Expr: replace([field],"chr(10)"," Found One ")

Chr(10) should not be in quotation marks. Putting it in quotes makes it just a string which happens to be "chr(10)", so the replace looks for that string, not a Carriage-return (or is it line-feed?) character.

HTH

John

Yeah I checked that - same error

(I made that error when typing the post only)

[orange]

Can you tell us exactly what you are trying to do? Perhaps a sample of the input and the intended output.

Here's the details on the Replace function

[John_G]

Hi -

I am having no difficulty with the replace function - it does exactly what it should with chr(10).

Are you getting an error message, and if so what does it say?

In your example, if [SchoolPostalAddress] is Null, you will get an error "Invalid Use of Null"

Using Nz([SchoolPostalAddress],"") will prevent that error.

John

[orange]

@John_G

I agree, I understand Replace but what is with the criteria in the query grid?

[John_G]

@orange -

Sorry - I was responding to the OP.

@Ruegen -

Orange raises a good point - what does (or should) the criteria do? From what I can see, it will give you only those records with more than one line in the address.

John

[Ruegen]

@orange -

Sorry - I was responding to the OP.

@Ruegen -

Orange raises a good point - what does (or should) the criteria do? From what I can see, it will give you only those records with more than one line in the address.

John

It's an old database where users have put new lines when entering data that I have to remove (they are hidden from view on forms otherwise on single line text boxes).

I'll try nz() as it might be that

[John_G]

Hi -

If all you want to do is replace all the Chr(10) "characters" in that field, for all records, you can do it with one SQL statement.

currentdb.execute "Update tablename set [SchoolPostalAddress] = Replace([SchoolPostalAddress],chr(10),' ') where [schoolpostaladdress] is not null", dbfailonerror

The "where" part limits it to records where [SchoolPostalAddress] has data in it.

Change tablename to the name of your table. You could paste the bold text into the SQL view of query design.

John

[Ruegen]

Sounds good - I could simply put not is null as a criteria on [schooladdress] and that way the error won't happen I guess - will have to try when I get back to work

I need to remember these things when I get errors

[Ruegen]

Hiya everyone

Yes it was indeed the null causing the error

Thanks for your help!