I have a table with 4 required quantities (ReqQuan1 to 4), 4 required 
dates (ReqDate1 to 4) and an oustanding quantity field.  I need an extra 
field (called CurrentDate) that isn't in the table that calculates which 
of the required dates is the current one based on the oustanding 
quantity.  I am trying to do this in a query for a report so that the 
report can be ordered by the current date. 
The calculations i need to do are:

If ReqQuan4 <= QuantityOutstanding then
    CurrentDate = ReqDate4
else
if ReqQuan3 <= (QuantityOutstanding - ReqQuan4) then
    CurrentDate = ReqDate3
else
if ReqQuan2 <= (QuantityOutstanding - ReqQuan4 - ReqQaun3) then
    CurrentDate = ReqDate2
else
    CurrentDate = ReqDate1
end if

I need  to know how i can get the query to calculate this field or how i 
can get the report doing it and still ordering the report by the 
CurrentDate field.

Any help much appriciated.
Andrew

To convert  your requirement into a column in the query:

If ReqQuan4 <= QuantityOutstanding then
    CurrentDate = ReqDate4
else
if ReqQuan3 <= (QuantityOutstanding - ReqQuan4) then
    CurrentDate = ReqDate3
else
if ReqQuan2 <= (QuantityOutstanding - ReqQuan4 - ReqQaun3) then
    CurrentDate = ReqDate2
else
    CurrentDate = ReqDate1
end if

   Add a blank column in the query grid and right-click to BUILD the
   formula
   Give the column an alias (a working name) AVOID CURRENTDATE - this is
   much too close to the CURRENT DATE constant in Access - lets call it
   DATE_TO_USE - the alias name is entered as Date_To_Use: (the colon
   denotes that is an alias)
   Use the IIF function as follows:

     Date_To_Use:   iif(ReqQuan4 <= QuantityOutstanding, ReqDate4,
               iif(ReqQuan3 <= (QuantityOutstanding - ReqQuan4), ReqDate3,
               iif(ReqQuan2 <= (QuantityOutstanding - ReqQuan4 - ReqQuan3),
ReqDate2, ReqDate1)))

Alternatively, take your code and make a function in a general module then
call your function, passing the variables and returning the date

Public Function WorkOutTheDate(pReqQ1 as double, pReqQ2 as double, pReqQ3
as double, pReqQ4 as double, pQOs as double, pReqD1 as date,  pReqD2 as
date, pReqD3 as date,  pReqD4 as date) as date

If pReqQ4 <= pQOs then
    WorkOutTheDate = pReqD4
else
if pReqQ3 <= (pQOs - pReqQ4) then
     WorkOutTheDate = pReqD3
else
if pReqQ2 <= (pQOs - pReqQ4 - pReqQ3) then
     WorkOutTheDate = pReqD2
else
     WorkOutTheDate = pReqD1
end if


End Function



Then call the function from the query as follows:

Date_To_Use: WorkOutTheDate(ReqQuan1, ReqQuan2, ReqQuan3, ReqQuan4,
QuantityOutstanding, ReqDate1, ReqDate2, ReqDate3, ReqDate4)



The choice is yours!


*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

You can use IIf Function.

For example,:

1) Create new query based on table containing ReqQuan 1 to 4 fields
2) Add QuantityOutstanding and ReqQuan 1 to 4 field to query 
3) Add another field and give it appropriate name (I called my MyQuen - see below)
4) Type required criteria (refer to my code below for hints or jusst copy it it and paste it in the qurey.

MyQuan:
IIf([QuantityOutstanding]>=[ReqQuan4],[reg4],IIf([ReqQuan3]<=([QuantityOutstanding]-[ReqQuan4]),[ReqQuan3],IIf([ReqQuan2]<=
([QuantityOutstanding]-[ReqQuan4]-[ReqQaun3]),[ReqQuan2],[ [ReqQuan2])))

Hope it helps...

____________________________________________________

John Fejsa
Systems Analyst/Computer Programmer
Hunter Centre for Health Advancement
Locked Bag 10, WALLSEND NSW 2287
Phone: (02) 4924 6336 Fax: (02) 4924 6209
www.hcha.org.au
____________________________________________________

The doors we open and close each day decide the lives we live
____________________________________________________

CONFIDENTIALITY & PRIVILEGE NOTICE
The information contained in this email message is intended for the named addressee only.  If you are not the intended recipient you
must not copy, distribute, take any action reliant on, or disclose any details of the information in this email to any other person
or organisation.  If you have received this email in error please notify us immediately.

>>> andrewpayne.plastics@b... 28/08/2002 20:18:03 >>>
I have a table with 4 required quantities (ReqQuan1 to 4), 4 required 
dates (ReqDate1 to 4) and an oustanding quantity field.  I need an extra 
field (called CurrentDate) that isn't in the table that calculates which 
of the required dates is the current one based on the oustanding 
quantity.  I am trying to do this in a query for a report so that the 
report can be ordered by the current date. 
The calculations i need to do are:

If ReqQuan4 <= QuantityOutstanding then
    CurrentDate = ReqDate4
else
if ReqQuan3 <= (QuantityOutstanding - ReqQuan4) then
    CurrentDate = ReqDate3
else
if ReqQuan2 <= (QuantityOutstanding - ReqQuan4 - ReqQaun3) then
    CurrentDate = ReqDate2
else
    CurrentDate = ReqDate1
end if

I need  to know how i can get the query to calculate this field or how i 
can get the report doing it and still ordering the report by the 
CurrentDate field.

Any help much appriciated.
Andrew