IIf statement in update query

[janmd]

Is it possible to use an Iif statement to on the Update to row in an
update query?

 

[Clifford Bass]

Hi,

Most assuredly.

update tblSomeTable
set SomeField1 = IIf(SomeField2 = 5, 10, 20);

Clifford Bass

 

[janmd]

Hi,

     Most assuredly.

update tblSomeTable
set SomeField1 = IIf(SomeField2 = 5, 10, 20);

               Clifford Bass





- Show quoted text -

I am trying to update a field so that if it already has a figure in it
(is not null), it will stay what it currently is, otherwise it will be
set to a field in another table.

iif([field1] is not null, [field1], [field2]) but it doesn't seem to
like it.

 

[MikeJohnB]

try

IIf(Not (IsNull([Field1])),[Field1],[Field2])

Hope this helps????

Regards

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Hi,

Most assuredly.

update tblSomeTable
set SomeField1 = IIf(SomeField2 = 5, 10, 20);

Clifford Bass





- Show quoted text -

I am trying to update a field so that if it already has a figure in it
(is not null), it will stay what it currently is, otherwise it will be
set to a field in another table.

iif([field1] is not null, [field1], [field2]) but it doesn't seem to
like it.

 

[Clifford Bass]

Hi,

In that case do this:

update tblSomeTable
set SomeField1 = SomeField2
where SomeField1 is null;

If you use the the Iff() part in the update part, you will
unnecessarily be updating rows that do not need to be updated. Not a big
issue if there are only a few records in the table. But as the row count
grows, it will take longer and longer. It also can lock up the whole table
when it does not need to be. An issue if you have multiple users.

Clifford Bass

 

[janmd]

Hi,

     In that case do this:

update tblSomeTable
set SomeField1 = SomeField2
where SomeField1 is null;

     If you use the the Iff() part in the update part, you will
unnecessarily be updating rows that do not need to be updated.  Not a big
issue if there are only a few records in the table.  But as the row count
grows, it will take longer and longer.  It also can lock up the whole table
when it does not need to be.  An issue if you have multiple users.

             Clifford Bass

I am trying to update a field so that if it already has a figure in it
(is not null), it will stay what it currently is, otherwise it will be
set to a field in another table.

iif([field1] is not null, [field1], [field2]) but it doesn't seem to
like it.- Hide quoted text -

- Show quoted text -

Thanks to both of you- however Mike's formula doesn't seem to update
it, and I'm not sure how to use Clifford's - it didn't like Where in
the update to row

 

[Clifford Bass]

Hi,

Put "Is Null", without the quotes, in the Criteria row for the
appropriate field. There will provide the where clause for the SQL statement
and should do it. If not and you still have trouble, change your query to
SQL view, copy the SQL text and post it.

Clifford Bass

 

[MikeJohnB]

Hi Jan

I only gave you the criteria you asked for, I left the update query to you.

I think the criteria that Clifford gave you is a SQL string which in fact is
a better way to get where you want to get.

I can give you my SQL string as follows:

UPDATE yourtablename SET yourtablename.[Field1] = IIf(Not
(IsNull([Field1])),[Field1],[Field2]);

Make an update query, goto the SQL view (Simple Query and select Update
Query from the Query Icon on the top menu bar to convert it)

Paste in the above to the SQL view replacing what is there and open the
query in design view again.

You should now see in the query grid

Field: Field1
Table: yourtablename
Update To: IIf(Not (IsNull([Field1])),[Field1],[Field2]);

Or enter as above in the update query grid in design view.

If you prefer to use Clifford's method, paste in SQL view the following.

update tblSomeTable set SomeField1 = SomeField2 where SomeField1 is null;

Sorry I haven't tested Cliffords answer so I can't give you the design view
of the query grid

I hope this helps explain the answers a little????

Regards
Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Hi,

In that case do this:

update tblSomeTable
set SomeField1 = SomeField2
where SomeField1 is null;

If you use the the Iff() part in the update part, you will
unnecessarily be updating rows that do not need to be updated. Not a big
issue if there are only a few records in the table. But as the row count
grows, it will take longer and longer. It also can lock up the whole table
when it does not need to be. An issue if you have multiple users.

Clifford Bass

I am trying to update a field so that if it already has a figure in it
(is not null), it will stay what it currently is, otherwise it will be
set to a field in another table.

iif([field1] is not null, [field1], [field2]) but it doesn't seem to
like it.- Hide quoted text -

- Show quoted text -

Thanks to both of you- however Mike's formula doesn't seem to update
it, and I'm not sure how to use Clifford's - it didn't like Where in
the update to row

 

[MikeJohnB]

As a post script, make sure your field is set to null default and not
something like 0 for instance else the update will not work because the field
will not have null values. (Stating the obvious I guess)

And again, stating the obvious, change field names and table name to your
names.
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Hi Jan

I only gave you the criteria you asked for, I left the update query to you.

I think the criteria that Clifford gave you is a SQL string which in fact is
a better way to get where you want to get.

I can give you my SQL string as follows:

UPDATE yourtablename SET yourtablename.[Field1] = IIf(Not
(IsNull([Field1])),[Field1],[Field2]);

Make an update query, goto the SQL view (Simple Query and select Update
Query from the Query Icon on the top menu bar to convert it)

Paste in the above to the SQL view replacing what is there and open the
query in design view again.

You should now see in the query grid

Field: Field1
Table: yourtablename
Update To: IIf(Not (IsNull([Field1])),[Field1],[Field2]);

Or enter as above in the update query grid in design view.

