Delete Part of String

[thingssocomplex]

I have text in a field that is various in text but always includes TTTFG this can be in numerous places within the text field i.e. beginning, end of middle, example

123TTFG23
TTFG23456
etc

Is it possible to us an update or replace to remove the "TTFG" if yes how would I do this. For this purpose the table name is "MyTable" and the field is "MyField" MyTable.MyField

 

[ajetrumpet]

you dont need a query for this. use the find dialog box

highlight a field, the whole table, or whatever you want as your range. (you dont have to select anything if you dont want), pres ctrl+f and use the "look-in" dropdown for the selection or the whole table. there is another box too, for WHERE to look for the string: "whole field", "any part of field", etc, etc...

use the REPLACE tab on the dialog, not the FIND tab

 

[gemma-the-husky]

in general use instr to find the position of TTFG

then reassemble the string by taking

left part of the string, upto the T

and

right part of the string after the G

it will need some slight attention to handle the special case where the TTFG is at the start or end of the text.

 

[thingssocomplex]

Hi Adam - Thanks I am aware I can do that however, I don't want users going in to the data table to this gawd knows what they will change, I was hoping a query could do this something along the lines of an update query but not sure how to handle it.

 

[gemma-the-husky]

adam has the right idea, doesnt he

an update query to replace TTFG's with blanks

job done.

 

[ajetrumpet]

adam has the right idea, doesnt he

an update query to replace TTFG's with blanks

job done.

that will not work gemma. update queries run on whole fields, NO? the problem is different here. expanding on that prev post of yours will yield the goods I think...

Hi Adam - Thanks I am aware I can do that however, I don't want users going in to the data table to this gawd knows what they will change, I was hoping a query could do this something along the lines of an update query but not sure how to handle it.

my apologies. good thinking sir!

 

[gemma-the-husky]

Adam

you can do it as you suggested, cant you

update myfield to replace(myfield,"TTFG","")

 

[ajetrumpet]

Adam

you can do it as you suggested, cant you

update myfield to replace(myfield,"TTFG","")

yeah. i think i misunderstood plenty here. I hope he has enough info now to get it done!

 

[thingssocomplex]

Hi Guys,

Thanks for your replies I am still slightly confused how I should handle the query?

 

[vbaInet]

You may find that both your helpers have advised on different strategies to accomplish this.

Table wise or field wise you use this:

highlight a field, the whole table, or whatever you want as your range. (you dont have to select anything if you dont want), pres ctrl+f and use the "look-in" dropdown for the selection or the whole table. there is another box too, for WHERE to look for the string: "whole field", "any part of field", etc, etc...

use the REPLACE tab on the dialog, not the FIND tab

Query wise you implement this:

update myfield to replace(myfield,"TTFG","")

 

[thingssocomplex]

The query version does not seem to work syntax error on thr "replace" function

 

[vbaInet]

The query version does not seem to work syntax error on thr "replace" function

Have you read about the Replace function or checked in the Help files to get an idea of how it works and the syntax?

 

[thingssocomplex]

Hi Guys, I would like to say thanks for all your help I've had a play in SQL and got this to work using the below.

update MyTable SET MyTable.MyField = replace([MyTable]![MyField],"TTTFG","");

 

[vbaInet]

I'm glad you did your own research to understand the Replace() function and glad you got it working. It wasn't socomplex after all Great job!

 

[gemma-the-husky]

and note that you could achieve the same end with a MUCH-EASIER-TO-BUILD Visual Query!

to see it, just open a new query, go to SQL mode, paste your SQL in, then goto design mode.


you design a visual query, then either run it by clicking the red exclamation mark

or run it in code

docmd.openquery "myqueryname" OR
currentdb.execute "myqueryname"

 

[Brianwarnock]

There is a quirk with update queries, atleast in 2002 Sp3

If you design the query in the Design grid you must always enclose any field names in [] else they will be treated as strings, however in SQL they are recognised as field names, however if you later alter the query in the DG they become strings.

Does this happen in later releases?

Brian

 

[vbaInet]

Not experienced that problem Brian. But when you create the query in the DG (in 2007) it encloses them in square brackets automatically and recognises them as fields with or without the brackets.

What happens if you qualify the field name with its table name in sql view?

 

[Brianwarnock]

Not experienced that problem Brian. But when you create the query in the DG (in 2007) it encloses them in square brackets automatically and recognises them as fields with or without the brackets.

What happens if you qualify the field name with its table name in sql view?

So 2007 atleast recognises the field names.

As to your question, yep just tried it, if you qualify the fieldname with the tablename then in the DG the [] are inserted.

I always just enclose the fieldname in [] , when you are a 2 finger typist you want to do as little as possible.

Brian

 

[vbaInet]

So 2007 atleast recognises the field names.

It sure does. Someone must have complained about this to Microsoft. Could have been you. Hmm...

I always just enclose the fieldname in [] , when you are a 2 finger typist you want to do as little as possible.

Your two fingers are doing rather well, sometimes even faster than my 10

 

[Brianwarnock]

Your two fingers are doing rather well, sometimes even faster than my 10

10 fingers ! crikey ! that must help, what do you do with the thumbs.

Brian