Update Query to delete partial cell data 1

[pspring1]

i have a column in a table that reads quarter and is supposed to have data in the following format
04 Q4
05 Q1
03 Q2

However, some of these have <> signs on them by mistake and i want to get them off. How do I make an update query to find the signs and just delete them. They are only before or after the data I want as shown below.

<04 Q4
05 Q3>

 

[dhookom]

If this is a one time process, just open the table in datasheet view, click in the column/field, press Ctrl+H, set the appropriate values, and replace all. You might want to backup your mdb prior to doing any mass update.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[HBWAL]

why can't you just ctrl-H (find and replace) in the table. If this keeps happening can you put an input mask on the field. If you import the data you could create a recordset that has that character (instr(field, "<") and loop through the recordset by replacing it. If it always is on the far left or far right then use a update query to ltrim, rtrim, and mid to get rid of it.

 

[PHV]

A starting point:
UPDATE yourTable SET yourField=Mid(yourField,2)
WHERE yourField Like '<*';

UPDATE yourTable SET yourField=Left(yourField,InSStr(yourField,'>')-1)
WHERE yourField Like '*>*';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pspring1]

Thanks PH I need to do it in an automated matter with constant new data so this is what I am looking for however it does not work.

My column name is quarter. anf my table name is Table1

I wrote the followin in an update query

Field: Quarter
Table: Table1
Update to: "quarter"=Left("QUARTER",InSStr("QUARTER",'>')-1)
Criteria: *>*

is this not right?

 

[PHV]

Oops, sorry for the typo: InStr instead of InSStr ...
I need to do it in an automated matter
In VBA take a look at the DoCmd.RunSQL method:
DoCmd.RunSQL "UPDATE Table1 SET Quarter=Left(Quarter,InStr(Quarter,'>')-1) WHERE Quarter Like '*>*'"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pspring1]

Thanks for the reply but I just deleted the S and tried it again andit says "Invalid procedure call" I'd like to do it like this and avoid using VBA bc I am not familiar with that unless you could explain it.

So I just tried this:

Field: Quarter
Table: Table1
Update to: "quarter"=Left("QUARTER",InStr("QUARTER",'>')-1)
Criteria: *>*

and it gives me a message that says:
"Invalid procedure call"

 

[PHV]

And this ?
=Left([QUARTER],InStr([QUARTER],'>')-1)

Criteria: Like '*>*' And Not Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pspring1]

thanks so much for your help0. that one works but the update query for the other one just deletes the cells i was going by this:

UPDATE TO: =Mid(yourField,2)
CRITERIA: '<*'

do i need to change this one?

 

[PHV]

=Mid([QUARTER],2)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244