If you prefer to use Clifford's method, paste in SQL view the following.

update tblSomeTable set SomeField1 = SomeField2 where SomeField1 is null;

Sorry I haven't tested Cliffords answer so I can't give you the design view
of the query grid

I hope this helps explain the answers a little????

Regards
Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Hi,

In that case do this:

update tblSomeTable
set SomeField1 = SomeField2
where SomeField1 is null;

If you use the the Iff() part in the update part, you will
unnecessarily be updating rows that do not need to be updated. Not a big
issue if there are only a few records in the table. But as the row count
grows, it will take longer and longer. It also can lock up the whole table
when it does not need to be. An issue if you have multiple users.

Clifford Bass



:
I am trying to update a field so that if it already has a figure in it
(is not null), it will stay what it currently is, otherwise it will be
set to a field in another table.

iif([field1] is not null, [field1], [field2]) but it doesn't seem to
like it.- Hide quoted text -

- Show quoted text -

Thanks to both of you- however Mike's formula doesn't seem to update
it, and I'm not sure how to use Clifford's - it didn't like Where in
the update to row

 

[janmd]

Hi Jan

I only gave you the criteria you asked for, I left the update query to you.

I think the criteria that Clifford gave you is a SQL string which in factis
a better way to get where you want to get.

I can give you my SQL string as follows:

UPDATE yourtablename SET yourtablename.[Field1] = IIf(Not
(IsNull([Field1])),[Field1],[Field2]);

Make an update query, goto the SQL view (Simple Query and select Update
Query from the Query Icon on the top menu bar to convert it)

Paste in the above to the SQL view replacing what is there and open the
query in design view again.

You should now see in the query grid

Field:          Field1
Table:         yourtablename
Update To:  IIf(Not (IsNull([Field1])),[Field1],[Field2]);

Or enter as above in the update query grid in design view.

If you prefer to use Clifford's method, paste in SQL view the following.

update tblSomeTable set SomeField1 = SomeField2 where SomeField1 is null;

Sorry I haven't tested Cliffords answer so I can't give you the design view
of the query grid

I hope this helps explain the answers a little????

Regards
Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider  providing some feed back to the response you have recieved.
Kindest Regards Mike B

Hi,
     In that case do this:
update tblSomeTable
set SomeField1 = SomeField2
where SomeField1 is null;
     If you use the the Iff() part in the update part, you will
unnecessarily be updating rows that do not need to be updated.  Nota big
issue if there are only a few records in the table.  But as the rowcount
grows, it will take longer and longer.  It also can lock up the whole table
when it does not need to be.  An issue if you have multiple users.
             Clifford Bass
:
I am trying to update a field so that if it already has a figure init
(is not null), it will stay what it currently is, otherwise it willbe
set to a field in another table.
iif([field1] is not null, [field1], [field2]) but it doesn't seem to
like it.- Hide quoted text -
- Show quoted text -

Thanks to both of you- however Mike's formula doesn't seem to update
it, and I'm not sure how to use Clifford's - it didn't like Where in
the update to row- Hide quoted text -

- Show quoted text -

Many thanks - I'll give it a go over the w/e or early next week. Have
a good w/e.

 

[janmd]

Hi Jan

I only gave you the criteria you asked for, I left the update query to you.

I think the criteria that Clifford gave you is a SQL string which in fact is
a better way to get where you want to get.

I can give you my SQL string as follows:

UPDATE yourtablename SET yourtablename.[Field1] = IIf(Not
(IsNull([Field1])),[Field1],[Field2]);

Make an update query, goto the SQL view (Simple Query and select Update
Query from the Query Icon on the top menu bar to convert it)

Paste in the above to the SQL view replacing what is there and open the
query in design view again.

You should now see in the query grid

Field:          Field1
Table:         yourtablename
Update To:  IIf(Not (IsNull([Field1])),[Field1],[Field2]);

Or enter as above in the update query grid in design view.

If you prefer to use Clifford's method, paste in SQL view the following..

update tblSomeTable set SomeField1 = SomeField2 where SomeField1 is null;

Sorry I haven't tested Cliffords answer so I can't give you the design view
of the query grid

I hope this helps explain the answers a little????

Regards
Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider  providing some feed back to the response you have recieved.
Kindest Regards Mike B

On 29 Jan, 19:16, Clifford Bass
Hi,
     In that case do this:
update tblSomeTable
set SomeField1 = SomeField2
where SomeField1 is null;
     If you use the the Iff() part in the update part, you will
unnecessarily be updating rows that do not need to be updated.  Not a big
issue if there are only a few records in the table.  But as the row count
grows, it will take longer and longer.  It also can lock up the whole table
when it does not need to be.  An issue if you have multiple users..
             Clifford Bass
:
I am trying to update a field so that if it already has a figure in it
(is not null), it will stay what it currently is, otherwise it will be
set to a field in another table.
iif([field1] is not null, [field1], [field2]) but it doesn't seemto
like it.- Hide quoted text -
- Show quoted text -
Thanks to both of you- however Mike's formula doesn't seem to update
it, and I'm not sure how to use Clifford's - it didn't like Where in
the update to row- Hide quoted text -

- Show quoted text -

Many thanks - I'll give it a go over the w/e or early next week. Have
a good w/e.- Hide quoted text -

- Show quoted text -

Many thanks - I think I've done it now. Sometimes coming back to it
when not tired at the end of the day halps one to think more clearly!!

 

[Clifford Bass]

Hi,

You are welcome!

Clifford Bass