VBA Run a simple select query?

[kawi6rr]

I’m not quite sure where I’m going wrong here but this code should just show the results in a query window but when I click the button to run it nothing happens.

Private Sub btn_Classes_by_Date_Click()
Dim strSQL As String


Me.beginDate.Value = beginDate
Me.endDate.Value = endDate


strSQL = "SELECT MRN,Patient_Name,Surgery_Date,Scheduled_For,Enrolled_Date " & _
        "FROM Total_Joint_Readiness " & _
        "WHERE Scheduled_For >= '" & beginDate & "' AND Scheduled_For <= '" & endDate & "' "


DoCmd.RunSQL strSQL


End Sub

any help is appreciated.

[ranman256]

No need to write in code.
build a query then run it with...
docmd.openQuery "qsMyQuery"

make a form and read the dates off the form.

[aytee111]

Dates need to be surrounded by # not single quote.

[kawi6rr]

I'm referencing two from fields where I set value to beginDate and endDate. Thanks for the # I forgot about those and date fields but I'm getting this error when trying to run the code below.

Run-time error ''12342
A RunSQL action requires and argument consisting of an sql statement.

Me.beginDate.Value = beginDate
Me.endDate.Value = endDate


strSQL = "SELECT MRN,Patient_Name,Surgery_Date,Scheduled_For,Enrolled_Date" & _
         "FROM Total_Joint_Readiness" & _
         "WHERE Scheduled_For >= #" & beginDate & "# AND Scheduled_For <= #" & endDate & "# "


DoCmd.RunSQL strSQL

[aytee111]

Try putting spaces at the end of each line (it reads Enrolled_DateFROM).

Before the DoCmd add the statement: Debug.Print strSQL - then copy that to a new query window and you can test it there.

[aytee111]

Using "Between date 1 and date2" is easier to read, instead of less than equal to.

[kawi6rr]

Something else must be going on with this because I made all the changes so the code look like this now.

debug.print strSQL showed an error which I fixed.

strSQL = "SELECT MRN,Patient_Name,Surgery_Date,Scheduled_For,Enrolled_Date " & _
              "FROM Total_Joint_Readiness " & _
              "WHERE Scheduled_For >= #' & beginDate & '# AND Scheduled_For <= #' & endDate & '# "

I'm still getting the same error.
Error = A RunSQL action requires and argument consisting of an sql statement.

[aytee111]

The single quotes should be double quotes - debug.print should be showing you this!

[kawi6rr]

I had it both ways as single quote and double quote and I get the same error. degug.print did help me fix one error but not the current error. The debug.print code runs fine it's the vb code seems not to be recognizing the sql statement.

[kawi6rr]

This should show the sql statement in the query window when it's run correct?

[aytee111]

The debug,print will print the SQL statement as it will run. You then copy the SQL and paste it into a query, then try and run it. Fix any errors you find,

[ssanfu]

You are using

DoCmd.RunSQL strSQL

From HELP about RunSQL:
A string expression that's a valid SQL statement for an action query or a data-definition query. It (an "action query") uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement.

You have a SELECT query and, in code, you are trying to open a record set.
So:

    Dim sSQL As String
    Dim rs As DAO.Recordset
    sSQL = "Select * from tbltest;"
    Set rs = CurrentDb.OpenRecordset(sSQL)
    '
    'more statements
    '
    '
    rs.Close

Note: opening a query in code will not show the results in a query window (AFAIK).

[Micron]

I don't see where you posted that you're trying to open a recordset based on your sql statement. What were you intending to do with it? If you want to display the results, it makes little sense to construct it on the fly when a saved query can reference fields on your form. If you want to validate a returned value or check that there are records that meet the criteria, opening a recordset that you can check is one way. Supplying the sql to a newly opened form or report is another valid reason for doing this. Presenting a datasheet would not be. That being said, you would have to create a temporary (or saved) query def to provide a datasheet, which may not make sense either.

[kawi6rr]

The individual who requested this wants 2 fields where they can input 2 dates that filter the query for them, datepicker, so they only have to click. I originally had it open a saved parameter query but that forced her to type in the dates manually... I won't go into anymore details on that it's a hot spot So I figured I would setup 2 input fields and feed those dates into a query.

[kawi6rr]

Solved: Thanks for everyone who helped me I eventually figured out how to get what I wanted to work but it was quite a bit different approach to what I originally started with.

Private Sub btn_Classes_by_Date_Click()
Dim conn As ADODB.Connection
Dim strSQL As String


DoCmd.SetWarnings False


strSQL = "delete * from Scheduled_For"
DoCmd.RunSQL strSQL
       
strSQL = "INSERT INTO Scheduled_For(MRN,Patient_Name,Surgery_Date,Scheduled_For,Enrolled_Date) " & _
         "SELECT MRN,Patient_Name,Surgery_Date,Scheduled_For,Enrolled_Date " & _
         "FROM Total_Joint_Readiness " & _
         "WHERE Scheduled_For >= #" & Me!beginDate & "# AND Scheduled_For <= #" & Me!endDate & "# ;"
                     
DoCmd.RunSQL strSQL


DoCmd.OpenQuery "Classes_By_Date", , acReadOnly

End Sub