Error - No Value Given for one or more required parameters

[_Matt_]

Hello All,

I am using MS Access 2010 and I have Windows 8. I just started learning VBA so I am a beginner.

I am receiving an error message when I run this code. I actually got the code from another website and modified it to use parameters. I have a form with a date range that runs fine in Access, but I want the user to enter dates and click a command button (which runs the vba code) and exports it to excel.

The code works fine with sSQL = "Select * from Query1 ".

The problem is when I add the parameter.

sSQL = "Select * from Query1 where date1 Between [Forms]![Form2]![Text9] And [Forms]![Form2]![Text21]"

The debugger stops here - objRs.Open sSQL, objConn, adOpenStatic, adLockReadOnly


Any help would be appreciated.

Private Sub Commandf()
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Const sFileNameTemplate As String = "C:\Matt\FileName.xls"


Dim sSQL As String
Set objConn = CurrentProject.Connection
sSQL = "Select * from Query1 where date1 Between [Forms]![Form2]![Text9] And [Forms]![Form2]![Text21]"




With oExcel
.Visible = True
'Create new workbook from the template file
Set WB = .Workbooks.Add(sFileNameTemplate)
With WB
Set WS = WB.Worksheets("Sheet1") 'Replace with the name of actual sheet
With WS
objRs.Open sSQL, objConn, adOpenStatic, adLockReadOnly
Set rng = .Range("A2") 'Starting point of the data range
rng.CopyFromRecordset objRs
objRs.Close
End With
End With
.Quit
End With

Set objConn = Nothing
Set objRs = Nothing
End Sub

[June7]

Do not enclose variables within quotes, otherwise you get the literal name of the variable, not the value of the variable. Reference to form control is a variable. Date values must be delimited with # character, text use ' character, nothing for numbers.

Try:

sSQL = "SELECT * FROM Query1 WHERE date1 BETWEEN #" & [Forms]![Form2]![Text9] & "# AND #" & [Forms]![Form2]![Text21] & "#"

Your query is actually named Query1 and the field is date1? Not very informative naming convention. It's also good idea to give meaningful names to controls on forms and reports, at least those that are referenced in expressions.

[_Matt_]

Hi June,

Thanks for the response. I inserted your sql statement into the code, but I'm still getting the same error message. Any more insight?

[June7]

No, can't see anything else wrong with syntax. Would need to know more about your db. If you want to provided it, follow instructions at bottom of my post. Identify objects involved.

[_Matt_]

Hi June,

Thanks for taking the time to help me. I've updated my db to have more informed information. I've deleted a good bit of the db (due to company confidential information) but the question is still the same. Please let me know if you need any more information.

Thanks again.

[June7]

First thing you need to do is change all the date fields in table to Date/Time type instead of text.

The query is not needed. Code can pull records direct from table.

Need lines to Set the Excel application and recordset variables.

The SQL needs ; punctuation.

All code modules should have these two lines in header:
Option Compare Database
Option Explicit

I don't have your Excel template so can't test with that.

Option Compare Database
Option Explicit

Public Sub CreateExcelInfo()
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Const sFileNameTemplate As String = "C:\Users\MBOBO\Desktop\on-time\Book2.xlsm"
Dim sSQL As String
Set objConn = CurrentProject.Connection
Set objRs = New ADODB.Recordset
Set oExcel = New Excel.Application
sSQL = "SELECT * FROM Yard_TB WHERE [Appointment Date] BETWEEN #" & [Forms]![Main]![Start_Date_MF] & "# AND #" & [Forms]![Main]![End_Date_MF] & "#;"
With oExcel
    .Visible = True
    Set WB = .Workbooks.Add(sFileNameTemplate)
    With WB
        Set WS = WB.Worksheets("Sheet2")
        With WS
            objRs.Open sSQL, objConn, adOpenStatic, adLockReadOnly
            Set rng = .Range("A3") 'Starting point of the data range
            rng.CopyFromRecordset objRs
            objRs.Close
        End With
    End With
    .Quit
End With
Set objConn = Nothing
Set objRs = Nothing
End Sub

[_Matt_]

Hi June,

That worked perfectly. Thank you so much for your help!