Find numbers > 0 and replace with M - How

[Guest]

I have a table with some data in it already, in one column I need to locate all dollar amounts of more than $0.00 and place an M there instead, and replace everything that is $0.00 with nothing - is there any way to do this.

Also I have another column with numbers like 24B, 24H, 24F, 24C, and so on, what I need to do is get rid of the first two numbers and leave just the letter in the column - is there any way to do this without going through each cell and erasing the number.

I'm using access 2000

Thanks

 

[CJ]

I'm afraid I can only help you with the first 1/2 of your
question.

STEP 1) Make sure your field will accept text. To do
this, open the table in design view. Highlight the field
you're concerned about and change the data type to text.
Save this change.

2) In datasheet view, highlight the field then run
a "search & replace." You do this by clicking on the
little binnoculars on the Access toolbar.

a) In Find What, enter $0.00 In Replace With,
enter "" This will create an empty field where all of
your $0.00 entries used to be.

b) I am assuming you want an M in ALL of the other
fields, right? Run a second "search & replace," Finding
* and replacing with M

Do the steps in this order so that you don't end up
replacing all of the original $0.00 fields with an M.

CJ

-----Original Message-----
I have a table with some data in it already, in one

column I need to locate all dollar amounts of more than
$0.00 and place an M there instead, and replace
everything that is $0.00 with nothing - is there any way
to do this.

Also I have another column with numbers like 24B, 24H,

24F, 24C, and so on, what I need to do is get rid of the
first two numbers and leave just the letter in the
column - is there any way to do this without going
through each cell and erasing the number.

 

[Craig Hornish]

For the second half you can use an Update query. In
the 'Update to:" area under the field value you want to
change, put Right([Value],1) (Value - is the name of the
field).
Note - this only works if letter you want is only 1
character in length.

This technique can also be used for the first part of
your question. By using the Criteria to find the records
you wish to change.

Craig

-----Original Message-----
I have a table with some data in it already, in one

column I need to locate all dollar amounts of more than
$0.00 and place an M there instead, and replace everything
that is $0.00 with nothing - is there any way to do this.

Also I have another column with numbers like 24B, 24H,

24F, 24C, and so on, what I need to do is get rid of the
first two numbers and leave just the letter in the column -
is there any way to do this without going through each
cell and erasing the number.

 

[John Vinson]

column I need to locate all dollar amounts of more than
$0.00 and place an M there instead, and replace everything
that is $0.00 with nothing - is there any way to do this.

M is not a numeric character. If this is a Number datatype field, you
CANNOT store a M in it.

What you can do is *display* a text string:

ShowNum: IIF([field] > 0, "M", [field])

This will of course not be editable.

24F, 24C, and so on, what I need to do is get rid of the
first two numbers and leave just the letter in the column -
is there any way to do this without going through each
cell and erasing the number.

Hrm. Try an Update query updating to

Mid([fieldname], Len(Val([fieldname])) + 1)