Can an update query be used to update/produce a query?

[Pat]

I'd like to use an update query in a chain of nested queries. Is this
possible? In this case the update query will not be updating a table, it
will be changing certain values from the preceding query before proceeding
with the next query in the chain.

My particular need is to substitute a dummy value (-9999) for all null
values in a final table which my query chain makes (the final query in the
chain is a make-table query which produces the table). I have to make this
substitution to several columns of the final table before exporting the table
for use in some other software. I would prefer to automate the substitution
procedure, especially since I may make changes to the query chain in the
future and don't want to have to re-do the substitution manually every time.

Related to this question, but a separate question: In one update query, can
you make changes to several fields based on different criteria for each field?

Thanks,
Patrick

 

[mscertified]

No, you cannot update a query only a table.
You could create a temporary table which you update in the query and access
in later queries.

-Dorian

 

[John Spencer]

You can only update data in tables.

You can use either the IIF operator or the NZ function to display an
alternate value

Field: Nz(Somefield,-9999)
will replace Nulls with -9999 in the query.

Field: IIF(SomeField is Null, -9999,SomeField)
will do the same

If the field is not null, but is just a zero-length string, you will need to
use the IIF construct, since NZ can only work with Nulls.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Pat]

Thanks John. In the query that does the substituting, how do I keep from
changing the fieldname of the field into which I'm substituting the values?
Access gives it the name Expr1, as follows:

Expr1: IIf([PSCP BT det ct.CountOfPSCP] Is Null,-9999,[PSCP BT det
ct.CountOfPSCP])

If I try to change Expr1 to the original field name, PSCP BT det
ct.CountOfPSCP, I get an error message that it's not a valid name, make sure
it doesn't contain invalide characters or punctuation and that it's not too
long.

Thanks,
Patrick

 

[Pat]

Wanted to mention that the field name, though long, has worked fine in this
query up to this point.

Patrick

Thanks John. In the query that does the substituting, how do I keep from
changing the fieldname of the field into which I'm substituting the values?
Access gives it the name Expr1, as follows:

Expr1: IIf([PSCP BT det ct.CountOfPSCP] Is Null,-9999,[PSCP BT det
ct.CountOfPSCP])

If I try to change Expr1 to the original field name, PSCP BT det
ct.CountOfPSCP, I get an error message that it's not a valid name, make sure
it doesn't contain invalide characters or punctuation and that it's not too
long.

Thanks,
Patrick

You can only update data in tables.

You can use either the IIF operator or the NZ function to display an
alternate value

Field: Nz(Somefield,-9999)
will replace Nulls with -9999 in the query.

Field: IIF(SomeField is Null, -9999,SomeField)
will do the same

If the field is not null, but is just a zero-length string, you will need to
use the IIF construct, since NZ can only work with Nulls.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

You can usually do it IF you refer to the field in the expression using the
tablename and the field name

Try the following and see if it works
[PSCP BT det ct.CountOfPSCP] : IIf([Tablename.[PSCP BT det ct.CountOfPSCP]
Is Null,-9999,[TableName].[PSCP BT det ct.CountOfPSCP])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John. In the query that does the substituting, how do I keep from
changing the fieldname of the field into which I'm substituting the
values?
Access gives it the name Expr1, as follows:

Expr1: IIf([PSCP BT det ct.CountOfPSCP] Is Null,-9999,[PSCP BT det
ct.CountOfPSCP])

If I try to change Expr1 to the original field name, PSCP BT det
ct.CountOfPSCP, I get an error message that it's not a valid name, make
sure
it doesn't contain invalide characters or punctuation and that it's not
too
long.

Thanks,
Patrick

You can only update data in tables.

You can use either the IIF operator or the NZ function to display an
alternate value

Field: Nz(Somefield,-9999)
will replace Nulls with -9999 in the query.

Field: IIF(SomeField is Null, -9999,SomeField)
will do the same

If the field is not null, but is just a zero-length string, you will need
to
use the IIF construct, since NZ can only work with Nulls.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Pat]

John, still can't get it to work. Gives the same error.

Thanks,
Patrick

You can usually do it IF you refer to the field in the expression using the
tablename and the field name

