VBA SQL Error - No value given for one or more parameters

[SarahHall]

Hi,

I'm attempting a query using VBA in Excel. Stepping through, I get the error as in the thread subject at '.Open SQL'
I've checked and double checked I have correct spelling and case, etc but I cannot see where the problem is!
I'd be very grateful if anyone could help me identify the problem with the code below.
-Using Excel & Access 2002

    Dim xName As String
    Dim xStart As Date
    Dim xEnd As Date
    xName = Chr(34) & Worksheets("Calcs").Range("c5").Value & Chr(34)
xStart = Format(Worksheets("Calcs").Range("c2").Value, "mm/dd/yyyy")
xEnd = Format(Worksheets("Calcs").Range("c3").Value, "mm/dd/yyyy")

sql1 = "SELECT qryTotalDuration.CSTRName, qryTotalDuration.Team, qryTotalDuration.Dept, qryTotalDuration.stepstopdate, qryTotalDuration.ProcessStepName, qryTotalDuration.duration "
    
    sql2 = "FROM qryTotalDuration "
    
    'Dept SQL3
    If Worksheets("Calcs").Range("g19").Value = "Y" Then
        sql3 = _
            "WHERE (((qryTotalDuration.Dept)=" & _
            xName & _
            ") AND ((qryTotalDuration.stepstopdate) " & _
            "Between #" & xStart & "# And #" & xEnd & "#)) "
    End If
    'Team SQL3
    If Worksheets("Calcs").Range("g20").Value = "Y" Then
        sql3 = _
            "WHERE (((qryTotalDuration.Team)=" & _
            xName & _
            ") AND ((qryTotalDuration.stepstopdate) " & _
            "Between #" & xStart & "# And #" & xEnd & "#)) "
    End If
    'CSR SQL3
    If Worksheets("Calcs").Range("g21").Value = "Y" Then
        sql3 = _
            "WHERE (((qryTotalDuration.CSTRName)=" & _
            xName & _
            ") AND ((qryTotalDuration.stepstopdate) " & _
            "Between #" & xStart & "# And #" & xEnd & "#)) "
    End If
    sql4 = ";"
    
    sql = sql1 & sql2 & sql3 & sql4
    
    
    With rsPubs
        .ActiveConnection = vConnection
        .Open sql
        
        Worksheets("ActualTime").Range("a2:g65536").Clear
        Worksheets("ActualTime").Range("a2").CopyFromRecordset rsPubs
        .Close

[nico5038]

I do miss DIM statements for the sql1, sql2, etc. variables and finally "sql" is a reserved word (Check: http://support.microsoft.com/kb/286335)

Try to add Dim statements and rename the "sql" into e.g. "strSQL"

Nic;o)

[NeoPa]

Sarah,

I list here the items we expect members to cover before posting code (If they don't know then we tell them). Notice the first item. I would strongly recommend this for code whether posting here or not. This would have saved you some effort. All these items help us, but in as much as they make it easier for us to help you, they help you too. Often following these steps means you won't even need to ask the question.

1. Ensure you have Option Explicit set (See Require Variable Declaration).
2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
3. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
4. Ensure that the code in your post is enveloped within CODE tags. The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.

Welcome to Bytes!

[Stewart Ross]

Hi Sarah. Your string comparison in the Where clause in lines 31 to 33 is missing its enclosing quotes. The SQL interpreter will try to interpret the string value as some kind of field name instead of a string literal, which I reckon is the source of your problem. Revised version:

          "WHERE (((qryTotalDuration.CSTRName)= '" & _ 
            xName & _ 
            "') AND ((qryTotalDuration.stepstopdate) " & _

Ahh, I note that you are using Chr(34) (double-quotes) on either side of your xname string in line 4 - I don't think this will work for you, as when you concatenate the Where components for sql3 there will be two double-quotes in the string one after the other; but I could be wrong. I'd just remove the Chr(34)'s and use the single apostrophe on either side as I show above.

I'd echo NeoPa's advice about the steps you need to take in debugging before posting, however!

-Stewart