remove some data (1 Viewer)

[kitty77]

I changed a field from a hyperlink to a text field. Some of the data now looks like this...

www.abc.com#blahhhh

How can I get rid of (in a query) everything to the right of #? Including the # too.

Thanks!

 

[Cronk]

Use an update query

update yourTableName set yourFieldName = left(yourFieldName, instr(yourFieldName, "#") -1)

 

[gemma-the-husky]

Something like this. someone probably has a much slicker pattern matching approach.
You may need to allow for not finding the # character in the search string.

if instr(yourtext,"#")>0 then
   yourtext = left(instr(yourtext,"#")-1)
end if

you could even split your string on the "#" character and take the first elelment

not sure of the precise format, but this sort of thing.

split(yourtext,"#")(0)

 

[Isaac]

or Replace with Mid

 

[kitty77]

Use an update query

update yourTableName set yourFieldName = left(yourFieldName, instr(yourFieldName, "#") -1)

Some of my data does not have the "#", so how can I make the value = the field value if no "#"?

 

[Isaac]

Some of my data does not have the "#", so how can I make the value = the field value if no "#"?

You can add an IIF statement

 

[Cronk]

Or add a where statement to the SQL

Try

update yourTableName set yourFieldName = left(yourFieldName, instr(yourFieldName, "#") -1) where instr(yourFieldName, "#") >0

 

[arnelgp]

or simply:
update yourTableName set yourFieldName = left(yourFieldName, instr(yourFieldName, "#") -1) where yourFieldName Like "*[#]*";