IIf to return a formula or field

[waterbug]

I do not have any problems getting the function to give me a text answer but
I need something like this IIf([machinestep]<140, [price]/2, [price]). This
was obviously not working. If the data in the field [machinestep] is less
than 140 then I want the answer to be [price] divided by 2, or the data that
is in the [price] field if it is not less than 140. I do not have a problem
with getting the first part of the string to give me "text", "text1" but not
the other way. Thanks for any advice!

 

[KARL DEWEY]

Is the datatype for the [price] field a number field?

 

[Chaim]

What function? What's a 'text answer'? What do you mean 'This was obviously
not working.". It may be obvious to you, but we aren't there.

You've got to provide some more detail.

 

[waterbug]

yes, the [price] field is numeric

Is the datatype for the [price] field a number field?

I do not have any problems getting the function to give me a text answer but
I need something like this IIf([machinestep]<140, [price]/2, [price]). This
was obviously not working. If the data in the field [machinestep] is less
than 140 then I want the answer to be [price] divided by 2, or the data that
is in the [price] field if it is not less than 140. I do not have a problem
with getting the first part of the string to give me "text", "text1" but not
the other way. Thanks for any advice!

 

[waterbug]

What I mean by "text" answer is this
IIf([machinestep]<140, "large", "small") as an example will work. It will
give me "large" if the the [machinestep] is less than 140 and give me "small"
if it is greater. I want to get the price that is in the price field divided
by 2 as an answer for the first part and if it does not meet that criteria I
want the answer to be whatever price is in that field.

What function? What's a 'text answer'? What do you mean 'This was obviously
not working.". It may be obvious to you, but we aren't there.

You've got to provide some more detail.
--
Chaim

I do not have any problems getting the function to give me a text answer but
I need something like this IIf([machinestep]<140, [price]/2, [price]). This
was obviously not working. If the data in the field [machinestep] is less
than 140 then I want the answer to be [price] divided by 2, or the data that
is in the [price] field if it is not less than 140. I do not have a problem
with getting the first part of the string to give me "text", "text1" but not
the other way. Thanks for any advice!

 

[KARL DEWEY]

Try --
IIf([machinestep]<140, Sum([price]/2), [price])

yes, the [price] field is numeric

Is the datatype for the [price] field a number field?

I do not have any problems getting the function to give me a text answer but
I need something like this IIf([machinestep]<140, [price]/2, [price]). This
was obviously not working. If the data in the field [machinestep] is less
than 140 then I want the answer to be [price] divided by 2, or the data that
is in the [price] field if it is not less than 140. I do not have a problem
with getting the first part of the string to give me "text", "text1" but not
the other way. Thanks for any advice!

 

[waterbug]

I really appreciate your input! It is getting close. But it is giving me
the sum of all the value in the price category. It is dividing by 2 but all
my results are either $1232.25 or $2464.50.

Try --
IIf([machinestep]<140, Sum([price]/2), [price])

yes, the [price] field is numeric

Is the datatype for the [price] field a number field?

:

I do not have any problems getting the function to give me a text answer but
I need something like this IIf([machinestep]<140, [price]/2, [price]). This
was obviously not working. If the data in the field [machinestep] is less
than 140 then I want the answer to be [price] divided by 2, or the data that
is in the [price] field if it is not less than 140. I do not have a problem
with getting the first part of the string to give me "text", "text1" but not
the other way. Thanks for any advice!

 

[KARL DEWEY]

So where is it getting that value from? Post your SQL.

I really appreciate your input! It is getting close. But it is giving me
the sum of all the value in the price category. It is dividing by 2 but all
my results are either $1232.25 or $2464.50.

Try --
IIf([machinestep]<140, Sum([price]/2), [price])

yes, the [price] field is numeric

:

Is the datatype for the [price] field a number field?

:

I do not have any problems getting the function to give me a text answer but
I need something like this IIf([machinestep]<140, [price]/2, [price]). This
was obviously not working. If the data in the field [machinestep] is less
than 140 then I want the answer to be [price] divided by 2, or the data that
is in the [price] field if it is not less than 140. I do not have a problem
with getting the first part of the string to give me "text", "text1" but not
the other way. Thanks for any advice!

 

[waterbug]

I copied the SQL below. I have had to put
"=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" in the report under the
or_cpu (price) field. WHen I put it in the query on the field line
"cost:=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" I get an error that
states "you tried to execute a query that does not inlcude the specified
expression 'cu_customer_name' as part of an aggregate function".
"cu_customer_name" is just the field with the customer name. Thanks again.

The value comes from when I enter a price each for the product. Normally I
would say I entered in a form, but the tables in this application are linked
into another foxpro program but the values are correct when I run the query
by itself without any IIf function. So if the part cost $30.00 or $100.00 it
shows up fine, but in the expression you had me try, it is adding up all the
values.

