 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005Replacing Query
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 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.  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html 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: [quoted text clipped - 17 lines] > 22 > 234 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. [quoted text clipped - 35 lines] > > 22 > > 234 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? SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html 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 [quoted text clipped - 41 lines] >> > 22 >> > 234 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. > [quoted text clipped - 47 lines] > >> > 22 > >> > 234 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 SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html 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 [quoted text clipped - 57 lines] >> >> > 22 >> >> > 234 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.