medihub_guy
Member
- Local time
- Yesterday, 22:55
- Joined
- Aug 12, 2023
- Messages
- 66
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;
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;