example:
Date range 08/01/05-08/05/05
customer A price $30.00 each and the return on the expression becomes
$380.00
customer B price $200.00 each (<140) " " " " " $190.00
customer C price $50.00 each " " " " " $380.00
customer D price $100.00 each (<140) " " " " " $190.00

SELECT q_cust.cu_cust_name, order.or_part_desc, order.or_cpu,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.jc_mach_code,
RejectQ.mt_mach_desc, RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;

thanks -

 

[KARL DEWEY]

I just built the tables and tested the query below - works.

SELECT q_cust.cu_cust_name, order.or_part_desc, RejectQ.jc_mach_code,
order.or_cpu, IIf([jc_mach_code]<140,[or_cpu]/2,[or_cpu]) AS Price,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.mt_mach_desc,
RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;

 

[waterbug]

The error stopped but the data (answer) shows #ERROR. Everything looks right
and from what I read, it should work. I hate to burn you out on this so if
you give up, I certainly understand. Thanks -Janet

I just built the tables and tested the query below - works.

SELECT q_cust.cu_cust_name, order.or_part_desc, RejectQ.jc_mach_code,
order.or_cpu, IIf([jc_mach_code]<140,[or_cpu]/2,[or_cpu]) AS Price,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.mt_mach_desc,
RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;

I copied the SQL below. I have had to put
"=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" in the report under the
or_cpu (price) field. WHen I put it in the query on the field line
"cost:=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" I get an error that
states "you tried to execute a query that does not inlcude the specified
expression 'cu_customer_name' as part of an aggregate function".
"cu_customer_name" is just the field with the customer name. Thanks again.

The value comes from when I enter a price each for the product. Normally I
would say I entered in a form, but the tables in this application are linked
into another foxpro program but the values are correct when I run the query
by itself without any IIf function. So if the part cost $30.00 or $100.00 it
shows up fine, but in the expression you had me try, it is adding up all the
values.

example:
Date range 08/01/05-08/05/05
customer A price $30.00 each and the return on the expression becomes
$380.00
customer B price $200.00 each (<140) " " " " " $190.00
customer C price $50.00 each " " " " " $380.00
customer D price $100.00 each (<140) " " " " " $190.00

SELECT q_cust.cu_cust_name, order.or_part_desc, order.or_cpu,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.jc_mach_code,
RejectQ.mt_mach_desc, RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;

thanks -

 

[KARL DEWEY]

Maybe check some of your fields --

jc_mach_code and or_cpu must be number fields.

The error stopped but the data (answer) shows #ERROR. Everything looks right
and from what I read, it should work. I hate to burn you out on this so if
you give up, I certainly understand. Thanks -Janet

I just built the tables and tested the query below - works.

SELECT q_cust.cu_cust_name, order.or_part_desc, RejectQ.jc_mach_code,
order.or_cpu, IIf([jc_mach_code]<140,[or_cpu]/2,[or_cpu]) AS Price,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.mt_mach_desc,
RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;

I copied the SQL below. I have had to put
"=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" in the report under the
or_cpu (price) field. WHen I put it in the query on the field line
"cost:=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" I get an error that
states "you tried to execute a query that does not inlcude the specified
expression 'cu_customer_name' as part of an aggregate function".
"cu_customer_name" is just the field with the customer name. Thanks again.

The value comes from when I enter a price each for the product. Normally I
would say I entered in a form, but the tables in this application are linked
into another foxpro program but the values are correct when I run the query
by itself without any IIf function. So if the part cost $30.00 or $100.00 it
shows up fine, but in the expression you had me try, it is adding up all the
values.

example:
Date range 08/01/05-08/05/05
customer A price $30.00 each and the return on the expression becomes
$380.00
customer B price $200.00 each (<140) " " " " " $190.00
customer C price $50.00 each " " " " " $380.00
customer D price $100.00 each (<140) " " " " " $190.00

SELECT q_cust.cu_cust_name, order.or_part_desc, order.or_cpu,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.jc_mach_code,
RejectQ.mt_mach_desc, RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;

thanks -

 

[waterbug]

The or_cpu is a number field but not the jc_mach_code. Unfortunately I can't
change anything because the tables come from another software program that
was built in FoxPro. I'll see what I can do to possibly to retrieve the data
from another field. Thanks! -Janet

Maybe check some of your fields --

jc_mach_code and or_cpu must be number fields.

The error stopped but the data (answer) shows #ERROR. Everything looks right
and from what I read, it should work. I hate to burn you out on this so if
you give up, I certainly understand. Thanks -Janet

I just built the tables and tested the query below - works.

SELECT q_cust.cu_cust_name, order.or_part_desc, RejectQ.jc_mach_code,
order.or_cpu, IIf([jc_mach_code]<140,[or_cpu]/2,[or_cpu]) AS Price,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.mt_mach_desc,
RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;


:

I copied the SQL below. I have had to put
"=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" in the report under the
or_cpu (price) field. WHen I put it in the query on the field line
"cost:=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" I get an error that
states "you tried to execute a query that does not inlcude the specified
expression 'cu_customer_name' as part of an aggregate function".
"cu_customer_name" is just the field with the customer name. Thanks again.

