Replacing Query

[Guest]

I have a table with one field (text) "EXP1"
The field contains this type of data:
22 minutes
117 minutes
98 minutes
22 minutes
234 minutes
etc....

My problem: How can I replace all these record with only the numbers and get
rid of the "minutes" text. And then convert from text to numbers? Remember
there is always a space in front of the text numbers before "minutes" I need
a query to do this all the time. Example:
22
117
98
22
234

 

[Allen Browne]

1. Create a new field in your table, named (say) Minutes, of type Number
(size Long Integer). Save. Close the table.

2. Create a query into this table.
Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

3. Drag the new Minutes field into the grid.
In the Criteria row under this field enter:
Is Not Null
In the Update row under this field enter:
Val([EXP1])

4. Run the query.

Once you have verified that the correct data is in the new column, you can
delete the old EXP1 column from the table.

 

[Guest]

This didn't work at all. I'm trying to delete the word minutes from the text
field, but only wanting to save the numbers in front of the word minutes.

1. Create a new field in your table, named (say) Minutes, of type Number
(size Long Integer). Save. Close the table.

2. Create a query into this table.
Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

3. Drag the new Minutes field into the grid.
In the Criteria row under this field enter:
Is Not Null
In the Update row under this field enter:
Val([EXP1])

4. Run the query.

Once you have verified that the correct data is in the new column, you can
delete the old EXP1 column from the table.

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

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

I have a table with one field (text) "EXP1"
The field contains this type of data:
22 minutes
117 minutes
98 minutes
22 minutes
234 minutes
etc....

My problem: How can I replace all these record with only the numbers and
get
rid of the "minutes" text. And then convert from text to numbers?
Remember
there is always a space in front of the text numbers before "minutes" I
need
a query to do this all the time. Example:
22
117
98
22
234

 

[Allen Browne]

Okay, lets take it one step at at time.

Are you able to create a query, and type this into the Field row:
Val([EXP1])
Does this give you the number of minutes from the start of the field?

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

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

This didn't work at all. I'm trying to delete the word minutes from the
text
field, but only wanting to save the numbers in front of the word minutes.

1. Create a new field in your table, named (say) Minutes, of type Number
(size Long Integer). Save. Close the table.

2. Create a query into this table.
Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

3. Drag the new Minutes field into the grid.
In the Criteria row under this field enter:
Is Not Null
In the Update row under this field enter:
Val([EXP1])

4. Run the query.

Once you have verified that the correct data is in the new column, you
can
delete the old EXP1 column from the table.

I have a table with one field (text) "EXP1"
The field contains this type of data:
22 minutes
117 minutes
98 minutes
22 minutes
234 minutes
etc....

My problem: How can I replace all these record with only the numbers
and
get
rid of the "minutes" text. And then convert from text to numbers?
Remember
there is always a space in front of the text numbers before "minutes" I
need
a query to do this all the time. Example:
22
117
98
22
234

 

[Guest]

I ran this query just like you said, I get 0 (zero) records updated. I fail
to see how this will take the word "minutes" away from the numbers. The field
is a text field that contains "127 minutes" and so on. I thought I could do
some kind of replace. Thanks

Okay, lets take it one step at at time.

Are you able to create a query, and type this into the Field row:
Val([EXP1])
Does this give you the number of minutes from the start of the field?

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

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

This didn't work at all. I'm trying to delete the word minutes from the
text
field, but only wanting to save the numbers in front of the word minutes.

1. Create a new field in your table, named (say) Minutes, of type Number
(size Long Integer). Save. Close the table.

2. Create a query into this table.
Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

3. Drag the new Minutes field into the grid.
In the Criteria row under this field enter:
Is Not Null
In the Update row under this field enter:
Val([EXP1])

4. Run the query.

Once you have verified that the correct data is in the new column, you
can
delete the old EXP1 column from the table.


I have a table with one field (text) "EXP1"
The field contains this type of data:
22 minutes
117 minutes
98 minutes
22 minutes
234 minutes
etc....

My problem: How can I replace all these record with only the numbers
and
get
rid of the "minutes" text. And then convert from text to numbers?
Remember
there is always a space in front of the text numbers before "minutes" I
need
a query to do this all the time. Example:
22
117
98
22
234

 

[Allen Browne]

Apparently I am not going to be able to convince you that:
a) Val() returns just the leading number part of a string, and
b) an Update query can replace values.

Bye

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

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

I ran this query just like you said, I get 0 (zero) records updated. I fail
to see how this will take the word "minutes" away from the numbers. The
field
is a text field that contains "127 minutes" and so on. I thought I could
do
some kind of replace. Thanks

Okay, lets take it one step at at time.

Are you able to create a query, and type this into the Field row:
Val([EXP1])
Does this give you the number of minutes from the start of the field?

This didn't work at all. I'm trying to delete the word minutes from the
text
field, but only wanting to save the numbers in front of the word
minutes.

:

1. Create a new field in your table, named (say) Minutes, of type
Number
(size Long Integer). Save. Close the table.

2. Create a query into this table.
Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

3. Drag the new Minutes field into the grid.
In the Criteria row under this field enter:
Is Not Null
In the Update row under this field enter:
Val([EXP1])

4. Run the query.

Once you have verified that the correct data is in the new column, you
can
delete the old EXP1 column from the table.


I have a table with one field (text) "EXP1"
The field contains this type of data:
22 minutes
117 minutes
98 minutes
22 minutes
234 minutes
etc....

My problem: How can I replace all these record with only the numbers
and
get
rid of the "minutes" text. And then convert from text to numbers?
Remember
there is always a space in front of the text numbers before
"minutes" I
need
a query to do this all the time. Example:
22
117
98
22
234