Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2019
    Posts
    4

    Exclamation Issues with WHERE conditions!

    I am working on a reporting series for work, and I am having an issue with pulling the information that I need. I am trying to personalize each report to run with the following criteria:


    By Salesperson
    By Date
    By Status
    Currently, I can have the first two running properly, but I cannot figure out how to run with all three.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you could post your current efforts (queries/SQL/tables...) so readers can better understand your requirements.

  3. #3
    Join Date
    Dec 2019
    Posts
    4
    So sorry, new to this forum. Please see below the issue I am dealing with:

    Private Sub Create_PDF_Click()


    Dim myPath As String
    Dim strReportName As String
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Table of Contents")
    myPath = "C:\Users\firstname.lastname\Documents"
    strReportName = "Sales Report 2019"
    Do While Not rs.EOF
    DoCmd.OpenReport strReportName, acViewPreview, , "[Salesperson] = '" & rs![Salesperson] & "' and [Date] >= #1/01/2018# and [Date] <= #11/30/2019#" & "' if [Status] WHERE= OPEN-Rep Response and [Status] WHERE= OPEN-No Rep Response", acHidden
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, myPath & strReportName & " - " & rs!Salesperson & ".PDF"
    DoCmd.Close acReport, strReportName
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing


    End Sub

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    And this works?
    How abut some sample data and a sample/mockup of what the output should be?

  5. #5
    Join Date
    Dec 2019
    Posts
    4
    This is portion is what I am having an issue with:

    DoCmd.OpenReport strReportName, acViewPreview, , "[Salesperson] = '" & rs![Salesperson] & "' and [Date] >= #1/01/2018# and [Date] <= #11/30/2019#" & "' if [Status] WHERE= OPEN-Rep Response and [Status] WHERE= OPEN-No Rep Response", acHidden

    What is in GREEN text is functional; what's in RED is not functional. I need to format the report to run a report for my each rep by their name, the current date range, and status of each lead. Hopefully this attachment will help.
    Attached Files Attached Files

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ??? Readers will need some test data --mock up something like Porky Pig, Polly Dacktyl,...- and some of your existing "Access related materials", if they are going to help. Only a few records 5-10 that show the range of factors you're dealing with should be sufficient.

    Good luck.

  7. #7
    Join Date
    Dec 2019
    Posts
    4
    Thank you for the guidance!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. conditions
    By kiranair in forum Access
    Replies: 2
    Last Post: 06-01-2016, 04:58 AM
  2. Top 5 per several conditions
    By tmcrouse in forum Queries
    Replies: 4
    Last Post: 03-11-2015, 07:52 AM
  3. iif for 16 conditions
    By harpreett.singhh@gmail.co in forum Access
    Replies: 3
    Last Post: 05-26-2014, 04:50 PM
  4. Sum Column with conditions
    By g8rnc in forum Access
    Replies: 1
    Last Post: 06-10-2010, 09:59 AM
  5. if...then conditions ???
    By em07189 in forum Access
    Replies: 6
    Last Post: 03-05-2010, 10:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums