Query not returning all records (1 Viewer)

[Rowey]

Hi everyone - please see attached file Warehouse.accdb

I have a form that calls a query in the subform to filter on a range of records between the "From Date" and the "To Date" fields of the form.

I cant figure out why the query is not returning two records for the 28/02/2019 when the From Date field is 01/02/2019 and the To Date field is 28/02/2019. It only returns those two records if the to date is set to 01/03/2019 or greater.

The syntax in the query has not failed me before.

 

[Ranman256]

THAT is very bizarre. It works if end date is Mar 1, and SHOULD work as 2/28 but it wont.
I stripped the query to just the date field and it still will not show 2/28.
I hardcoded the dates and it still did not work.

I doesn't like 2/28, so you may have to use 3/1. (1st o month to 1st o month)

 

[isladogs]

Its got nothing to do with not liking 28/02/2019 though the solution is indeed to add a day to the selected date in your query criteria.

As written your query will always exclude events occurring on the final day selected as its only including up to midnight at the start of that day e.g. 28/02/2019 00:00:00
This is because your DateSold field includes a time component e.g. 28/02/2019 12:33:54. As that is later than the value selected it is excluded

The easiest solution is probably to change your query to:

SELECT Sales_tbl.DateSold, Sales_tbl.ComboCode, Sales_tbl.Description, Sales_tbl.Contract, Sales_tbl.SiteBuyer, Sales_tbl.Seller, Customer_tbl.Firstname, IIf(Format([customer_tbl].[LastName],">") & " " & [customer_tbl].[firstname] & " " & [customer_tbl].[initial]="","None",Format([LastName],">") & " " & [firstname] & " " & [initial]) AS Nam, Sales_tbl.SaleID, Sales_tbl.InvID, Sales_tbl.PartID, Sales_tbl.StockBefore, Sales_tbl.StockAfter, Sales_tbl.SaleTotal, Sales_tbl.AmountEach, Sales_tbl.NumberSold, Sales_tbl.Paid, Sales_tbl.Condition
FROM Sales_tbl LEFT JOIN Customer_tbl ON Sales_tbl.CustCode = Customer_tbl.CustCode
WHERE (((Sales_tbl.DateSold) [B][COLOR="Red"]Between [forms]![salesfrm]![from] And [forms]![SalesFrm]![to]+1) [/COLOR][/B]AND ((Sales_tbl.ComboCode) Like [forms]![SalesFrm]![CCode] & "*") AND ((Sales_tbl.Contract) Like [forms]![SalesFrm]![Contract] & "*") AND ((Sales_tbl.Seller) Like [forms]![SalesFrm]![Sell] & "*") AND ((IIf(Format([customer_tbl].[LastName],">") & " " & [customer_tbl].[firstname] & " " & [customer_tbl].[initial]="","None",Format([LastName],">") & " " & [firstname] & " " & [initial])) Like [forms]![SalesFrm]![Customer] & "*") AND (([combocode]=[Forms]![salesfrm]![ccode] Or [Forms]![Salesfrm]![ccode] Is Null)=True) AND (([contract]=[Forms]![salesfrm]![contract] Or [Forms]![salesfrm]![contract] Is Null)=True) AND (([sales_tbl].[custcode]=[Forms]![salesfrm]![customer] Or [Forms]![salesfrm]![customer] Is Null)=True) AND (([sitebuyer]=[Forms]![salesfrm]![seller] Or [Forms]![salesfrm]![seller] Is Null)=True))
ORDER BY Sales_tbl.DateSold DESC , Sales_tbl.ComboCode;

HTH

 

[Rowey]

Thanks Isladog - I fixed it by filtering on SaleDate: Format([DateSold],"Short Date") to remove the time component which I needed for the transaction log.

I was surprised because I didn't realise after all these years of using Access that the time component was considered in the query in that way. I would have thought that if the time component was less than midnight on the date component then the date would be that date.:banghead:

 

[isladogs]

Glad you've fixed it.
You could also have removed the time component of the stored date values.

For info, my previous code could also have been written:

SELECT Sales_tbl.DateSold, Sales_tbl.ComboCode, Sales_tbl.Description, Sales_tbl.Contract, Sales_tbl.SiteBuyer, Sales_tbl.Seller, Customer_tbl.Firstname, IIf(Format([customer_tbl].[LastName],">") & " " & [customer_tbl].[firstname] & " " & [customer_tbl].[initial]="","None",Format([LastName],">") & " " & [firstname] & " " & [initial]) AS Nam, Sales_tbl.SaleID, Sales_tbl.InvID, Sales_tbl.PartID, Sales_tbl.StockBefore, Sales_tbl.StockAfter, Sales_tbl.SaleTotal, Sales_tbl.AmountEach, Sales_tbl.NumberSold, Sales_tbl.Paid, Sales_tbl.Condition
FROM Sales_tbl LEFT JOIN Customer_tbl ON Sales_tbl.CustCode = Customer_tbl.CustCode
WHERE (((Sales_tbl.DateSold)[B]>=[forms]![salesfrm]![from] And (Sales_tbl.DateSold)[COLOR="Red"]<[/COLOR]([forms]![SalesFrm]![to][COLOR="red"]+1[/COLOR]))[/B] AND ((Sales_tbl.ComboCode) Like [forms]![SalesFrm]![CCode] & "*") 
AND ((Sales_tbl.Contract) Like [forms]![SalesFrm]![Contract] & "*") AND ((Sales_tbl.Seller) Like [forms]![SalesFrm]![Sell] & "*") AND ((IIf(Format([customer_tbl].[LastName],">") & " " & [customer_tbl].[firstname] & " " & [customer_tbl].[initial]="","None",Format([LastName],">") & " " & [firstname] & " " & [initial])) Like [forms]![SalesFrm]![Customer] & "*") AND (([combocode]=[Forms]![salesfrm]![ccode] Or [Forms]![Salesfrm]![ccode] Is Null)=True) AND (([contract]=[Forms]![salesfrm]![contract] Or [Forms]![salesfrm]![contract] Is Null)=True) AND (([sales_tbl].[custcode]=[Forms]![salesfrm]![customer] Or [Forms]![salesfrm]![customer] Is Null)=True) AND (([sitebuyer]=[Forms]![salesfrm]![seller] Or [Forms]![salesfrm]![seller] Is Null)=True))
ORDER BY Sales_tbl.DateSold DESC , Sales_tbl.ComboCode;

Note the use of '<' rather than '<=' so it doesn't include the following day