Update Queries - Help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,. I'm a student trying to turn in my final exam.

Can't figure out how to use an Update Query in design to remove extra
letters (fff or ggg) from a column(or field).

any suggestions ?
 
So far I have........ zero rows are updated. There are at least 3 in the
column of the table.

Table: Good Info
Update To: Field 2
Criteria: Trim ("fff") or Trim ("ggg")
 
If the letters are at the end of the string you can use the following in your
criteria:

Like “*fff†or Like “*gggâ€

The update to should read:

Left([FieldName],LEN([FieldName])-3)
 
Hi Craig,

The letters are actually located at the beginning and middle of the string
(i.e. fffnevada, Nevgggda). Zero rows were updated using Like “*fff†or
Like “*gggâ€.



Craig said:
If the letters are at the end of the string you can use the following in your
criteria:

Like “*fff†or Like “*gggâ€

The update to should read:

Left([FieldName],LEN([FieldName])-3)


Jchbd said:
So far I have........ zero rows are updated. There are at least 3 in the
column of the table.

Table: Good Info
Update To: Field 2
Criteria: Trim ("fff") or Trim ("ggg")
Click to expand...
Click to expand...
 
Hi Craig,

The letters are actually located at the beginning and middle of the string
(i.e. fffnevada, Nevgggda). Zero rows were updated using Like “*fff” or
Like “*ggg”.
Click to expand...

Well, that's exactly as I would expect. LIKE "*fff" means "match any
string followed by the three letters fff" - so this would find records
where the field ENDED in fff.

If you want to update "fffNevada", use a criterion of LIKE "fff*"

The asterisk wildcard means "any string of zero or more characters" so
LIKE "fff*" means "fff followed by anything at all, or by nothing".

John W. Vinson[MVP]
 
Thanks. The below works however, it does not remove instead replaces my
existing text with what is in the Update To: row in design view.

For example: In my table, there is a column called Field2 which has a row
with fffnevada as text.

Any ideas on how to remove vs. replace the entire text? My existing code is:

Field: Field2
Table: Good Data
Update To: Field2
Criteria: Like (fff*) or Like ("ggg*)

- - JCHBD
 
Thanks. The below works however, it does not remove instead replaces my
existing text with what is in the Update To: row in design view.

For example: In my table, there is a column called Field2 which has a row
with fffnevada as text.

Any ideas on how to remove vs. replace the entire text? My existing code is:

Field: Field2
Table: Good Data
Update To: Field2
Criteria: Like (fff*) or Like ("ggg*)
Click to expand...

To replace

fffNevada

by

Nevada

or, in general, to trim off the first three letters of any field value
starting with fff or ggg, use a criterion

LIKE "fff*" OR LIKE "ggg*"

and an Update To value of

Mid([Field2], 4)

The Mid() function returns a substring, in this case the substring
starting at the 4th character (after the fff) and going to the end of
the field.

The SQL view of the query would be

UPDATE [Good Data]
SET [Field2] = Mid([Field2], 4)
WHERE [Field2] LIKE "fff*" OR [Field2] LIKE "ggg*"

John W. Vinson[MVP]
 
Thanks John,

Found out this works ONLY IF I know exactly where the extra characters are
located.

Because my assignment was specifically to correct NEVADA I was able to
execute the following:

Field: Field2
Table: Good Data
Update To: "Nevada"
Criteria: Like ("*fff*) Or Like ("*ggg*)

However, this poses the thought of 'What IF I did not know exactly where fff
or ggg were in the text ?"

Any who.. I received a B+ for my access class. Appreciate all your help :)
--
jchbd


John Vinson said:
Thanks. The below works however, it does not remove instead replaces my
existing text with what is in the Update To: row in design view.

For example: In my table, there is a column called Field2 which has a row
with fffnevada as text.

Any ideas on how to remove vs. replace the entire text? My existing code is:

Field: Field2
Table: Good Data
Update To: Field2
Criteria: Like (fff*) or Like ("ggg*)
Click to expand...

To replace

fffNevada

by

Nevada

or, in general, to trim off the first three letters of any field value
starting with fff or ggg, use a criterion

LIKE "fff*" OR LIKE "ggg*"

and an Update To value of

Mid([Field2], 4)

The Mid() function returns a substring, in this case the substring
starting at the 4th character (after the fff) and going to the end of
the field.

The SQL view of the query would be

UPDATE [Good Data]
SET [Field2] = Mid([Field2], 4)
WHERE [Field2] LIKE "fff*" OR [Field2] LIKE "ggg*"

John W. Vinson[MVP]
Click to expand...
 
Back
Top