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

[halex uk]

Hi,

I am new to bytes and I was hoping you could please help me with the Error - No value given for one or more parameters that occurs on the .Open MySql line of code. I m trying to extract data from a query from an access database and output this to Excel. I think there must be something wrong with the string MySql which I have used (I took most of it from the auto-generated sql code in the access query and changed the date between area to use the DateSerial fuction). I may have missed out that I need to code something else with the code...

Any help would be greatly appreciated. thank you
Alex

Private Function CallHandling(ByVal x As String)
    '************************************** CALL HANDLING ******************************
    Dim cn As Object, rs As Object, Status As String
    Dim MySql As String, dbfullname As String, myCnt As Long
 
    dbfullname = "H:\Operations Planning\MIU\Internal\STATS\TELE\New_Call2000.mdb"
    Status = "22"
 
    '*******Q_SAGA HER BY DAY(B2B)*******
    'Stack your SQL string
 
    ' SQL FOR Last 7 days DATES!
 
    MySql = "SELECT [SumOfCalls answered]+[SumOfAbandoned] AS Offered, Sum(T_Rockwell_Application_Data.[Calls answered]) AS [SumOfCalls answered]," _
    & "IIf(Sum(T_Rockwell_Application_data.[calls offered])=0,0,Sum(T_Rockwell_Application_data.[calls offered]-([calls answered]-" _
    & "T_Rockwell_Application_data.[calls answered in 20 secs])-(T_Rockwell_Application_data.abandoned-T_Rockwell_Application_data.[abandoned" _
    & "in 20 secs]))/Sum(T_Rockwell_Application_data.[calls offered])) AS [Svc Level], ([SumOfTotal Talk Time]+[SumOfTotal Work Time])/" _
    & "[SumOfCalls answered] AS AHT, ([SumOfAbandoned]-[SumOfAbandoned in 20 Secs]) AS [Aban calls after 20 secs], Sum(T_Rockwell_Application_" _
    & "Data.Abandoned) AS SumOfAbandoned, Sum(T_Rockwell_Application_Data.[Abandoned in 20 Secs]) AS [SumOfAbandoned in 20 Secs], " _
    & "Sum(T_Rockwell_Application_Data.[Total Talk Time]) AS [SumOfTotal Talk Time], Sum(T_Rockwell_Application_Data.[Total Work Time]) " _
    & "AS [SumOfTotal Work Time], Max(T_Rockwell_Application_Data.Date) AS MaxOfDate, Sum([Abandoned]-[abandoned in 20 secs])/[Offered] " _
    & "AS [20 Sec Abd%]FROM T_Rockwell_Application_Data INNER JOIN [T_HOME ASSIST] ON (T_Rockwell_Application_Data.Application = " _
    & "[T_HOME ASSIST].[Application No]) AND (T_Rockwell_Application_Data.[Application Name] = [T_HOME ASSIST].Name) AND " _
    & "(T_Rockwell_Application_Data.Site = [T_HOME ASSIST].Site)WHERE ((([T_HOME ASSIST].[Sub Category])<>'Home assist engineer') AND " _
    & "((T_Rockwell_Application_Data.Date) Between DateSerial(Year(Date()-7),Month(Date()-7),Day(Date()-7)) And DateSerial(Year(Date()-1),Month(Date()-1),Day(Date()-1))) " _
    & "AND ((T_Rockwell_Application_Data.[Application Group])='HO' Or " _
    & "(T_Rockwell_Application_Data.[Application Group]) Like 'U*') AND (([T_HOME ASSIST].[Application No])<>954 And " _
    & "([T_HOME ASSIST].[Application No])<>57));"
 
    'Clear SQL variable string
    Status = Empty
    Set cn = CreateObject("ADODB.Connection")
 
    'Create DB connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbfullname & ";"
    Set rs = CreateObject("ADODB.Recordset")
    With rs
        Set .ActiveConnection = cn
        'Pass your SQL
        .Source = MySql
        .Open MySql, , 3, 1 - ERROR OCCURS HERE!
 
        myCnt = .RecordCount
        If myCnt > 0 Then
            .MoveLast: .MoveFirst
             'Pull data to first sheet, cells a1:RecordestCountRow & column 3 3 fields in the sql pass
            Windows(mstrHESWB).Activate
            Sheets("CH input").Activate
            Range(Cells(1, 3), Cells(myCnt + 1, 6)).CopyFromRecordset rs
        End If
        .Close
    End With
    cn.Close
    Set rs = Nothing: Set cn = Nothing
 
 
