Find and Replace Numbers In Query 1

[dawnd3]

Hi All, I am having trouble with finding and replacing numbers in a query. I need to remove all numbers from a particular field. I can do it manually in the table by finding # and replacing with "" but when I do it in the query it throws up an error about an invalid date value. This is what I have in the update field for my field Replace([FullName],#,"")

Please advise...

Dawn

 

[PWise]

Try

Replace([FullName],"#","")

 

[dawnd3]

I tried that too, it didn't work. It ran the query without issue, but the numbers were still there.

 

[dawnd3]

Just for fun I added a # symbol to one of my records and the above removed it.

 

[PWise]

so you want to remove digits from your field not the pound sign

try

replace(......Replace(replace(replace(Replace(Replace([FullName],"0",""),1,""),2,""),3,""),4,"").......,9,')

you get the gist of it

 

[dawnd3]

That works! Thanks!

 

[MichaelRed]

numbers (fields) can't be text / strings. try replacing w/ null (BUT CHECK FIELD PROPERTIES, to make sure this is allowed.

MichaelRed

 

[PWise]

MichaelRed
if your field as text then 1234567890 are alphanumeric and can be replace by

 

[MichaelRed]

yes, ... but op never states the field type explicitly, however does refer to the error wih " ... date value ... ". I assume this means the field is date time-type which is numeric.

The original post is not clear on the actual operation being performed. It COULD be interperted to mean they are eliminating specific (individual) numerals (e.g. take all the "9"s out of the field?) which SEEMS to be a non-sensical exercise?) or it could (more believably?) delete the entire (value) in the field.

If that (the latter) were the case, it is still unclear. Does this refer to all the field where the value is a specific value (e.g. where field x = 1234)? or remove all values from the field?

Again, if the latter, why not just delete the filed and then re-add it?

Back to the original post, I interpert it to be to remove the specific value from a numeric (date-time) field, from the context, while agreeing that other interpertations are valid.


MichaelRed