Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009

    Question Unanswered: Formula Question

    I have a IF statement that appears to work sometimes and other times it doesn't. The formula is supposed to populate a field if the date is missing missing with 1/1/1999 otherwise use the Latest Date at the Merge Center. I've looked the backend query that this query is built upon hoping that it would provide some insight why the formula isn't working and it hasn't.

    Here is the formula:

    Start: IIf([EarliestMCGRDate]![Dummy]=#1/1/1999#,#1/1/1999#,IIf([LatestMCGRDate]![Dummy]<[LatestReqAtSC]![Dummy],[LatestReqAtSC]![Dummy],[LatestMCGRDate]![Dummy]))

    Thanks for your help.
    Last edited by usd02744; 07-09-10 at 18:19.

  2. #2
    Join Date
    Jun 2007
    Maitland NSW,Australia
    Is Dummy a field on a form? If Start is used in the query then form name must have [forms]! before the form name.

    I suggest you post a copy of your database with the relevant form etc.

  3. #3
    Join Date
    Mar 2009


    The database is huge. I posted the SQL that is attached to the query, hopefully it will provide some insight.

    PARAMETERS [Enter start date:] DateTime, [Enter end date:] DateTime;
    SELECT CycleTimeRaw.Customer, CycleTimeRaw.[Sales Order], CycleTimeRaw.Item, CycleTimeRaw.[Sales group], CycleTimeRaw.Net, CycleTimeRaw.ITL, CycleTimeRaw.INVDt, StartDate.Start, CycleTimeRaw.MCGRActDt, CycleTimeRaw.RDDdt, CycleTimeRaw.REQatSCDt, CycleTimeRaw.MCGIactDt, CycleTimeRaw.PODdt, TransitToCustomer.TransitToCustomer, [CycleTimeRaw]![INVDt]-[StartDate]![Start]-[TransitToCustomer]![TransitToCustomer] AS FieldCT, ([CycleTimeRaw]![INVDt]-[StartDate]![Start]-[TransitToCustomer]![TransitToCustomer])*[CycleTimeRaw]![Net] AS FieldCTDolDays
    FROM (CycleTimeRaw INNER JOIN StartDate ON CycleTimeRaw.[Sales Order] = StartDate.[Sales Order]) INNER JOIN TransitToCustomer ON (CycleTimeRaw.Item = TransitToCustomer.Item) AND (CycleTimeRaw.[Sales Order] = TransitToCustomer.[Sales Order])
    WHERE (((CycleTimeRaw.INVDt) Between [Enter start date:] And [Enter end date:]) AND ((CycleTimeRaw.RDDdt)<>#1/1/1999#) AND ((CycleTimeRaw.REQatSCDt)<>#1/1/1999#) AND ((CycleTimeRaw.MCGIactDt)<>#1/1/1999#));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts