filter word length and delete letters

[Guest]

Hi there I would like to be able to filter words with a certain length and
format and then delete the last 2 letter of that word using a query update.
Any ideas?

 

[Allen Browne]

Type something like this into the Field row in query design:
Len([Field1])
substituting your field name for Field1.
Type the number of characters in the Criteria under this field.

Turn it into an Update query (Update on Query menu.)
Access adds an Update row to the grid.
Under Field1, enter:
Left([Field1], Len(Field1]) - 2)

 

[Guest]

Thanks Alen,

The first part worked (filtering to word length) but on the update it told
me I had the wrong syntax - any other suggestions?

CHeers

Type something like this into the Field row in query design:
Len([Field1])
substituting your field name for Field1.
Type the number of characters in the Criteria under this field.

Turn it into an Update query (Update on Query menu.)
Access adds an Update row to the grid.
Under Field1, enter:
Left([Field1], Len(Field1]) - 2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi there I would like to be able to filter words with a certain length and
format and then delete the last 2 letter of that word using a query
update.
Any ideas?

 

[Allen Browne]

Are you sure you set criteria for fields that are more than 2 characters
long? Chopping off the last 2 characters from a field that is only 0 or 1
character long might cause problems.

You might want to check if you have a problem with library references:
http://allenbrowne.com/ser-38.html

If you are still stuck, what version of Access is this? And post the entire
SQL statement (by switching the query to SQL View, on the View menu in query
design.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Alen,

The first part worked (filtering to word length) but on the update it told
me I had the wrong syntax - any other suggestions?

CHeers

Type something like this into the Field row in query design:
Len([Field1])
substituting your field name for Field1.
Type the number of characters in the Criteria under this field.

Turn it into an Update query (Update on Query menu.)
Access adds an Update row to the grid.
Under Field1, enter:
Left([Field1], Len(Field1]) - 2)

Hi there I would like to be able to filter words with a certain length
and
format and then delete the last 2 letter of that word using a query
update.
Any ideas?

 

[Guest]

Thanks I just changed the coma for a semi-colon and it worked.

Do you know how I can now add 2 letters (XX) to the same field?

Cheers

Are you sure you set criteria for fields that are more than 2 characters
long? Chopping off the last 2 characters from a field that is only 0 or 1
character long might cause problems.

You might want to check if you have a problem with library references:
http://allenbrowne.com/ser-38.html

If you are still stuck, what version of Access is this? And post the entire
SQL statement (by switching the query to SQL View, on the View menu in query
design.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Alen,

The first part worked (filtering to word length) but on the update it told
me I had the wrong syntax - any other suggestions?

CHeers

Type something like this into the Field row in query design:
Len([Field1])
substituting your field name for Field1.
Type the number of characters in the Criteria under this field.

Turn it into an Update query (Update on Query menu.)
Access adds an Update row to the grid.
Under Field1, enter:
Left([Field1], Len(Field1]) - 2)

Hi there I would like to be able to filter words with a certain length
and
format and then delete the last 2 letter of that word using a query
update.
Any ideas?

 

[Allen Browne]

Use an Update query.

To add the text "XX" to Field1, type this in the update row:
[Field1] & "XX"