I want make a report and filter it based on a form named "frmDeliveryNoteDomain". It has three variable "varStartDate","varEndDate" and "varOrderNumber". All of these variables are Number. Because I use Persian calender
the date format is long integer. All things are good but I cannot use AND and OR operators in SQL statement in this procedure.
My code is as follow:
Private Sub Report_Open(Cancel As Integer)
Dim varStartDate As Variant
Dim varEndDate As Variant
Dim varOrderNumber As Variant
'Open the form for getting information for filter
DoCmd.OpenForm "frmDeliveryNoteDomain", acNormal, , , , acDialog
'get filter information
varStartDate = Nz(Forms("frmDeliveryNoteDomain")!txtStartDate, "")
varEndDate = Nz(Forms("frmDeliveryNoteDomain")!txtEndDate, "")
varOrderNumber = Nz(Forms("frmDeliveryNoteDomain")!cboOrderNumber, "")
'Set the recordsource for the report
If (varOrderNumber > 0) Then
Me.RecordSource = "SELECT tblDeliveryNote.DeliveryID, tblDeliveryNote.DeliveryDate, tblDeliveryNote.WorkOrderID, tblDeliveryNote.BigAmount, tblDeliveryNote.BigUnit, tblDeliveryNote.Description, tblOrder.JobName, tblOrder.JobName, Customers.FinalName " & _
"FROM Customers INNER JOIN (tblDeliveryNote INNER JOIN tblOrder ON tblDeliveryNote.WorkOrderID = tblOrder.WorkOrderID) ON Customers.CustomerID = tblOrder.CustomerID " & _
"WHERE tblDeliveryNote.DeliveryDate = Between " & "varStartDate" & "And & "varEndDate" & " And tblDeliveryNote.WorkOrderID = " & "varOrderNumber" "
Else
Regards,