21471217904 (80040e10) Error - No Value Given for one or more....

Hi,
Please find bellow VBA that I use for export data from MS Access database in Excel worksheet.
It works fine every until I try to devide Turnover with number of months. Number of months I use for devide is from My combobox on worksheet loaded with date value also from Access db.
Please find extract of my sql statement:
strSQL = " SELECT [MB],[c], [Average_AV], [Turnover]/['" & Mid(Me.ComboBox2.Value,4 , 2) & "'],[LGT],[NOP] FROM FINAL WHERE DATE= '" & Me.ComboBox2.Value & "' " 
After I introduced this devide issue I receive error as in title. How can I set My SQL statement in order to devide Turnover with number of months and avoid the error at the same time.
Please help...
Kind regards....

Private Sub Start_Command_Click()
Dim col As Integer
Dim row As Integer
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
 
stDB = "Path to my database\db2.mdb"
 
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;" _
& "Data Source=" & stDB & ";"
Range("A7:G888") = " "
Range("C5") = Me.ComboBox2.Value
 
strSQL = " SELECT [MB],[c], [Average_AV], [Turnover]/['" & Mid(Me.ComboBox2.Value,4 , 2) & "'],[LGT],[NOP] FROM FINAL WHERE DATE= '" & Me.ComboBox2.Value & "' " 
rst.Open strSQL, cnn
 
 col = 0
 
 row = 6
  Do While Not rst.EOF
    row = row + 1
    col = 0
    
    Do While col < rst.Fields.Count
      Cells(row, col + 1) = rst.Fields(col).Value
      col = col + 1
    Loop
  
    rst.MoveNext
  Loop
 
Set rst = Nothing: Set cnn = Nothing
End Sub

Open in new window

garfild_1Asked:
Who is Participating?
 
Ken FayalCTOCommented:
The error is specifically pointing at the Me.ComboBox2.Value.  You have to make sure that you have a real date in the combobox.  If you DO actually have a real date in the combobox, then you might want to try changing the strSQL to this:




strSQL = " SELECT [MB],[c], [Average_AV], [Turnover]/['" & Mid(Me.ComboBox2.Value,4 , 2) & "'],[LGT],[NOP] FROM FINAL WHERE DATE= #" & Me.ComboBox2.Value & "# " 

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
DATE is a Reserved Word; you really should use some sort of nameing convention, but if you cannot then enclose that word in square brackets:

WHERE [DATE]=

Can you post an actual SQL string that is built? You can do this immediately before you .Open your recordset:

Debug.Print strSQL

You've got single quotes that don't look like they belong, and a Debug.Print should tell us exactly what is being passed through ADO.
0
 
garfild_1Author Commented:
Hello again,
Thank both of you for replys in such short notice. I reached solution. Please find bellow VBA.
Now it works perfectlly. It seems that bracketing [] caused confusion because it was treated as value from MS Access  database actually doesn't exist.
Anyway than you for your time and effort.
Kind regards....

strSQL = " SELECT [MB],[c], [Average_AV], [Turnover]/" & Mid(Me.ComboBox2.Value,4 , 2) & ",[LGT],[NOP] FROM FINAL WHERE DATE= '" & Me.ComboBox2.Value & "' " 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.