The value comes from when I enter a price each for the product. Normally I
would say I entered in a form, but the tables in this application are linked
into another foxpro program but the values are correct when I run the query
by itself without any IIf function. So if the part cost $30.00 or $100.00 it
shows up fine, but in the expression you had me try, it is adding up all the
values.

example:
Date range 08/01/05-08/05/05
customer A price $30.00 each and the return on the expression becomes
$380.00
customer B price $200.00 each (<140) " " " " " $190.00
customer C price $50.00 each " " " " " $380.00
customer D price $100.00 each (<140) " " " " " $190.00

SELECT q_cust.cu_cust_name, order.or_part_desc, order.or_cpu,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.jc_mach_code,
RejectQ.mt_mach_desc, RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;

thanks -

 

[KARL DEWEY]

Use --
Val([jc_mach_code]) <140

The or_cpu is a number field but not the jc_mach_code. Unfortunately I can't
change anything because the tables come from another software program that
was built in FoxPro. I'll see what I can do to possibly to retrieve the data
from another field. Thanks! -Janet

Maybe check some of your fields --

jc_mach_code and or_cpu must be number fields.

The error stopped but the data (answer) shows #ERROR. Everything looks right
and from what I read, it should work. I hate to burn you out on this so if
you give up, I certainly understand. Thanks -Janet

:

I just built the tables and tested the query below - works.

SELECT q_cust.cu_cust_name, order.or_part_desc, RejectQ.jc_mach_code,
order.or_cpu, IIf([jc_mach_code]<140,[or_cpu]/2,[or_cpu]) AS Price,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.mt_mach_desc,
RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;


:

I copied the SQL below. I have had to put
"=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" in the report under the
or_cpu (price) field. WHen I put it in the query on the field line
"cost:=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" I get an error that
states "you tried to execute a query that does not inlcude the specified
expression 'cu_customer_name' as part of an aggregate function".
"cu_customer_name" is just the field with the customer name. Thanks again.

The value comes from when I enter a price each for the product. Normally I
would say I entered in a form, but the tables in this application are linked
into another foxpro program but the values are correct when I run the query
by itself without any IIf function. So if the part cost $30.00 or $100.00 it
shows up fine, but in the expression you had me try, it is adding up all the
values.

example:
Date range 08/01/05-08/05/05
customer A price $30.00 each and the return on the expression becomes
$380.00
customer B price $200.00 each (<140) " " " " " $190.00
customer C price $50.00 each " " " " " $380.00
customer D price $100.00 each (<140) " " " " " $190.00

SELECT q_cust.cu_cust_name, order.or_part_desc, order.or_cpu,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.jc_mach_code,
RejectQ.mt_mach_desc, RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;

thanks -

 

[waterbug]

YES! It worked! I did WorkCenter:Val([jc_mach_code]) <140 then I created
another query and used the other IIf formula and it worked perfectly!! Thank
you so so so much!!!!!!!!!! -Janet

Use --
Val([jc_mach_code]) <140

The or_cpu is a number field but not the jc_mach_code. Unfortunately I can't
change anything because the tables come from another software program that
was built in FoxPro. I'll see what I can do to possibly to retrieve the data
from another field. Thanks! -Janet

Maybe check some of your fields --

jc_mach_code and or_cpu must be number fields.


:

The error stopped but the data (answer) shows #ERROR. Everything looks right
and from what I read, it should work. I hate to burn you out on this so if
you give up, I certainly understand. Thanks -Janet

:

I just built the tables and tested the query below - works.

SELECT q_cust.cu_cust_name, order.or_part_desc, RejectQ.jc_mach_code,
order.or_cpu, IIf([jc_mach_code]<140,[or_cpu]/2,[or_cpu]) AS Price,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.mt_mach_desc,
RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;


:

I copied the SQL below. I have had to put
"=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" in the report under the
or_cpu (price) field. WHen I put it in the query on the field line
"cost:=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" I get an error that
states "you tried to execute a query that does not inlcude the specified
expression 'cu_customer_name' as part of an aggregate function".
"cu_customer_name" is just the field with the customer name. Thanks again.

The value comes from when I enter a price each for the product. Normally I
would say I entered in a form, but the tables in this application are linked
into another foxpro program but the values are correct when I run the query
by itself without any IIf function. So if the part cost $30.00 or $100.00 it
shows up fine, but in the expression you had me try, it is adding up all the
values.

example:
Date range 08/01/05-08/05/05
customer A price $30.00 each and the return on the expression becomes
$380.00
customer B price $200.00 each (<140) " " " " " $190.00
customer C price $50.00 each " " " " " $380.00
customer D price $100.00 each (<140) " " " " " $190.00

SELECT q_cust.cu_cust_name, order.or_part_desc, order.or_cpu,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.jc_mach_code,
RejectQ.mt_mach_desc, RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;

thanks -