modifying field values in an Access 2003 query

[Jan07]

Someone please help. Is there a SQL or Visual Basic statement that can be
used to set/change the values in a field in an Access 2003 query to a default
value?

 

[Jeff Boyce]

I'm more familiar with the concept of a table's field (or a control on a
form) having a "default" value. How is it that you expect a query's field
to have a default value? What are the circumstances?

If, for example, you want to display something like "n/a" when an underlying
field in a table contains a Null, you could use something like:

YourQueryField:
IIF(IsNull([YourUnderlyingField]),"n/a",[YourUnderlyingField])

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jan07]

Thank you…the circumstances are a bit confusing, but I’ll attempt to explain.
I need to provide a monthly count of different transaction types which are
stored in a single table in the database. The various transaction types are
identified by a [Tr_type] field containing single letter or letter
combination values. The problem is that some records are added to the
database as one transaction type even though they are really two different
types of transactions, which need to be grouped and counted separately. These
particular records can be distinguished by their transaction numbers (a
trans_num field). What I am trying to do in the query is change the
[Tr_type] of those records if the value in the [trans_num] field follows a
specified sequence, so that they can be grouped and tallied separately in a
report. I cannot utilize an update query to change the transaction type in
the underlying table because I need to create a report that the user can run
without having to do an update query first. I’m wondering if there is some
type of “If†statement or function that can be used in the query to
accomplish this. I can only use the transaction number to distinguish this
particular group of records in the database…for all other records I have to
use the transaction type.

Thanks for reading...Jan07

I'm more familiar with the concept of a table's field (or a control on a
form) having a "default" value. How is it that you expect a query's field
to have a default value? What are the circumstances?

If, for example, you want to display something like "n/a" when an underlying
field in a table contains a Null, you could use something like:

YourQueryField:
IIF(IsNull([YourUnderlyingField]),"n/a",[YourUnderlyingField])

Regards

Jeff Boyce
Microsoft Office/Access MVP

Someone please help. Is there a SQL or Visual Basic statement that can be
used to set/change the values in a field in an Access 2003 query to a
default
value?

 

[Jeff Boyce]

I may not understand enough yet, but I suspect the IIF() statement/function
should be able to do what you are describing.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you.the circumstances are a bit confusing, but I'll attempt to
explain.
I need to provide a monthly count of different transaction types which are
stored in a single table in the database. The various transaction types
are
identified by a [Tr_type] field containing single letter or letter
combination values. The problem is that some records are added to the
database as one transaction type even though they are really two different
types of transactions, which need to be grouped and counted separately.
These
particular records can be distinguished by their transaction numbers (a
trans_num field). What I am trying to do in the query is change the
[Tr_type] of those records if the value in the [trans_num] field follows a
specified sequence, so that they can be grouped and tallied separately in
a
report. I cannot utilize an update query to change the transaction type
in
the underlying table because I need to create a report that the user can
run
without having to do an update query first. I'm wondering if there is some
type of "If" statement or function that can be used in the query to
accomplish this. I can only use the transaction number to distinguish
this
particular group of records in the database.for all other records I have
to
use the transaction type.

Thanks for reading...Jan07

I'm more familiar with the concept of a table's field (or a control on a
form) having a "default" value. How is it that you expect a query's
field
to have a default value? What are the circumstances?

If, for example, you want to display something like "n/a" when an
underlying
field in a table contains a Null, you could use something like:

YourQueryField:
IIF(IsNull([YourUnderlyingField]),"n/a",[YourUnderlyingField])

Regards

Jeff Boyce
Microsoft Office/Access MVP

Someone please help. Is there a SQL or Visual Basic statement that can
be
used to set/change the values in a field in an Access 2003 query to a
default
value?