Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Better way to write this SQL?

Discussion in 'Information Technology' started by Sarah Schreffler, Jul 28, 2006.

Tags:
  1. Sarah Schreffler

    Sarah Schreffler
    Expand Collapse
    Guest

    Access automatically writes this SQL when I make the query view look
    like what I want:
    INSERT INTO trptDeliverable ( lnkDeliverableID, dtmReportRun, txtField
    )
    SELECT tblDeliverable.DeliverableID, #6/21/2006 16:18:34# AS
    DateReportRan, 'QC' AS Expr2
    FROM tblDeliverable
    WHERE (((tblDeliverable.QCcompleteDate) Is Null) AND
    ((tblDeliverable.DateDeliverableAdded)>#6/30/2006 10:4:31#) AND
    ((tblDeliverable.QCreviewDate)<=#6/30/2006#)) OR
    (((tblDeliverable.QCcompleteDate) Is Null) AND
    ((tblDeliverable.DateDeliverableAdded)>#6/30/2006 10:4:31#) AND
    ((tblDeliverable.QCreviewDate) Between #6/30/2006 10:4:31# And
    #6/30/2006 16:20:33#));


    WHERE clauses referred to:
    Criteria 1 = ((tblDeliverable.QCcompleteDate) Is Null)
    Criteria 2 = ((tblDeliverable.DateDeliverableAdded)>#6/30/2006
    10:4:31#)
    Criteria 3 = ((tblDeliverable.QCreviewDate)<=#6/30/2006#))
    Criteria 4 = ((tblDeliverable.QCreviewDate) Between #6/30/2006 10:4:31#
    And #6/30/2006 16:20:33#)

    What I want, conceptually speaking is WHERE (Criteria 1 and Criteria 2
    and (Criteria 3 or Criteria 4)) -- But when I put those parenthese in
    that way, it doesn't isolate the Criteria out the way I want.

    Is there any way to write this SQL statement where I don't have to
    write it
    WHERE (Criteria 1 and Criteria 2 and Criteria 3 or Criteria 1 and
    Criteria 2 and Criteria 4)?

    This is a SQL statement being created in code and I'd rather it be as
    easy to read as possible. But Access does not seem to be reading the
    parenthesse as I would expect.

    Thanks

    --Sarah Schreffler
     
  2. Loading...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    WHERE ((tblDeliverable.QCcompleteDate Is Null) AND
    (tblDeliverable.DateDeliverableAdded>#6/30/2006 10:4:31#) AND
    ((tblDeliverable.QCreviewDate<=#6/30/2006#) OR
    (tblDeliverable.QCreviewDate Between #6/30/2006 10:4:31# And
    #6/30/2006 16:20:33#)))

    or, more simply,

    WHERE (tblDeliverable.QCcompleteDate Is Null AND
    tblDeliverable.DateDeliverableAdded>#6/30/2006 10:4:31# AND
    (tblDeliverable.QCreviewDate<=#6/30/2006# OR
    tblDeliverable.QCreviewDate Between #6/30/2006 10:4:31# And
    #6/30/2006 16:20:33#)



    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Sarah Schreffler" <sarah.schreffler@gmail.com> wrote in message
    news:1150932740.059503.134990@g10g2000cwb.googlegroups.com...
    > Access automatically writes this SQL when I make the query view look
    > like what I want:
    > INSERT INTO trptDeliverable ( lnkDeliverableID, dtmReportRun, txtField
    > )
    > SELECT tblDeliverable.DeliverableID, #6/21/2006 16:18:34# AS
    > DateReportRan, 'QC' AS Expr2
    > FROM tblDeliverable
    > WHERE (((tblDeliverable.QCcompleteDate) Is Null) AND
    > ((tblDeliverable.DateDeliverableAdded)>#6/30/2006 10:4:31#) AND
    > ((tblDeliverable.QCreviewDate)<=#6/30/2006#)) OR
    > (((tblDeliverable.QCcompleteDate) Is Null) AND
    > ((tblDeliverable.DateDeliverableAdded)>#6/30/2006 10:4:31#) AND
    > ((tblDeliverable.QCreviewDate) Between #6/30/2006 10:4:31# And
    > #6/30/2006 16:20:33#));
    >
    >
    > WHERE clauses referred to:
    > Criteria 1 = ((tblDeliverable.QCcompleteDate) Is Null)
    > Criteria 2 = ((tblDeliverable.DateDeliverableAdded)>#6/30/2006
    > 10:4:31#)
    > Criteria 3 = ((tblDeliverable.QCreviewDate)<=#6/30/2006#))
    > Criteria 4 = ((tblDeliverable.QCreviewDate) Between #6/30/2006 10:4:31#
    > And #6/30/2006 16:20:33#)
    >
    > What I want, conceptually speaking is WHERE (Criteria 1 and Criteria 2
    > and (Criteria 3 or Criteria 4)) -- But when I put those parenthese in
    > that way, it doesn't isolate the Criteria out the way I want.
    >
    > Is there any way to write this SQL statement where I don't have to
    > write it
    > WHERE (Criteria 1 and Criteria 2 and Criteria 3 or Criteria 1 and
    > Criteria 2 and Criteria 4)?
    >
    > This is a SQL statement being created in code and I'd rather it be as
    > easy to read as possible. But Access does not seem to be reading the
    > parenthesse as I would expect.
    >
    > Thanks
    >
    > --Sarah Schreffler
    >
     

Share This Page