change data in access

  • Thread starter Thread starter Achille's
  • Start date Start date
A

Achille's

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.
 
I get the error "The expression you entered has a function containing the
wrong number of arguments."

Klatuu said:
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.
--
Dave Hargis, Microsoft Access MVP


Achille's said:
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..
Click to expand...
Click to expand...
 
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.
--
Dave Hargis, Microsoft Access MVP


Achille''''s said:
I get the error "The expression you entered has a function containing the
wrong number of arguments."

Klatuu said:
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.
--
Dave Hargis, Microsoft Access MVP


Achille's said:
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..
Click to expand...
Click to expand...
Click to expand...
 
"Update Products SET [Price]"=[Price*2] this worked but as you said it nulled
everything to 0.

Klatuu said:
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.
--
Dave Hargis, Microsoft Access MVP


Achille''''s said:
I get the error "The expression you entered has a function containing the
wrong number of arguments."

Klatuu said:
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.
--
Dave 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..
Click to expand...
Click to expand...
Click to expand...
 
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.
--
Dave Hargis, Microsoft Access MVP


Achille''''s said:
"Update Products SET [Price]"=[Price*2] this worked but as you said it nulled
everything to 0.

Klatuu said:
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.
--
Dave Hargis, Microsoft Access MVP


Achille''''s said:
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.
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.
--
Dave 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..
Click to expand...
Click to expand...
Click to expand...
 
Thanx! It worked!

Klatuu said:
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.
--
Dave Hargis, Microsoft Access MVP


Achille''''s said:
"Update Products SET [Price]"=[Price*2] this worked but as you said it nulled
everything to 0.

Klatuu said:
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.
--
Dave Hargis, Microsoft Access MVP


:

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.
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.
--
Dave 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..
Click to expand...
Click to expand...
Click to expand...
 
Back
Top