Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report with multiple criteria (Office 97 SR2)

    I have an exsisting report that is opened from two different places. Depending on where it's opened from, it uses different criteria & has always worked until now as there was only one criteria. I now wish to add another criteria and cannot find an example with more than one criteria.

    Original Code:
    criteria = "companyid<>2965"
    DoCmd.OpenReport stDocName, acPreview, , criteria

    New code ATTEMPT:
    criteria = ("companyid=2965 and TransactionDate >=" & StartDateED & " and <=" & FinishDate)
    DoCmd.OpenReport stDocName, acPreview, , criteria

    I'm trying to set the companyID to 2965 and specify the date range. I know the 1st "and" isn't supposed to go where it is, which is where I need help. An example of criteria with more than 1 set of criteria would be much appreciated.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report with multiple criteria (Office 97 SR2)

    Dates should be specified as #mm/dd/yyyy#, try change your line to this:
    criteria = "companyid=2965 and TransactionDate >=#" & StartDateED & "# and <=#" & FinishDate & "#"
    assuming StartDateED and FinishDate are strings containing dates like 11/14/2001.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report with multiple criteria (Office 97 SR2)

    You need to surround the dates with #, as Bart suggested. However, you also have a syntax problem. You either have to use:

    TransDt>=#firstdate# AND TransDt<=#2ndDate#
    or
    TransDt BETWEEN #firstdate# and #2ndDate#
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report with multiple criteria (Office 97 SR2)

    Thanks for your replies, however:

    I tried
    criteria = ("companyid<>2965 and TransactionDate >= #" & StartDateCS & "# and <=#" & FinishDateCS & "#")
    I also tried
    criteria = ("companyid<>2965 and TransactionDate >= #" & StartDateCS & "# and TransactionDate <=#" & FinishDateCS & "#")
    And
    criteria = ("companyid<>2965 and TransactionDate BETWEEN #" & StartDateCS & "# and #" & FinishDateCS & "#")
    None of the above worked.
    The companyid bit worked but the TransactionDate bit didn't.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report with multiple criteria (Office 97 SR2)

    >>criteria = ("companyid<>2965 and TransactionDate >= #" & StartDateCS & "# and <=#" & FinishDateCS & "#")<<

    You still have a syntax error. If you use >= and <=, you treat them as 2 separate criteria, so you must specify the TransactionDate twice! Example:
    yourDate>=#1/1/01# and yourDate<=#12/31/01#

    You can use Between... And like this, however:
    yourDate BETWEEN #1/1/01# AND #12/1/01#
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report with multiple criteria (Office 97 SR2)

    I still couldn't seem to get that working with the "DoCmd.OpenReport stDocName, acPreview, , criteria" command. I could get = sign working but as soon as I put > sign in or used the BETWEEN function, I got all dates.
    However, I came up with the idea of filling an invisible textbox with the date criteria and got the data source of the report to look at the textbox using:

    >=[Forms]![frmStockManagement]![startdate] And <=[Forms]![frmStockManagement]![finishdate]

    Just for information, I have a section on the left of a form for Company and one on the right for Customers and they both use the same report, which is why I was trying to set the criteria using code.

    Thanks for your help, shame I couldn't work out how to make use of it properly.

Posting Permissions

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