Execute SQL Query From CommandButton ErrorL Cannot execute a select query

[mrmmickle1]

I have a simple form with two textboxes



FromDate & ToDate

I have linked a macro to a CommandButton to execute the query.... however I can't get it to work. The error says it cannot execute a select query:

 
    If Me.ToDate = Null Or Me.FromDate = Null Then
        MsgBox "Please Enter Date Range", vbCritical, "PCLS- Supply Query"
        'THIS DOES NOT TRIGGER AS EXPECTED EITHER 
        Exit Sub
    Else

     CurrentDb.Execute "SELECT SUBTOTAL.ID, Count(SUBTOTAL.OrderNo) AS OrderCount, Sum(SUBTOTAL.SumOfTotal) AS ProductCost " & _
    "FROM (SELECT INNERQUERY.ID, INNERQUERY.OrderNo, Sum(INNERQUERY.Total) AS SumOfTotal " & _
    "FROM (SELECT dbo_OrderNo.ShipDate, dbo_OrderNoLine.OrderNo, dbo_OrderNo.ID, dbo_OrderNoLine.Qty, dbo_OrderNoLine.UOM, " & _
    "dbo_OrderNoLine.UnitPrice, dbo_OrderNoLine.UnitPrice*dbo_OrderNoLine.Qty AS Total " & _
    "FROM " & _
    "dbo_OrderNo INNER JOIN dbo_OrderNoLine ON dbo_OrderNo.OrderNo = dbo_OrderNoLine.OrderNo WHERE " & _
    "(((dbo_OrderNo.Status)=""Completed"" AND (dbo_OrderNo.ID) <> """" AND (dbo_OrderNo.ID) <> ""Loc000999"" )) AND " & _
    "Format(dbo_OrderNo.ShipDate, ""MM/DD/YYYY"")  >= Format('" & Me.FromDate & "', ""MM/DD/YYYY"") AND " & _
    "Format(dbo_OrderNo.ShipDate, ""MM/DD/YYYY"") <=  Format('" & Me.ToDate & "', ""MM/DD/YYYY"")) " & _
    "AS INNERQUERY GROUP BY INNERQUERY.ID, INNERQUERY.OrderNo)  AS SUBTOTAL " & _
    "GROUP BY SUBTOTAL.ID;"

I know the query is accurate because I have tested it many times. I am also having trouble with checking to make sure a date has been entered

How do I link the query to the button in an acceptable manner? Any help would be greatly appreciated.

[June7]

Cannot use = Null. Cannot compare something to Null.

Review http://allenbrowne.com/casu-12.html

Misplaced apostrophes within Format function.

"dbo_OrderNo.Status)='Completed' AND dbo_OrderNo.ID <> '' AND dbo_OrderNo.ID <> 'Loc000999' AND " & _
"Format(dbo_OrderNo.ShipDate, 'MM/DD/YYYY') BETWEEN '" & Format(Me.FromDate "MM/DD/YYYY") & "' AND '" & Format(Me.ToDate , "MM/DD/YYYY") & "'" & _

Also, SELECT queries cannot be 'executed' - only action SQL statements can be. If you want to open this query in VBA need to open recordset object set to this SQL.

[John_G]

With respect to the error message cannot execute a select query, it means exactly what it says.

When you use Docmd.Execute, the query must be an action query (Insert, Update, Delete) - it cannot be a Select query.

What is it you want to do with the results of the query?

Just a note on your date comparisons -

You are using text-formatted dates for comparisons; this will work as long as all dates are in the same year. When the format is "MM/DD/YYYY", the expression
"12/31/2015" < "01/01/2016" is False.

For comparisons between dates using text format, format them as "YYYY/MM/DD"

[mrmmickle1]

June7,

Thank you so much for the help! I was able to make some progress but I am still encountering an issue with the Execution of the SQL Statement.

I was able to get the if statement to work using the ISNULL() Function.


JohnG,

Thank you for the date suggestion!!

I would simply like to show the query to the user in datasheet view and then close the form (SupplyQuery).

I tried putting the SQL into a Variable SQLString and running the code like this:

Dim rs As Object

Set rs = CurrentDb.OpenRecordset(SQLString)

It looks like the code executed but the query was not visible? I debugged the code and I had no error/issue What am I doing incorrectly? Do I need to make the recordset visible?

I would have also tried using this

Dim rs AS DAO.Recordset

but my work doesn't have the 3.6 DAO object library and it takes an act of congress to get IT to update our VDI Server with the DLL.


Any additional thoughts/Assistance would be much appreciated!'

[June7]

A recordset is never 'visible'. It is an object in VBA that can be manipulated.

If you want to open a visible dataset then open a query object or form or report.

[mrmmickle1]

June7,

Oh I see about the visibility. I had not thought about that.

Since the query changes based on the user supplied date information how would that work? The UserForm Variables FromDate and ToDate are throwing me off. I got it to work if I just type in the dates but I would rather let the user select them if possible.

[June7]

Use a form for user input of criteria. Then query can reference the controls on form as parameters. Or code can construct filter criteria and pass to form or report when opening. The latter is my preferred method.

Users should rarely interact with tables and queries, only forms and reports.

[mrmmickle1]

So I used:

TestQuery:

SELECT SUBTOTAL.ID, Count(SUBTOTAL.OrderNo) AS OrderCount, Sum(SUBTOTAL.SumOfTotal) AS ProductCost
FROM (SELECT INNERQUERY.ID, INNERQUERY.OrderNo, Sum(INNERQUERY.Total) AS SumOfTotal FROM (SELECT dbo_OrderNo.ShipDate, dbo_OrderNoLine.OrderNo, dbo_OrderNo.ID, dbo_OrderNoLine.Qty, dbo_OrderNoLine.UOM, dbo_OrderNoLine.UnitPrice, dbo_OrderNoLine.UnitPrice*dbo_OrderNoLine.Qty AS Total FROM dbo_OrderNo INNER JOIN dbo_OrderNoLine ON dbo_OrderNo.OrderNo = dbo_OrderNoLine.OrderNo WHERE (((dbo_OrderNo.Status)="Completed" AND (dbo_OrderNo.ID) <> "" AND (dbo_OrderNo.ID) <> "Loc000999" )) AND Format(dbo_OrderNo.ShipDate, "YYYY/MM/DD")  >= Format(' & Me.FromDate & ', "YYYY/MM/DD") AND Format(dbo_OrderNo.ShipDate, "YYYY/MM/DD") <=  Format(' & Me.ToDate & ', "YYYY/MM/DD"))  AS INNERQUERY GROUP BY INNERQUERY.ID, INNERQUERY.OrderNo)  AS SUBTOTAL
GROUP BY SUBTOTAL.ID;

Private Sub cmdTask1_1_Click()
    
    If IsNull(Me.ToDate) Or IsNull(Me.FromDate) Then
        MsgBox "Please Enter Date Range!", vbCritical, "PCLS- Supply Query"
        
    Else
    
    DoCmd.OpenQuery "TestQuery", acViewNormal


    End If


End Sub

But it returned no results...

When I use:

SELECT SUBTOTAL.ID, Count(SUBTOTAL.OrderNo) AS OrderCount, Sum(SUBTOTAL.SumOfTotal) AS ProductCost
FROM (SELECT INNERQUERY.ID, INNERQUERY.OrderNo, Sum(INNERQUERY.Total) AS SumOfTotal FROM (SELECT dbo_OrderNo.ShipDate, dbo_OrderNoLine.OrderNo, dbo_OrderNo.ID, dbo_OrderNoLine.Qty, dbo_OrderNoLine.UOM, dbo_OrderNoLine.UnitPrice, dbo_OrderNoLine.UnitPrice*dbo_OrderNoLine.Qty AS Total FROM dbo_OrderNo INNER JOIN dbo_OrderNoLine ON dbo_OrderNo.OrderNo = dbo_OrderNoLine.OrderNo WHERE (((dbo_OrderNo.Status)="Completed" AND (dbo_OrderNo.ID) <> "" AND (dbo_OrderNo.ID) <> "Loc000999" )) AND Format(dbo_OrderNo.ShipDate, "YYYY/MM/DD")  >= Format(' 1/1/2015', "YYYY/MM/DD") AND Format(dbo_OrderNo.ShipDate, "YYYY/MM/DD") <=  Format('1/31/2015', "YYYY/MM/DD"))  AS INNERQUERY GROUP BY INNERQUERY.ID, INNERQUERY.OrderNo)  AS SUBTOTAL
GROUP BY SUBTOTAL.ID;

It returns the expected dataset.

When i tried this (Below) it asked me to enter Parameters... (I also tried me.ToDate etc):

SELECT SUBTOTAL.ID, Count(SUBTOTAL.OrderNo) AS OrderCount, Sum(SUBTOTAL.SumOfTotal) AS ProductCost
FROM (SELECT INNERQUERY.ID, INNERQUERY.OrderNo, Sum(INNERQUERY.Total) AS SumOfTotal FROM (SELECT dbo_OrderNo.ShipDate, dbo_OrderNoLine.OrderNo, dbo_OrderNo.ID, dbo_OrderNoLine.Qty, dbo_OrderNoLine.UOM, dbo_OrderNoLine.UnitPrice, dbo_OrderNoLine.UnitPrice*dbo_OrderNoLine.Qty AS Total FROM dbo_OrderNo INNER JOIN dbo_OrderNoLine ON dbo_OrderNo.OrderNo = dbo_OrderNoLine.OrderNo WHERE (((dbo_OrderNo.Status)="Completed" AND (dbo_OrderNo.ID) <> "" AND (dbo_OrderNo.ID) <> "Loc000999" )) AND Format(dbo_OrderNo.ShipDate, "YYYY/MM/DD")  >= Format(FromDate, "YYYY/MM/DD") AND Format(dbo_OrderNo.ShipDate, "YYYY/MM/DD") <=  Format(ToDate, "YYYY/MM/DD"))  AS INNERQUERY GROUP BY INNERQUERY.ID, INNERQUERY.OrderNo)  AS SUBTOTAL
GROUP BY SUBTOTAL.ID;

What am I doing inaccurately? Unfortunately I don't see the flaw?

Thanks so much for the help. So Close!

[June7]

Here is suggestion from post 2 with minor revision.

"dbo_OrderNo.Status)='Completed' AND dbo_OrderNo.ID <> '' AND dbo_OrderNo.ID <> 'Loc000999' AND " & _
"Format(dbo_OrderNo.ShipDate, 'YYYY/MM/DD/') BETWEEN '" & Format(Me.FromDate "YYYY/MM/DD/") & "' AND '" & Format(Me.ToDate , "YYYY/MM/DD/") & "'" & _

[John_G]

Use a temporary query, i.e. in code create the query, open it, then delete it.

  
  Dim qdf As QueryDef
  Dim SQL as string
  '
  '  The select as you have it should work fine
  '
  SQL = "Select....."    
  Set qdf = CurrentDb.CreateQueryDef("ZTEMP",SQL)
  DoCmd.OpenQuery "ZTEMP"
  CurrentDb.QueryDefs.Delete "ZTEMP"
  Set qdf = Nothing

[mrmmickle1]

Thanks John_G and June7!!! I appreciate all the help.

June7,

I will modify the query to the between SQL statement you used now that I have it working correctly

I was able to get it working:

Option Compare Database
Option Explicit

Private Sub cmdTask1_1_Click()

    Dim SQLString As String
    Dim qdf As Object
    
    If IsNull(Me.ToDate) Or IsNull(Me.FromDate) Then
        MsgBox "Please Enter Date Range!", vbCritical, "PCLS- Supply Query"
        
    Else
    
    SQLString = "SELECT SUBTOTAL.ID, Count(SUBTOTAL.OrderNo) AS OrderCount, Sum(SUBTOTAL.SumOfTotal) AS ProductCost " & _
    "FROM (SELECT INNERQUERY.ID, INNERQUERY.OrderNo, Sum(INNERQUERY.Total) AS SumOfTotal " & _
    "FROM (SELECT dbo_OrderNo.ShipDate, dbo_OrderNoLine.OrderNo, dbo_OrderNo.ID, dbo_OrderNoLine.Qty, dbo_OrderNoLine.UOM, dbo_OrderNoLine.UnitPrice, dbo_OrderNoLine.UnitPrice*dbo_OrderNoLine.Qty AS Total " & _
    "FROM dbo_OrderNo INNER JOIN dbo_OrderNoLine ON dbo_OrderNo.OrderNo = dbo_OrderNoLine.OrderNo WHERE (((dbo_OrderNo.Status)=""Completed"" AND " & _
    "(dbo_OrderNo.ID) <> """" AND (dbo_OrderNo.ID) <> ""Loc000999"" )) AND Format(dbo_OrderNo.ShipDate, ""YYYY/MM/DD"")  >= Format('" & Me.FromDate & "', ""YYYY/MM/DD"") AND " & _
    "Format(dbo_OrderNo.ShipDate, ""YYYY/MM/DD"") <=  Format('" & Me.ToDate & "', ""YYYY/MM/DD""))  AS INNERQUERY GROUP BY INNERQUERY.ID, INNERQUERY.OrderNo)  AS SUBTOTAL " & _
    "GROUP BY SUBTOTAL.ID;"

    Set qdf = CurrentDb.CreateQueryDef("ZTEMP", SQLString)
    DoCmd.OpenQuery "ZTEMP"
    CurrentDb.QueryDefs.Delete "ZTEMP"
    Set qdf = Nothing
    DoCmd.Close acForm, "SupplyQuery"
    End If

End Sub