End Function

PS. I think it compiles correctly.

[halex uk]

Hi, I have now figured out the error! However it is not posting any data to the excel sheet in the line:

Range(Cells(1, 3), Cells(myCnt + 1, 6)).CopyFromRecordset rs

I have modified this to be:

Range(Cells(3, 1), Cells(myCnt + 3, 10)).CopyFromRecordset rs

but it still doesn't output anything.

Thank you
Alex

[TheSmileyCoder]

In your VBE window, use the menu to click Debug->Compile. If the Compile is grayed out, it is because the project is allready compiled.

Its a big bit of code to try to work sense out of. If I were you I would start by adding this to line 29:

Debug.Print mySql

Then Copy paste that output from the immediate window, and paste it into a empty query, and try to run it. That should tell you if the SQL is valid or not, and whether that is whats the root cause of your problem.

[halex uk]

Hi thank you - I found the problem with the MySql string.

However it is not outputting the info corectly... Nothing happens when I call the command:

Range(Cells(3, 1), Cells(myCnt + 3, 10)).CopyFromRecordset rs

However I would hope it would output the data to the spreadsheet.

[NeoPa]

Please check out Before Posting (VBA or SQL) Code. One of the more important points has already been raised by Smiley here, but you didn't seem to notice it. I suggest you read the attached set of points carefully, as it will probably effect how likely people are to want to put effort into helping you.

[halex uk]

Hi,

I did read those points before posting. I forgot to mention in the intial quote that it compiled correctly but I did it as the next post (as a p.s). Apologies for missing it originally, I hope this didn't cause too much confusion.

I still have the error when trying to output the data.
Thank you

[NeoPa]

OK. I hear you, and I can't fault your attitude.

However. It appears we are going to have to look at the resultant SQL if we're to be of any help. The linked article indicates that in such situations it makes best sense to get that SQL, not the code that produces it but the SQL itself, format it legibly, and post it here for us to look at. Does that make more sense?

[halex uk]

Hi NeoPa,

Thanks for that. I hope this is what you mean. The actual access sql code is below. However, I have changed the Dates so that it doesn't read from a form but instead reads from the DateSerial command as above because I need the same specific dates always run (i.e. last 7 days). Hope this helps.

Thank you

SELECT [SumOfCalls answered]+[SumOfAbandoned] AS Offered
     , Sum(T_Rockwell_Application_Data.[Calls answered]) AS [SumOfCalls answered]
     , IIf(Sum(T_Rockwell_Application_data.[calls offered])=0,
           0,
           Sum(T_Rockwell_Application_data.[calls offered]-
           ([calls answered]-T_Rockwell_Application_data.[calls answered in 20 secs])-
           (T_Rockwell_Application_data.abandoned-T_Rockwell_Application_data.[abandoned in 20 secs]))/
           Sum(T_Rockwell_Application_data.[calls offered])) AS [Svc Level]
     , ([SumOfTotal Talk Time]+[SumOfTotal Work Time])/[SumOfCalls answered] AS AHT
     , ([SumOfAbandoned]-[SumOfAbandoned in 20 Secs]) AS [Aban calls after 20 secs]
     , Sum(T_Rockwell_Application_Data.Abandoned) AS SumOfAbandoned
     , Sum(T_Rockwell_Application_Data.[Abandoned in 20 Secs]) AS [SumOfAbandoned in 20 Secs]
     , Sum(T_Rockwell_Application_Data.[Total Talk Time]) AS [SumOfTotal Talk Time]
     , Sum(T_Rockwell_Application_Data.[Total Work Time]) AS [SumOfTotal Work Time]
     , Max(T_Rockwell_Application_Data.Date) AS MaxOfDate
     , Sum([Abandoned]-[abandoned in 20 secs])/[Offered] AS [20 Sec Abd%]
 
