Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: SQL 2-field parameter

    I have a union query that prints to a report, made up of 2 select queries. I am trying to find the correct syntax to put in my select queries to
    limit what prints to the report, without deleting data.

    What I need to do is limit all records that equal "RED" and "YELLOW" and "BLUE" in the status column, and is less than "1/1/00" in the date column. Other status' that are less than 1/1/00 will still print to the report.....

    I'm not sure the structure, or if it can be done in SQL...........

    Any help is greatly appreciated!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use a WHERE clause:
    WHERE Status IN ( 'Red', 'Yellow', 'Blue' ) AND [Date] < #1/1/00#
    Have a nice day!

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    Quote Originally Posted by Sinndho View Post
    Use a WHERE clause:
    WHERE Status IN ( 'Red', 'Yellow', 'Blue' ) AND [Date] < #1/1/00#
    Thanks alot - but I thought this would pull the info, not omit it?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    To omit just reverse the operators:
    WHERE Status NOT IN ( 'Red', 'Yellow', 'Blue' ) AND [Date] >= #1/1/00#
    Have a nice day!

  5. #5
    Join Date
    Jun 2010
    Posts
    186
    thank you for your help.......when I tried this, I realized an error in my first post for this......

    First - What I need is to query number of days field calculated from the date =Date()-[Date], so I can parameter less then 3,650 days instead of 1/1/00.

    Second - I want parameter all of the Red status, then see if they are less than 3,650 days. If they are more than 3,650 days - do not return.

    The problem I have writing in SQL - is there are other status values that need to be returned, that doesn't matter the days. All I know is If, then in VBA which I'm so new with that I still struggle through it.........so I'm tempted to VBA the report (skip the query parameters). What do you think?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can specify other fields as well:
    Code:
    SELECT <Field1>, <Field5>, <Field2>, <FieldX>, ... 
    FROM <MyTable_or_Query>
    WHERE <FieldX> NOT IN ( 'Foo', 'Blah', 'uh' ) AND <Field1> > 0;
    To know the number of matching rows you can use:
    Code:
    SELECT SUM (<FieldX> )
    FROM <MyTable_or_Query>
    WHERE <FieldX> NOT IN ( 'Foo', 'Blah', 'uh' ) AND <Field1> > 0;
    In VBA you can use the DSUM() function:
    Code:
    Total = DSUM("<FieldX> ", "<MyTable_or_Query>", "<FieldX> NOT IN ( 'Foo', 'Blah', 'uh' ) AND <Field1> > 0")
    Have a nice day!

Posting Permissions

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