Query Expressions

[Kristibaer]

If I have a record that contains a field that is a type-code and another
field a number, is there an expression that will allow me to state if certain
information is present in one field of a record, then change the quantity to
a negative value?

For example, in my inventory transaction table one field of each record is a
transaction type that has a either an "I" or an "O" (in or out) and another
field contains a positive quantity. What expression can I use (in a new
field) that will change quantities to negative for all records that have "O"
in the transaction type field and leave the records with "I" as a positive
number?

Thank you,
Kristi

 

[Conan Kelly]

Kristi,

Enter the following in the "Field:" row in your query design grid, changing
field names appropriately:

NewFieldName: Iif([Transaction Type Field Name]="O",[Quantity Field
Name]*-1,[Quantity Field Name])

If you query has more than one table and the fields you are using are in
both tables (spelled exactly the same), you will need to qualify your field
with a table. For example: [TableName].[FieldName].

HTH,

Conan

 

[John Spencer]

Field: PostiveOrNegative: IIF([Transaction Type] = "O", Quantity * -1,
Quantity)



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

 

[Kristibaer]

Thanks Conan: So if I also want all records that have tran type "I" to be
diplayed basically as they already exist in the table (positive number)
would the following expression work:

item date trantype qty NewField
PTTA0050K 1/26/2007 I 24 24
PTTC0050K 1/26/2007 O 2460 -2460


New Field: If([trantype]="O",[qty]*-1,[qty])

Or If([trantype]="I",[qty])

Kristi

Kristi,

Enter the following in the "Field:" row in your query design grid, changing
field names appropriately:

NewFieldName: Iif([Transaction Type Field Name]="O",[Quantity Field
Name]*-1,[Quantity Field Name])

If you query has more than one table and the fields you are using are in
both tables (spelled exactly the same), you will need to qualify your field
with a table. For example: [TableName].[FieldName].

HTH,

Conan

If I have a record that contains a field that is a type-code and another
field a number, is there an expression that will allow me to state if
certain
information is present in one field of a record, then change the quantity
to
a negative value?

For example, in my inventory transaction table one field of each record is
a
transaction type that has a either an "I" or an "O" (in or out) and
another
field contains a positive quantity. What expression can I use (in a new
field) that will change quantities to negative for all records that have
"O"
in the transaction type field and leave the records with "I" as a positive
number?

Thank you,
Kristi

 

[Rob Parker]

Hi Kristi,

Something like this should do the trick:
IIf([TransactionTypeFieldName]="O",[QuantityFieldName]*-1,[QuantityFieldName])

HTH,

Rob

 

[Conan Kelly]

Kristi,

The formula I gave you should already do that.

I used the IIF() function. Its syntax for IIF() is:

Iif(logical test (returns TRUE or FALSE), value if true, value if false)

My logical test was "is [trantype] equal to O", my value if true was
"multiply [qty] by negative 1" (turning it into a negative number), and my
value if false was "display [qty] as is".

So anytime trantype is "O" this new field is will display the negative of
the qty field, but anything other than "O", this field will display the qty
field as is.

Out of the 2 different expressions you posted, "New Field:
If([trantype]="O",[qty]*-1,[qty])" should work.

Does that answer your question or did I misunderstand you?

HTH,

Conan

Thanks Conan: So if I also want all records that have tran type "I" to be
diplayed basically as they already exist in the table (positive number)
would the following expression work:

item date trantype qty NewField
PTTA0050K 1/26/2007 I 24 24
PTTC0050K 1/26/2007 O 2460 -2460


New Field: If([trantype]="O",[qty]*-1,[qty])

Or If([trantype]="I",[qty])

Kristi

Kristi,

Enter the following in the "Field:" row in your query design grid,
changing
field names appropriately:

NewFieldName: Iif([Transaction Type Field Name]="O",[Quantity Field
Name]*-1,[Quantity Field Name])

If you query has more than one table and the fields you are using are in
both tables (spelled exactly the same), you will need to qualify your
field
with a table. For example: [TableName].[FieldName].

HTH,

Conan

If I have a record that contains a field that is a type-code and
another
field a number, is there an expression that will allow me to state if
certain
information is present in one field of a record, then change the
quantity
to
a negative value?

For example, in my inventory transaction table one field of each record
is
a
transaction type that has a either an "I" or an "O" (in or out) and
another
field contains a positive quantity. What expression can I use (in a
new
field) that will change quantities to negative for all records that
have
"O"
in the transaction type field and leave the records with "I" as a
positive
number?