FROM   T_Rockwell_Application_Data
       INNER JOIN
       [T_HOME ASSIST]
  ON   (T_Rockwell_Application_Data.Site=[T_HOME ASSIST].Site)
 AND   (T_Rockwell_Application_Data.[Application Name]=[T_HOME ASSIST].Name)
 AND   (T_Rockwell_Application_Data.Application=[T_HOME ASSIST].[Application No])
 
WHERE  (([T_HOME ASSIST].[Sub Category]<>'Home assist engineer')
  AND  (T_Rockwell_Application_Data.Date Between Forms!F_CallHandling_Daily!MonthStart And Forms!F_CallHandling_Daily!Current)
  AND  (T_Rockwell_Application_Data.[Application Group]="HO" Or T_Rockwell_Application_Data.[Application Group] Like "U*")
  AND  ([T_HOME ASSIST].[Application No]<>954
  AND  ([T_HOME ASSIST].[Application No]<>57))

[NeoPa]

Halex UK:
However, I have changed the Dates so that it doesn't read from a form but instead reads from the DateSerial command as above because I need the same specific dates always run (i.e. last 7 days).

If this means that your report of the problem was run with SQL different from this then that makes it all but useless for looking at. As it would make no sense to do that though, I have really very little idea what it is you're saying here. I think we need this clarified before attempting to proceed though. We don't want to spend days on something that isn't even the problem you're experiencing.

[Stewart Ross]

In your earlier posts you point out that the CopyFromRecordset method is not having any effect. I note that the line of code you quote does not refer to Excel through an explicit reference to an Excel application object, an approach which will fail when using Excel as an automation server from Access:

Range(Cells(3, 1), Cells(myCnt + 3, 10)).CopyFromRecordset rs

You must explicitly refer to the Excel instance you have defined and opened - which I cannot see anywhere in your code so far.

I would expect to see something like this elsewhere in your code:

Dim oExcel as Excel.Application
Set oExcel = New Excel.Application
oExcel.Workbooks.Open <PathOfYourWorkBook>

If you have something like this in your code then you can use the CopyFromRecordSet method like this:

oExcel.ActiveSheet.Range(Cells(3, 1), Cells(myCnt + 3, 10)).CopyFromRecordset rs

When working in Excel itself it is possible to leave such references out, as the Application object is implied by the code being run within Excel. When running from Access (or anyother non-Excel application) you must explictly refer to the automation server concerned - it cannot be left implied.

This is true of all calls to methods which in one way or another need to be qualified with the application object.

Note the use of the ActiveSheet method in my reference to the Range above; if it is the active worksheet of the Excel workbook you want to use then you must explicitly refer to it in your code when running in another environment like this.

-Stewart

[halex uk]

Hi,

Thank you for the replies.

Neopa - The sql which I have copied into the forum is exactly what I have used. I have just said that I have modified the Access Sql for date to arrive at the code. (substituting the use of a form for a predefined date range).

Stewart - I have attempted using your suggestions The workbook I want to use is the same as the one which has the macro vba code written to it. Will this affect things? It gives me a 1004 runtime error when executing

oExcel.ActiveSheet.Range(Cells(3, 1), Cells(myCnt + 3, 10)).CopyFromRecordset rs 

Do you have any suggestions of why this is? I have just used the path of the currently open workbook so not sure if there is a better way of doing this.

I have never used excel to communicate with an access database and so am very new to this so apolgiese if I am not being very helpful.

Thank you
Alex

[NeoPa]

Alex, I'm thoroughly confused as to why you are even talking about modified SQL (and have very little idea what you're trying to get across even). I would expect one set of SQL and a single description of the problems. I see no reason to include any reference to any other SQL, which makes me wonder what it is that you're talking about. If it's part of the problem, then it really shouldn't be. If it isn't, then it's as relevant to the discussion as my aunt's cat.

I can't work with you if I don't understand what you're trying to say. Does that make sense?

[Stewart Ross]

I confess to having been confused too. What I wrote applies to using Access as the primary source, linking to Excel as an automation server. You are clearly using Excel itself as the source, opening a connection to an Access DB which I had not seen whan I skimmed the code concerned.

My post therefore does not add anything to the discussion, and can be removed accordingly.

-Stewart

[halex uk]

Thanks for your help. I now have managed to reslove the issue now.
Thanks again