RE: IIf to return a formula or field

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

"KARL DEWEY" wrote:

> Maybe check some of your fields --
>
> jc_mach_code and or_cpu must be number fields.
>
>
> "waterbug" wrote:
>
> > 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
> >
> > "KARL DEWEY" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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 -
> > > >
.

Relevant Pages

  • Re: external DVD drives
    ... On Tue, 07 Nov 2006 14:05:19 GMT, Jaimie Vandenbergh ... know it can / does burn to the built in CDRW through using Bootcamp)? ... Control Panel = Apple, Prefs or Apps, Utils? ...
    (uk.comp.sys.mac)
  • Re: Dampness, Roof Leak in Mobile Home
    ... fire department for a burn and learn, ... caravan, but a modular house. ... it's built like a regular stick built house, ...
    (misc.rural)
  • Re: Doctor Who theme played on the Howard Stern show!
    ... Rome wasn't built in a day. ... Mind you, it only took a day to burn. ...
    (rec.arts.drwho)
  • Re: Sherlock Holmes: The Awakening
    ... purchases with a built in expiry date. ... Can't you burn a DVD of the game after you download it so you ... and you need a new license key. ...
    (comp.sys.ibm.pc.games.adventure)
  • Re: Sherlock Holmes: The Awakening
    ... purchases with a built in expiry date. ... Can't you burn a DVD of the game after you download it so you ... and you need a new license key. ...
    (comp.sys.ibm.pc.games.adventure)