Code:
Dim purchasedate, purchasetime As String
        purchasedate = Date.Today
        purchasetime = Now.ToString("HH:mm:ss")
        Dim purchasetime2 As String = Now.AddSeconds(120).ToString("HH:mm:ss")
        Dim subtotal As String

        'Totals the amount spent by the customer in one purchase
        Dim Str As OleDbCommand = New OleDbCommand
        'Totals the amount spent within the purchasing time period to ensure customer is not entitled to a prize more than once if theier spending is not over £100 
        Str.CommandText = "SELECT SUM(Total_spent) FROM Customer_Order WHERE CustomerID = " & GetcustomerID & " AND Order_Date = #" & purchasedate & "# AND Order_Time >= #" & purchasetime & "# AND Order_Time <= #" & purchasetime2 & "# "
        Str.Connection = cn
        subtotal = CType(Str.ExecuteScalar(), String)
The "SELECT SUM(Total_spent) FROM Customer_Order WHERE CustomerID = " & GetcustomerID & " AND Order_Date = #" & purchasedate & "# AND Order_Time >= #" & purchasetime & "# AND Order_Time <= #" & purchasetime2 & "# " query seems to work in access but not in VB it passes down a null value to my stubtotal variable which is weird because it work fine in access

Any ideas?