 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / April 2008change data in access
I have a database with a table of 1,123 records that I need to change in a hurry! The problem is I do not know how to do it.... For example there are 2 fields that are numerical and formatted buy currency 1 record reads 400 and the other reads 400, but I need to double the amount for both fields and all of the records. Please help..... Thanx in advance for any and all help.. YOu can do this easily with an Update query. Open a new query in the query builder and select the table. Put the two fields into the query grid. Change the query type to Update. Now you will have a row that says Update To. For example purposes, we will use the names FieldOne and FieldTwo, but use your real names for the fields. in the Update To row, for each field enter IIf(IsNull([FieldOne], Null, [FieldOne] * 2) and for the other IIf(IsNull([FieldOne], Null, [FieldOne] * 2) The reason for the IIf statement is that if you try to multiple a field that is Null, it will error out. Instead, this will only multiply non null fields and leave the Null fields alone.  SignatureDave Hargis, Microsoft Access MVP > I have a database with a table of 1,123 records that I need to change in a > hurry! The problem is I do not know how to do it.... For example there are 2 > fields that are numerical and formatted buy currency 1 record reads 400 and > the other reads 400, but I need to double the amount for both fields and all > of the records. Please help..... Thanx in advance for any and all help.. I get the error "The expression you entered has a function containing the wrong number of arguments." > YOu can do this easily with an Update query. > Open a new query in the query builder and select the table. [quoted text clipped - 16 lines] > > the other reads 400, but I need to double the amount for both fields and all > > of the records. Please help..... Thanx in advance for any and all help.. That indicates you put the wrong number of arguments in a function <g> But seriously, folks. Post the SQL of the query so I can have a look. If you don't know how to do that, open the query in design mode, the change to SQL view, then copy/paste. SignatureDave Hargis, Microsoft Access MVP > I get the error "The expression you entered has a function containing the > wrong number of arguments." [quoted text clipped - 19 lines] > > > the other reads 400, but I need to double the amount for both fields and all > > > of the records. Please help..... Thanx in advance for any and all help.. "Update Products SET [Price]"=[Price*2] this worked but as you said it nulled everything to 0. > That indicates you put the wrong number of arguments in a function <g> > [quoted text clipped - 25 lines] > > > > the other reads 400, but I need to double the amount for both fields and all > > > > of the records. Please help..... Thanx in advance for any and all help.. Okay, if you want the 0's to go back to Null, do an update on each field. That is, update one field, then the other. You would put Null in the Update To row and 0 in the Criteria row. SignatureDave Hargis, Microsoft Access MVP > "Update Products SET [Price]"=[Price*2] this worked but as you said it nulled > everything to 0. [quoted text clipped - 28 lines] > > > > > the other reads 400, but I need to double the amount for both fields and all > > > > > of the records. Please help..... Thanx in advance for any and all help.. Thanx! It worked! > Okay, if you want the 0's to go back to Null, do an update on each field. > That is, update one field, then the other. You would put Null in the Update [quoted text clipped - 32 lines] > > > > > > the other reads 400, but I need to double the amount for both fields and all > > > > > > of the records. Please help..... Thanx in advance for any and all help.. |
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.