By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Expand|Select|Wrap|Line Numbers
  1.     Dim xName As String
  2.     Dim xStart As Date
  3.     Dim xEnd As Date
  4.     xName = Chr(34) & Worksheets("Calcs").Range("c5").Value & Chr(34)
  5. xStart = Format(Worksheets("Calcs").Range("c2").Value, "mm/dd/yyyy")
  6. xEnd = Format(Worksheets("Calcs").Range("c3").Value, "mm/dd/yyyy")
  7.  
  8. sql1 = "SELECT qryTotalDuration.CSTRName, qryTotalDuration.Team, qryTotalDuration.Dept, qryTotalDuration.stepstopdate, qryTotalDuration.ProcessStepName, qryTotalDuration.duration "
  9.  
  10.     sql2 = "FROM qryTotalDuration "
  11.  
  12.     'Dept SQL3
  13.     If Worksheets("Calcs").Range("g19").Value = "Y" Then
  14.         sql3 = _
  15.             "WHERE (((qryTotalDuration.Dept)=" & _
  16.             xName & _
  17.             ") AND ((qryTotalDuration.stepstopdate) " & _
  18.             "Between #" & xStart & "# And #" & xEnd & "#)) "
  19.     End If
  20.     'Team SQL3
  21.     If Worksheets("Calcs").Range("g20").Value = "Y" Then
  22.         sql3 = _
  23.             "WHERE (((qryTotalDuration.Team)=" & _
  24.             xName & _
  25.             ") AND ((qryTotalDuration.stepstopdate) " & _
  26.             "Between #" & xStart & "# And #" & xEnd & "#)) "
  27.     End If
  28.     'CSR SQL3
  29.     If Worksheets("Calcs").Range("g21").Value = "Y" Then
  30.         sql3 = _
  31.             "WHERE (((qryTotalDuration.CSTRName)=" & _
  32.             xName & _
  33.             ") AND ((qryTotalDuration.stepstopdate) " & _
  34.             "Between #" & xStart & "# And #" & xEnd & "#)) "
  35.     End If
  36.     sql4 = ";"
  37.  
  38.     sql = sql1 & sql2 & sql3 & sql4
  39.  
  40.  
  41.     With rsPubs
  42.         .ActiveConnection = vConnection
  43.         .Open sql
  44.  
  45.         Worksheets("ActualTime").Range("a2:g65536").Clear
  46.         Worksheets("ActualTime").Range("a2").CopyFromRecordset rsPubs
  47.         .Close
Jan 24 '10 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Jan 24 '10 #2

NeoPa
Expert Mod 15k+
P: 31,494
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!
Jan 24 '10 #3

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1.           "WHERE (((qryTotalDuration.CSTRName)= '" & _ 
  2.             xName & _ 
  3.             "') 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
Jan 26 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.