Solved Searching between two dates just doesnt seem to work properly

[medihub_guy]

Hi there. My issue is that I have a field with a date/time format. It captures date and time. I have a form set up with 2 unbound fields: "FromDate" and "ToDate". My subform is bound to a query (see SQL query below). When I requery the subform, the dates never seem to work. Please take a look and tell me what you think:


SELECT OrderDetail_V.ReceiptID, IIf([paid_order]=-1,[ReceiptNo],"UNPAID") AS Receipt, IIf([Paid_order]=-1,"PAID","UNPAID") AS OrderStatus, OrderDetail_V.Paid_order, IIf([MaidenName_patient] Is Null Or [MaidenName_patient]="",[LastName_patient] & ", " & [FirstName_patient],[MaidenName_patient] & "-" & [LastName_patient] & ", " & [FirstName_patient]) AS [PATIENT NAME], Format([TimeIn_order],"dd/mm/yy") AS SearchDate, OrderDetail_V.LocationID_orderdetail, OrderDetail_V.Reversed_orderdetail, OrderDetail_V.OnHold_orderdetail, OrderDetail_V.LocationID_receipt, OrderDetail_V.DatePaid_order, OrderDetail_V.StaffID_Cashier, Sum(OrderDetail_V.SelfPay_orderdetail) AS SumOfSelfPay_orderdetail, Sum(OrderDetail_V.Discount_orderdetail) AS SumOfDiscount_orderdetail, Sum(OrderDetail_V.PatientCopay_orderdetail) AS SumOfPatientCopay_orderdetail, Sum(OrderDetail_V.VAT_PatientCoverage_orderdetail) AS SumOfVAT_PatientCoverage_orderdetail, Sum(OrderDetail_V.Linetotal_orderdetail) AS SumOfLinetotal_orderdetail, Sum(IIf([paid_order]=True,[Linetotal_orderdetail]-[Discount_orderdetail],0)) AS PaidTotal, OrderDetail_V.Description_location, OrderDetail_V.StreetAddress_location
FROM OrderDetail_V
GROUP BY OrderDetail_V.ReceiptID, IIf([paid_order]=-1,[ReceiptNo],"UNPAID"), IIf([Paid_order]=-1,"PAID","UNPAID"), OrderDetail_V.Paid_order, IIf([MaidenName_patient] Is Null Or [MaidenName_patient]="",[LastName_patient] & ", " & [FirstName_patient],[MaidenName_patient] & "-" & [LastName_patient] & ", " & [FirstName_patient]), Format([TimeIn_order],"dd/mm/yy"), OrderDetail_V.TimeIn_order, OrderDetail_V.LocationID_orderdetail, OrderDetail_V.Reversed_orderdetail, OrderDetail_V.OnHold_orderdetail, OrderDetail_V.LocationID_receipt, OrderDetail_V.DatePaid_order, OrderDetail_V.StaffID_Cashier, OrderDetail_V.Description_location, OrderDetail_V.StreetAddress_location
HAVING (((IIf([MaidenName_patient] Is Null Or [MaidenName_patient]="",[LastName_patient] & ", " & [FirstName_patient],[MaidenName_patient] & "-" & [LastName_patient] & ", " & [FirstName_patient])) Like "*" & [Forms]![70-General_OrderList_F]![SearchBox] & "*") AND ((OrderDetail_V.TimeIn_order)>=[Forms]![70-General_OrderList_F]![FromDate] And (OrderDetail_V.TimeIn_order)<[Forms]![70-General_OrderList_F]![ToDate]+1) AND ((OrderDetail_V.LocationID_orderdetail)=[Forms]![1-General_Login_F]![LocationID]) AND ((OrderDetail_V.Reversed_orderdetail)=False) AND ((OrderDetail_V.OnHold_orderdetail)=False))
ORDER BY IIf([MaidenName_patient] Is Null Or [MaidenName_patient]="",[LastName_patient] & ", " & [FirstName_patient],[MaidenName_patient] & "-" & [LastName_patient] & ", " & [FirstName_patient]), OrderDetail_V.TimeIn_order;

 

[The_Doc_Man]

To be honest, that SQL will be painful to read in detail.

However, I see that it has no WHERE clause so I doubt it would ever filter anything to show you only entries between two external dates. The WHERE clause to restrict dates based on a specific pair of form controls might be as simple as

...FROM OrderDetail_V WHERE [TimeIn_Order] BETWEEN Forms![Formname]!FromDate AND Forms![Formname]!ToDate ... GROUP BY ...

 

[Gasman]

In the Having Doc, though I agree a nightmare to read.

SELECT orderdetail_v.receiptid,
       Iif([paid_order] =- 1, [receiptno], "unpaid")
       AS Receipt,
       Iif([paid_order] =- 1, "paid", "unpaid")
       AS OrderStatus,
       orderdetail_v.paid_order,
       Iif([maidenname_patient] IS NULL
            OR [maidenname_patient] = "",
       [lastname_patient] & "," & [firstname_patient],
       [maidenname_patient] & "-" & [lastname_patient] & "," &
       [firstname_patient]) AS
       [PATIENT NAME],
       Format([timein_order], "dd/mm/yy")
       AS SearchDate,
       orderdetail_v.locationid_orderdetail,
       orderdetail_v.reversed_orderdetail,
       orderdetail_v.onhold_orderdetail,
       orderdetail_v.locationid_receipt,
       orderdetail_v.datepaid_order,
       orderdetail_v.staffid_cashier,
       SUM(orderdetail_v.selfpay_orderdetail)
       AS SumOfSelfPay_orderdetail,
       SUM(orderdetail_v.discount_orderdetail)
       AS SumOfDiscount_orderdetail,
       SUM(orderdetail_v.patientcopay_orderdetail)
       AS SumOfPatientCopay_orderdetail,
       SUM(orderdetail_v.vat_patientcoverage_orderdetail)
       AS
       SumOfVAT_PatientCoverage_orderdetail,
       SUM(orderdetail_v.linetotal_orderdetail)
       AS SumOfLinetotal_orderdetail,
       SUM(Iif([paid_order] = true,
           [linetotal_orderdetail] - [discount_orderdetail], 0
           ))
       AS PaidTotal,
       orderdetail_v.description_location,
       orderdetail_v.streetaddress_location
