Multiple IF Statement VBA Date Range - syntax error

Question

I have VBA to run a query that connects to a form with a date range. I'm trying to write an IF statement that incorporates two text boxes for Start Date and End Date. I'm getting a syntax error on my below IF statement can you help? :

Option Compare Database
Function ImportPurchases()

Dim qdfPassThrough As DAO.QueryDef
Dim MyDB As Database
Dim strConnect As String

Set db = CurrentDb()
DoCmd.SetWarnings False
Set qdfPassThrough = db.QueryDefs("Netezza_abc_purch_track")

If Not IsNull((Forms![DateRange].StartDate)) And Not IsNull((Forms![DateRange].EndDate)) Then
    strSQL = "SELECT A.STORE_NBR,A.ITEM_NBR, A.NDC_NBR, C.BUSINESS_UNIT_NBR, C.INV_CUST_NAME ,C.BUSINESS_UNIT_NAME, Sum(SHIPPED_QTY)as Allocated_Qty, Sum(INV_LINE_AMT) as Extended_Cost FROM FCT_DLY_INVOICE_DETAIL A, FCT_DLY_INVOICE_HEADER B, DIM_INVOICE_CUSTOMER C WHERE A.INV_HDR_SK = B.INV_HDR_SK AND B.DIM_INV_CUST_SK = C.DIM_INV_CUST_SK AND A.STORE_NBR=B.STORE_NBR AND A.INV_DT BETWEEN (" & Forms![DateRange].StartDate & ")and (" & Forms![DateRange].EndDate & ")AND A.SUPPLIER_NBR NOT IN ('50000181', '20000775', '50000809', '50000950') AND A.SRC_SYS_CD = 'ABC' AND C.INV_CUST_NAME NOT LIKE '%340B%' AND C.BUSINESS_UNIT_NAME NOT LIKE '%340B%' Group by 1,2,3,4,5,6"
Else
    strSQL = "ERROR"
End If
qdfPassThrough.SQL = strSQL
strConnect = "ODBC;DSN=NZSQL;Database=PRD_EDW_RXPURCHASING_DB;Server"
qdfPassThrough.Connect = strConnect
qdfPassThrough.Close


DoCmd.SetWarnings True
End Function

Answer 1

The If keyword cannot be used twice in the same statement. Remove the second one.

If Not IsNull(...) And Not IsNull(...) Then
    strSQL = ...
Else
    strSQL = ...
End If

(What is the precise error message?)

(I wonder why your VBA has missing spaces? The editor cleans all that up, or throws dialog boxes. Just curious on my part, I guess.)

---

Your version of the code as of 9:00 AM has this comparison clause:

isnull  >= (Forms![DateRange].StartDate)

I think you want something like this:

IsNull((Forms![DateRange].StartDate))

As a general advice, I suggest you make some minimal working bits of code using a testing or trial mindset, learn the building blocks, and then work up to your actual need.

IMPORTANT

Type Option Explicit at the top of every module. Then use Debug > Compile Database. This will reveal critical errors in your code and probably lead to you resolving your syntax error.

Answer 2

Most likely you (also) need to format proper string expressions for your dates:

... (A.INV_DT Between '" & Format(Forms![DateRange]!StartDate, "yyyy\/mm\/dd") & "' And '" & Format(Forms![DateRange]!EndDate, "yyyy\/mm\/dd") & "') And A.SUPPLIER_NBR ...

Answer 3

I ended up with this:

Option Explicit Private Sub Command9_Click() 'JB Function ImportPurchases() updated this to private sub, Function is for modules, and updated the name to reflect the button name plus _click() Dim db As DAO.Database Dim qdf As QueryDef

Dim qdfPassThrough As DAO.QueryDef
Dim MyDB As Database
Dim strConnect As String

'JB Dim SQLstr As String, you had this reversed in the sql :)

Dim strSQL As String
Dim DateStart As Date
Dim DateEnd As Date
Dim DateStart_string As String
Dim DateEnd_string As String



    'Set db and querydef variables
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Netezza_abc_purch_track")

'Initialize variables with values from the form
      DateStart = Forms!DateRange!DateStart.Value
    DateStart_string = Format(DateStart, "yyyy-mm-dd")
    DateEnd = Forms!DateRange!DateEnd.Value
    DateEnd_string = Format(DateEnd, "yyyy-mm-dd")



    strSQL = "SELECT A.STORE_NBR,A.ITEM_NBR, A.NDC_NBR, C.BUSINESS_UNIT_NBR, C.INV_CUST_NAME ,C.BUSINESS_UNIT_NAME, Sum(SHIPPED_QTY)as Allocated_Qty, Sum(INV_LINE_AMT) as Extended_Cost " & _
    "FROM FCT_DLY_INVOICE_DETAIL A, FCT_DLY_INVOICE_HEADER B, DIM_INVOICE_CUSTOMER C WHERE A.INV_HDR_SK = B.INV_HDR_SK AND B.DIM_INV_CUST_SK = C.DIM_INV_CUST_SK AND A.STORE_NBR=B.STORE_NBR " & _
    "AND A.INV_DT BETWEEN ('" & DateStart_string & "') and ('" & DateEnd_string & "')  AND A.SUPPLIER_NBR NOT IN ('50000181', '20000775', '50000809', '50000950') " & _
    "AND A.SRC_SYS_CD = 'ABC' AND C.INV_CUST_NAME NOT LIKE '%340B%' AND C.BUSINESS_UNIT_NAME NOT LIKE '%340B%' Group by 1,2,3,4,5,6"

   qdf.SQL = strSQL

 Set db = Nothing
    Set qdf = Nothing
    'DoCmd.RunMacro "Macro1"

DoCmd.OpenQuery "Netezza_abc_purch_track"

End Sub