Results 1 to 5 of 5
  1. #1
    dbc23 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    11

    Query Criteria Not Working

    So, I'm using an Access 365 instance as a front end to pull data from a SQL database we have on-site. I have quite a few queries built and a few make tables that run every morning to compile the core data I need routinely so I can access it locally without constantly pinging the SQL server (which is used live with an application front end all day).

    The problem I'm having is, some queries when I run them and filter for a data range using "Between... And" it works and the export is perfect. While other queries using the SAME source tables will default to include all dates, and even others text search filters on fields don't get applied.

    I've attached a copy of the query that WORKS, and one that doesn't work.



    Any input is GREATLY appreciated because this is starting to drive me a little insane, given it should be 1:1, i'd think.

    This WORKS:

    Code:
    SELECT DriveDates.DriveDate, DriveInfoSource.DriveStatus, DriveInfoSource.MobileOrCenter, DriveInfoSource.State, DriveInfoSource.DriveName, dbo_DriveListGridView.accountexternalid AS GroupCode, DriveInfoSource.SiteAddress, DriveInfoSource.DriveStart, DriveInfoSource.DriveEnd, DriveGeneralNotes.LastOfText AS MostRecentNote, dbo_DriveMaster.OpenToPublic INTO MIMailing
    FROM (((DriveInfoSource LEFT JOIN dbo_DriveListGridView ON DriveInfoSource.DriveID = dbo_DriveListGridView.driveid) LEFT JOIN DriveGeneralNotes ON DriveInfoSource.DriveID = DriveGeneralNotes.DriveID) LEFT JOIN dbo_DriveMaster ON DriveInfoSource.DriveID = dbo_DriveMaster.DriveID) LEFT JOIN DriveDates ON DriveInfoSource.DriveID = DriveDates.DriveID
    WHERE (((DriveDates.DriveDate) Between (Date()) And (Date()+40)) AND ((DriveInfoSource.DriveStatus)="Confirmed") AND ((DriveInfoSource.MobileOrCenter) Like "Mobile") AND ((DriveInfoSource.State) Like "MI") AND ((dbo_DriveMaster.OpenToPublic)=True))
    ORDER BY DriveDates.DriveDate, Format([DriveInfoSource]![DriveDate],"Long Date",1,1);
    This DOESN'T WORK (date field):
    Code:
    SELECT DriveDates.DriveDate, DriveInfoSource.State, DriveInfoSource.Center, DriveInfoSource.DriveName, DriveInfoSource.DRBCHours, [AlyxAssigned]*[DRBCHours] AS MaxDRBCProcedures, ProceduresPerformed.DRProcedures AS DRBCActualProcedures, ProceduresPerformed.WBProcedures, UnitsCollected.RBCCollected, Nz(Round([UnitsCollected]![RBCCollected]/([ProceduresPerformed]![DRProcedures]+[ProceduresPerformed]![WBProcedures])-1,2),0) AS [%Gain], Round([DRBCActualProcedures]/[MaxDRBCProcedures],2) AS TurnRate, TLs.FirstLead_LeadName AS Lead1, TLs.[ScndLead(IA)] AS Lead2, DriveInfoSource.DriveID, DriveInfoSource.DriveStatus, DriveDates.Year, DriveDates.MonthValue AS [Month], DriveDates.Week, TrueAlyxCount.AlyxAssigned, DriveInfoSource.MobileOrCenter INTO AlyxReportData
    FROM ((((DriveInfoSource LEFT JOIN ProceduresPerformed ON DriveInfoSource.DriveID = ProceduresPerformed.DriveID) LEFT JOIN TLs ON DriveInfoSource.DriveID = TLs.DriveID) LEFT JOIN UnitsCollected ON DriveInfoSource.DriveID = UnitsCollected.DriveID) LEFT JOIN DriveDates ON DriveInfoSource.DriveID = DriveDates.DriveID) LEFT JOIN TrueAlyxCount ON DriveInfoSource.DriveID = TrueAlyxCount.DriveID
    WHERE (((DriveDates.DriveDate) Between (Date()) And (Date()+40)) AND ((DriveInfoSource.DriveStatus)="Confirmed") AND ((DriveInfoSource.MobileOrCenter)="Mobile")) OR (((DriveInfoSource.DriveStatus)="Complete"))
    ORDER BY DriveDates.DriveDate;

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    have you tried (what I use) :
    between Date and DateAdd("d",40,date())

    and are you sure there is data in this range?
    What does NOT WORK mean?

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You've mixed AND & OR in the second, so take out all the extraneous parentheses and add back so you get the desired result.

    (A AND B) OR C
    A AND (B OR C)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    dbc23 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    11
    Quote Originally Posted by ranman256 View Post
    have you tried (what I use) :
    between Date and DateAdd("d",40,date())

    and are you sure there is data in this range?
    What does NOT WORK mean?

    Not work meant it was including additional rows outside the criteria results, and yeah the table has dates from 2014-2022, which was what kept frustrating me since it would seem to work for some criteria but not all.

    The reply after yours SEEMS to be the solution, and I'm suspecting that syntax is the result of me using the QBE grid to build these rather than writing the code. I'm not a SQL native so I'm learning as I go, and access is my training wheels.

    The results I was seeing in the query extract was consistent with the AND/OR mixup though, I just need to figure out how to re-write that now so it selects the correct dates and text fields from their respective columns.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The editor loves to add all the parentheses. In SQL view, take them all out except for the ones for the Date() function, then add them back to achieve your desired logic. When you switch back to design view, you'll see what it does to the grid.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Query Criteria of <=x not working
    By dwheatley in forum Queries
    Replies: 3
    Last Post: 12-05-2017, 08:24 AM
  2. Query Criteria not working
    By SteveApa in forum Queries
    Replies: 6
    Last Post: 01-16-2015, 03:29 PM
  3. Criteria in query is not working
    By sshel55 in forum Queries
    Replies: 7
    Last Post: 10-17-2013, 04:50 PM
  4. query criteria not working
    By George in forum Access
    Replies: 3
    Last Post: 10-30-2012, 12:14 PM
  5. Multiple Criteria in query not working
    By avarusbrightfyre in forum Queries
    Replies: 3
    Last Post: 04-17-2012, 05:06 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