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;