Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-21
3
Medium Priority
?
351 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:garfild_1
3 Comments
 
LVL 9

Accepted Solution

by:
Ken Fayal earned 1000 total points
ID: 23700928
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
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 23700986
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
 

Author Comment

by:garfild_1
ID: 23701216
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question