FROM   orderdetail_v
GROUP  BY orderdetail_v.receiptid,
          Iif([paid_order] =- 1, [receiptno], "unpaid"),
          Iif([paid_order] =- 1, "paid", "unpaid"),
          orderdetail_v.paid_order,
          Iif([maidenname_patient] IS NULL
               OR [maidenname_patient] = "",
          [lastname_patient] & "," & [firstname_patient],
          [maidenname_patient] & "-" & [lastname_patient] & "," &
          [firstname_patient]),
          Format([timein_order], "dd/mm/yy"),
          orderdetail_v.timein_order,
          orderdetail_v.locationid_orderdetail,
          orderdetail_v.reversed_orderdetail,
          orderdetail_v.onhold_orderdetail,
          orderdetail_v.locationid_receipt,
          orderdetail_v.datepaid_order,
          orderdetail_v.staffid_cashier,
          orderdetail_v.description_location,
          orderdetail_v.streetaddress_location
HAVING ( ( ( Iif([maidenname_patient] IS NULL
                  OR [maidenname_patient] = "", [lastname_patient] & "," &
                                                [firstname_patient],
                          [maidenname_patient] & "-" & [lastname_patient] & ","
                          &
                          [firstname_patient]) ) LIKE
                    "*" & [forms] ! [70-general_orderlist_f] ! [searchbox] & "*"
         )
         AND
(
( orderdetail_v.timein_order ) >= [forms] ! [70-general_orderlist_f] ! [fromdate]
AND ( orderdetail_v.timein_order ) < [forms] ! [70-general_orderlist_f] !  [todate] + 1 )
AND ( ( orderdetail_v.locationid_orderdetail ) =
        [forms] ! [1-general_login_f] ! [locationid] )
AND ( ( orderdetail_v.reversed_orderdetail ) = false )
AND ( ( orderdetail_v.onhold_orderdetail ) = false ) )
ORDER  BY Iif([maidenname_patient] IS NULL
               OR [maidenname_patient] = "", [lastname_patient] & "," &
                                             [firstname_patient],
                    [maidenname_patient] & "-" & [lastname_patient] & "," &
                    [firstname_patient]),
          orderdetail_v.timein_order;

 

[theDBguy]

You should move your having clause to a where clause and try using the CDate() function against your form references to see if it makes a difference.

 

[Gasman]

Are your form controls defined as Date?
TBH I would get the criteria working first with minimal fields, then add the rest when correct.

 

[CJ_London]

Suggest you define your query parameters - as a date or text, number, whatever

see the parameter’s option on the ribbon

 

[The_Doc_Man]

In the Having Doc, though I agree a nightmare to read.

Because the WHERE clause is implemented before the GROUP BY (which is in turn executed before the HAVING clause, in terms of SQL operations), the query will do less work in the GROUP BY and HAVING phases if you have a filtering WHERE clause. (Which would be the case here.)

Order of Execution in SQL

Learn the order of execution in SQL queries for optimal performance. Master the sequence to write more efficient queries and boost your SQL skills now!

www.almabetter.com

https://www.shiksha.com/online-courses/articles/order-of-execution-in-sql/

SQL query order of execution

The SQL order of execution defines the order in which the clauses of a query are evaluated. Understanding query order can help you optimize your queries.

www.sisense.com

The idea is if you implement a filtering WHERE clause before those other clauses get implemented, you have fewer records to drag down query performance in those other clauses.

 

[medihub_guy]

To be honest, that SQL will be painful to read in detail.

However, I see that it has no WHERE clause so I doubt it would ever filter anything to show you only entries between two external dates. The WHERE clause to restrict dates based on a specific pair of form controls might be as simple as

...FROM OrderDetail_V WHERE [TimeIn_Order] BETWEEN Forms![Formname]!FromDate AND Forms![Formname]!ToDate ... GROUP BY ...

Are your form controls defined as Date?
TBH I would get the criteria working first with minimal fields, then add the rest when correct.

You should move your having clause to a where clause and try using the CDate() function against your form references to see if it makes a difference.

The CDate() function did the trick! Thank you so much DBguy!

 

[Gasman]

So they were not dates but strings?

 

[medihub_guy]

So they were not dates but strings?

They were dates. I set the formats for both unbound textboxes to Long Date. I cant explain it.

 

[isladogs]

SQL to/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.

www.isladogs.co.uk

Currently available as a standaone app. The add-in version will be available in a few days

 

[Gasman]

They were dates. I set the formats for both unbound textboxes to Long Date. I cant explain it.

Perhaps use short date?

 

[CJ_London]

Still say define the parameters per post #6.

if you use a string , you have to use the # delimiters so the sql knows that what is between them should be treated as a date

and the date string needs to be in the US format of mm/dd/yyyy or the sql standard of yyyy-mm-dd

 

[Space Cowboy]

This has just work for me in the CRITERIA section of the field

>#01/01/2010# And <#01/01/2025#