Try the following and see if it works
[PSCP BT det ct.CountOfPSCP] : IIf([Tablename.[PSCP BT det ct.CountOfPSCP]
Is Null,-9999,[TableName].[PSCP BT det ct.CountOfPSCP])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John. In the query that does the substituting, how do I keep from
changing the fieldname of the field into which I'm substituting the
values?
Access gives it the name Expr1, as follows:

Expr1: IIf([PSCP BT det ct.CountOfPSCP] Is Null,-9999,[PSCP BT det
ct.CountOfPSCP])

If I try to change Expr1 to the original field name, PSCP BT det
ct.CountOfPSCP, I get an error message that it's not a valid name, make
sure
it doesn't contain invalide characters or punctuation and that it's not
too
long.

Thanks,
Patrick

You can only update data in tables.

You can use either the IIF operator or the NZ function to display an
alternate value

Field: Nz(Somefield,-9999)
will replace Nulls with -9999 in the query.

Field: IIF(SomeField is Null, -9999,SomeField)
will do the same

If the field is not null, but is just a zero-length string, you will need
to
use the IIF construct, since NZ can only work with Nulls.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'd like to use an update query in a chain of nested queries. Is this
possible? In this case the update query will not be updating a table,
it
will be changing certain values from the preceding query before
proceeding
with the next query in the chain.

My particular need is to substitute a dummy value (-9999) for all null
values in a final table which my query chain makes (the final query in
the
chain is a make-table query which produces the table). I have to make
this
substitution to several columns of the final table before exporting the
table
for use in some other software. I would prefer to automate the
substitution
procedure, especially since I may make changes to the query chain in
the
future and don't want to have to re-do the substitution manually every
time.

Related to this question, but a separate question: In one update
query,
can
you make changes to several fields based on different criteria for each
field?

Thanks,
Patrick

 

[John Spencer]

Please copy and paste the exact expression you tried to use. I did notice
that I left out a closing bracket when I posted the suggested solution

[PSCP BT det ct.CountOfPSCP] : IIf([Tablename].[PSCP BT det ct.CountOfPSCP]
Is Null,-9999,[TableName].[PSCP BT det ct.CountOfPSCP])

And as I said "You can ****usually**** do it IF ..."


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John, still can't get it to work. Gives the same error.

Thanks,
Patrick

You can usually do it IF you refer to the field in the expression using
the
tablename and the field name

Try the following and see if it works
[PSCP BT det ct.CountOfPSCP] : IIf([Tablename.[PSCP BT det
ct.CountOfPSCP]
Is Null,-9999,[TableName].[PSCP BT det ct.CountOfPSCP])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John. In the query that does the substituting, how do I keep
from
changing the fieldname of the field into which I'm substituting the
values?
Access gives it the name Expr1, as follows:

Expr1: IIf([PSCP BT det ct.CountOfPSCP] Is Null,-9999,[PSCP BT det
ct.CountOfPSCP])

If I try to change Expr1 to the original field name, PSCP BT det
ct.CountOfPSCP, I get an error message that it's not a valid name, make
sure
it doesn't contain invalide characters or punctuation and that it's not
too
long.

Thanks,
Patrick


:

You can only update data in tables.

You can use either the IIF operator or the NZ function to display an
alternate value

Field: Nz(Somefield,-9999)
will replace Nulls with -9999 in the query.

Field: IIF(SomeField is Null, -9999,SomeField)
will do the same

If the field is not null, but is just a zero-length string, you will
need
to
use the IIF construct, since NZ can only work with Nulls.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'd like to use an update query in a chain of nested queries. Is
this
possible? In this case the update query will not be updating a
table,
it
will be changing certain values from the preceding query before
proceeding
with the next query in the chain.

My particular need is to substitute a dummy value (-9999) for all
null
values in a final table which my query chain makes (the final query
in
the
chain is a make-table query which produces the table). I have to
make
this
substitution to several columns of the final table before exporting
the
table
for use in some other software. I would prefer to automate the
substitution
procedure, especially since I may make changes to the query chain in
the
future and don't want to have to re-do the substitution manually
every
time.

Related to this question, but a separate question: In one update
query,
can
you make changes to several fields based on different criteria for
each
field?

Thanks,
Patrick

 

[Pat]

Here's what I used. I had noticed the missing bracket and had added it
before trying to run it:

[PSCP BT det ct.CountOfPSCP]: IIf([PSCP BT no nd sub].[PSCP BT det
ct.CountOfPSCP] Is Null,-9999,[PSCP BT no nd sub].[PSCP BT det
ct.CountOfPSCP])

The problem seems to be with the fieldname [PSCP BT det ct.CountOfPSCP] that
I'm trying to substitute for the default 'Expr1'. Maybe it is just too long.
It actually works if I leave the fieldname as 'Expr1'. I've since learned
how to shorten field names with a query, and I think I'll do that rather than
working with such long field names.

Thanks,
Patrick

Please copy and paste the exact expression you tried to use. I did notice
that I left out a closing bracket when I posted the suggested solution

[PSCP BT det ct.CountOfPSCP] : IIf([Tablename].[PSCP BT det ct.CountOfPSCP]
Is Null,-9999,[TableName].[PSCP BT det ct.CountOfPSCP])

And as I said "You can ****usually**** do it IF ..."


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John, still can't get it to work. Gives the same error.

Thanks,
Patrick

You can usually do it IF you refer to the field in the expression using
the
tablename and the field name

Try the following and see if it works
[PSCP BT det ct.CountOfPSCP] : IIf([Tablename.[PSCP BT det
ct.CountOfPSCP]
Is Null,-9999,[TableName].[PSCP BT det ct.CountOfPSCP])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John. In the query that does the substituting, how do I keep
from
changing the fieldname of the field into which I'm substituting the
values?
Access gives it the name Expr1, as follows:

Expr1: IIf([PSCP BT det ct.CountOfPSCP] Is Null,-9999,[PSCP BT det
ct.CountOfPSCP])

If I try to change Expr1 to the original field name, PSCP BT det
ct.CountOfPSCP, I get an error message that it's not a valid name, make
sure
it doesn't contain invalide characters or punctuation and that it's not
too
long.

Thanks,
Patrick


:

You can only update data in tables.

You can use either the IIF operator or the NZ function to display an
alternate value

Field: Nz(Somefield,-9999)
will replace Nulls with -9999 in the query.

Field: IIF(SomeField is Null, -9999,SomeField)
will do the same

If the field is not null, but is just a zero-length string, you will
need
to
use the IIF construct, since NZ can only work with Nulls.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'd like to use an update query in a chain of nested queries. Is
this
possible? In this case the update query will not be updating a
table,
it
will be changing certain values from the preceding query before
proceeding
with the next query in the chain.

My particular need is to substitute a dummy value (-9999) for all
null
values in a final table which my query chain makes (the final query
in
the
chain is a make-table query which produces the table). I have to
make
this
substitution to several columns of the final table before exporting
the
table
for use in some other software. I would prefer to automate the
substitution
procedure, especially since I may make changes to the query chain in
the
future and don't want to have to re-do the substitution manually
every
time.

Related to this question, but a separate question: In one update
query,
can
you make changes to several fields based on different criteria for
each
field?

Thanks,
Patrick