none
Using OR and AND operators in SQL statement in VBA RRS feed

  • Question

  • 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,

    Tuesday, September 22, 2015 8:34 PM

Answers

  • You shouldn't enclose the variable names in quotes. Moreover, there shouldn't be an = before Between.

        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


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, September 22, 2015 9:08 PM