Thank you,
Kristi

 

[Kristibaer]

Thanks Conan-that did it! I am in the process of re-creating inventory
activity files that were corrupted and need to get a summary of all item
movement but for whatever reason the FoxPro table just sees numbers, not
negative or positive.

Thanks again,
Kristi

Kristi,

The formula I gave you should already do that.

I used the IIF() function. Its syntax for IIF() is:

Iif(logical test (returns TRUE or FALSE), value if true, value if false)

My logical test was "is [trantype] equal to O", my value if true was
"multiply [qty] by negative 1" (turning it into a negative number), and my
value if false was "display [qty] as is".

So anytime trantype is "O" this new field is will display the negative of
the qty field, but anything other than "O", this field will display the qty
field as is.

Out of the 2 different expressions you posted, "New Field:
If([trantype]="O",[qty]*-1,[qty])" should work.

Does that answer your question or did I misunderstand you?

HTH,

Conan

Thanks Conan: So if I also want all records that have tran type "I" to be
diplayed basically as they already exist in the table (positive number)
would the following expression work:

item date trantype qty NewField
PTTA0050K 1/26/2007 I 24 24
PTTC0050K 1/26/2007 O 2460 -2460


New Field: If([trantype]="O",[qty]*-1,[qty])

Or If([trantype]="I",[qty])

Kristi

Kristi,

Enter the following in the "Field:" row in your query design grid,
changing
field names appropriately:

NewFieldName: Iif([Transaction Type Field Name]="O",[Quantity Field
Name]*-1,[Quantity Field Name])

If you query has more than one table and the fields you are using are in
both tables (spelled exactly the same), you will need to qualify your
field
with a table. For example: [TableName].[FieldName].

HTH,

Conan





If I have a record that contains a field that is a type-code and
another
field a number, is there an expression that will allow me to state if
certain
information is present in one field of a record, then change the
quantity
to
a negative value?

For example, in my inventory transaction table one field of each record
is
a
transaction type that has a either an "I" or an "O" (in or out) and
another
field contains a positive quantity. What expression can I use (in a
new
field) that will change quantities to negative for all records that
have
"O"
in the transaction type field and leave the records with "I" as a
positive
number?

Thank you,
Kristi

 

[Conan Kelly]

Glad I could help,

Conan

Thanks Conan-that did it! I am in the process of re-creating inventory
activity files that were corrupted and need to get a summary of all item
movement but for whatever reason the FoxPro table just sees numbers, not
negative or positive.

Thanks again,
Kristi

Kristi,

The formula I gave you should already do that.

I used the IIF() function. Its syntax for IIF() is:

Iif(logical test (returns TRUE or FALSE), value if true, value if
false)

My logical test was "is [trantype] equal to O", my value if true was
"multiply [qty] by negative 1" (turning it into a negative number), and
my
value if false was "display [qty] as is".

So anytime trantype is "O" this new field is will display the negative of
the qty field, but anything other than "O", this field will display the
qty
field as is.

Out of the 2 different expressions you posted, "New Field:
If([trantype]="O",[qty]*-1,[qty])" should work.

Does that answer your question or did I misunderstand you?

HTH,

Conan

Thanks Conan: So if I also want all records that have tran type "I" to
be
diplayed basically as they already exist in the table (positive
number)
would the following expression work:

item date trantype qty NewField
PTTA0050K 1/26/2007 I 24 24
PTTC0050K 1/26/2007 O 2460 -2460


New Field: If([trantype]="O",[qty]*-1,[qty])

Or If([trantype]="I",[qty])

Kristi

:

Kristi,

Enter the following in the "Field:" row in your query design grid,
changing
field names appropriately:

NewFieldName: Iif([Transaction Type Field Name]="O",[Quantity Field
Name]*-1,[Quantity Field Name])

If you query has more than one table and the fields you are using are
in
both tables (spelled exactly the same), you will need to qualify your
field
with a table. For example: [TableName].[FieldName].

HTH,

Conan





If I have a record that contains a field that is a type-code and
another
field a number, is there an expression that will allow me to state
if
certain
information is present in one field of a record, then change the
quantity
to
a negative value?

For example, in my inventory transaction table one field of each
record
is
a
transaction type that has a either an "I" or an "O" (in or out) and
another
field contains a positive quantity. What expression can I use (in a
new
field) that will change quantities to negative for all records that
have
"O"
in the transaction type field and leave the records with "I" as a
positive
number?

Thank you,
Kristi