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.)

SQL Query Order of Execution

Boost your SQL expertise with knowledge of the SQL Query Order of Execution. Optimize your database queries with these tips to work efficiently.

www.almabetter.com

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

SQL Query Order of Execution | Sisense

Understanding query order can help you diagnose why a query won't run, and will help you optimize your queries to run faster.

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

 

[Pat Hartman]

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

Hi Stephan,

Dates are stored internally as double precision numbers. The integer portion is the number of days since 12/30/1899 (for Jet/ACE) and I think 1/1/1900 for SQL Server. The time is the decimal and it is the percent of a day that has elapsed. So 12/31/1899 6:00 PM would = 1.75 and
1/1/1900 6:00 AM would = 2.25 Dates prior to 12/30/1899 are negative.

When you format a date field, you convert it to a string and that makes it act like a string for sorting and comparing. So, the bottom line is NEVER, EVER format a date before you use it OR you will have to format it so that the sort/compare will still work. yyyy/mm/dd will compare correctly because year is more important than month and month is more important than day. dd/mm/yyyy and mm/dd/yyyy will give you invalid results. Using just two digits for year can make matters worse if your data can span the century date as it can with DOB.

Then we get to the problem with HAVING and when you should be using WHERE instead. The WHERE is used to exclude/include data before it is aggregated. That means that your entire HAVING should be a WHERE clause. If you built the query using the QBE it "helped" you and assumed you needed a HAVING because you had some Group By's. The WHERE clause will use indexes if they are available. The HAVING cannot.

The HAVING is intended to be used on aggregated data. For example a Sum() or Avg(). It is NEVER used on any column that has not been aggregated. Group By retains the full value so the value that the HAVING is working with is identical to the value the WHERE would have worked with. Since the HAVING is intended to work on aggregated values, there are no indexes available because the criteria is applied to the recordset in memory rather than when retrieving it from the actual table.

Pat

 

[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#

 

[Pat Hartman]

If your form field is bound to a date data type OR the control is unbound but has a date date type specified then --

Where SomeDate = Forms!yourform!yourdate --- works fine.

BUT - If you build the SQL as a string, then you need to deal with formatting and the date needs to be US standard m/d/y or the unambiguous yyyy/mm/dd. If you use two digit years, you get ambiguity in the ymd format so you have to use four digit years with that format.

strWHERE = " WHERE SomeDate = #" & Format(Me.yourdate, "mm/dd/yyyy") & "#"