Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Script for Excel VBA to use a Stored Procedure with an Input Parameter

Posted on 2010-09-20
40
Medium Priority
?
642 Views
Last Modified: 2012-05-10
Pretty much a rookie at this...

I have a store procedure "GlDrillDown", which has a parameter called @EnterYear...

I am trying to create an input from the user that will allow a year to be entered and have the data filtered out according to this input..

Here is my  Code
.. Dont laugh  :(


Private Sub CommandButton1_Click()


' Declare the QueryTable object
Dim qt As QueryTable

' Set up the SQL Statement
sqlstring = "Execute GLDrillDown"

' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
 "ODBC;DSN=SysproCompany1"

' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh
End With


End Sub
0
Comment
Question by:Mikeyman_01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 14
  • 10
  • +2
40 Comments
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33724641
See if this will work:

Private Sub CommandButton1_Click()


' Declare the QueryTable object
Dim qt As QueryTable

' Set up the SQL Statement
sqlstring = "Execute GLDrillDown(" & format(inputbox("Enter Date"),"mm/dd/yyyy") & ")"

' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
 "ODBC;DSN=SysproCompany1"

' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh
End With


End Sub

Open in new window

0
 

Author Comment

by:Mikeyman_01
ID: 33726250
Hi MWGAINES,

The "Execute GLDrillDown(" & format(inputbox("Enter Date"),"mm/dd/yyyy") & ")"

@EnterYear is a integer field.. Does this matter??
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33726281
Oops, was thinking date, not year. ok change it to:
 "Execute GLDrillDown(" & inputbox("Enter Year") & ")"

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33726800
Here is some code I use which calls a stored procedure.

The data comes from a workbook and I'm updating the DB with the entered data.

So, a reverse of what you are doing, but does describe how to call a stored procedure with parameters.

The important bit is the comment.

' Stored procedures are methods of the connection.
Private Sub CommandButton1_Click()
    Dim o_Conn As ADODB.Connection
    Dim i_BlankCount As Integer
    Dim i_CurrentRow As Integer
    Dim s_Updated As String
    
    Application.ScreenUpdating = False
    
    i_BlankCount = 0
    i_CurrentRow = 5
    Set o_Conn = New ADODB.Connection
    s_Updated = Format(Date, "YYYY-MM-DD")
    
    With o_Conn
        .Open "Accounts" ' Assuming that Windows Authentication is now running.
        .DefaultDatabase = "RechargePriceMatrix"
        
        While i_BlankCount < 2
            s_Database = Cells(i_CurrentRow, 1).Value
            s_Contract = Cells(i_CurrentRow, 2).Value
            i_Year = Cells(i_CurrentRow, 3).Value
            i_Period = Cells(i_CurrentRow, 4).Value
            i_Week = Cells(i_CurrentRow, 5).Value
            s_Start = Format(Cells(i_CurrentRow, 6).Value2, "YYYY-MM-DD")
            If s_Database = "" Or s_Contract = "" Then
                i_BlankCount = 1 + i_BlankCount
            Else
                i_BlankCount = 0
                If i_Year >= 2008 And i_Year <= 2020 And _
                    i_Period >= 1 And i_Period <= 13 And _
                    i_Week >= 0 And i_Week <= 5 Then
                    ' Stored procedures are methods of the connection!
                    .sp_SetPeriodOpen s_Database, s_Contract, i_Year, i_Period, i_Week, s_Start, s_Updated
                End If
            End If
            i_CurrentRow = 1 + i_CurrentRow
        Wend

        .Close
    End With

    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:Mikeyman_01
ID: 33728622
Hi RQuadring,

Can i assume that
s_Database
s_Contract
i_Year
i_Period
i_Week
s_Start
are all parameters of the Stored Procedure??  I am a bit confused... but I will attempt to work on it
0
 

Author Comment

by:Mikeyman_01
ID: 33728632
MWGaines,

I did what our suggestion was...It didnt return any data
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33728673
Executing a stored procedure won't return any data.  You would need a select statement for that....
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33729461
Yes they are, sorry. They come from the data entered by the user into the worksheet.

I am essentially passing 1 worksheet row at a time to the database.

It is extremely easy.

My example may not be directly useful to you in this question, but it does mean you do not have to build the query and have to deal with the issues of mistyped data.






@MWGainesJR. I disagree.

A stored procedure may do anything. Including return multiple result sets.


0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33729494
RQualing, Using a Query table isn't going to return the data from the SP, they would need to use selects, to my knowledge, atleast.  He would need to use ADO or DAO to return the resulting tows to a recordset.....
 
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33729964
Ah. I see the distinction. It isn't that SP can't return data, it may just not be read by Excel.

Hmmm.

I'd say that there is no reason why it would be any different.

The QueryTable returns the result set.

The result set is generated by the SP.

Whilst  INSERT, DELETE and UPDATE don't return data (well, not a result set), a SP can and a SELECT does.

I think it'd be fine.
0
 

Author Comment

by:Mikeyman_01
ID: 33730115
Live the Answers but not sure where that leaves me.. Should I re-do my VB script?? or Do I alter it??

I am a rookie obviously
0
 
LVL 34

Expert Comment

by:Norie
ID: 33730946
What does the stored procedure actually do?

If it's nothing too spectacular why not just use it's SQL in your code?
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33732613
That's upto you.

Which do you feel more comfortable with. Which will you still understand in several months time.

Whilst my example was VBA for Excel, the code will translate to straight VB. Getting the parameters is obviously different, but that's not going to impact on the call to the database.

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33733782
The query in excel only executes the code and returns the imediate result.  An SP is simply a call.  The immediate result can't be returned in this way.  
You would need to use DAO and utilize the ReturnRows property of the QueryDef.  

Is your SP a simple Select?  If so, just use the Statement instead of the SP call.  
Or I can help you return rows using the SP by using DAO.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33733954
If the SP returns data, it is no different to SELECT statement.

I've attached a spreadsheet which connects to our of our SQL servers and runs the stored procedure master..sp_databases.

Code included below

As you can see, the SP, creates a temp table, inserts some data into it and returns it to the client.

The spreadsheet is using the following SQL statement to get the data ...

{Call sp_databases(@RETURN_VALUE, @RETURN_VALUE)}


USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_databases]    Script Date: 09/22/2010 13:46:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*	Procedure for pre-7.0 server */
ALTER proc [dbo].[sp_databases]
as
	set nocount on
	/* Use temporary table to sum up database size w/o using group by */
	create table #databases (
				  DATABASE_NAME varchar(32) NOT NULL,
				  size int NOT NULL)

	/* Insert row for each database */
	insert into #databases
		select
			name,
			(select sum(size) from master.dbo.sysusages
				where dbid = d.dbid)
		from master.dbo.sysdatabases d

	select
		 DATABASE_NAME,
		 DATABASE_SIZE = size*2,	/* Convert from 2048 byte pages to K */
		 REMARKS = convert(varchar(254),null)	/* Remarks are NULL */
	from #databases
	order by 1

Open in new window

CallingSP.xls
CallingSP.xml
0
 

Author Comment

by:Mikeyman_01
ID: 33734790
MWGaines,

That would be ideal...I guess I needed clarity on how to use my SP with Parameters and utilize VB in Excel to return Data per entry points in a form of some sort...My lack of knowledge here is pretty disappointing, oh well...Can we try the DAO method..



0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33734859
Can you post the code that is in the SP?
0
 

Author Comment

by:Mikeyman_01
ID: 33735148
USE [SysproCompany1]
GO
/****** Object:  StoredProcedure [dbo].[GLDrillDown]    Script Date: 09/22/2010 07:53:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[GLDrillDown]

      
@EnterYear as int

as



SELECT     Company, GlCode, BeginYearBalance, ClosingBalPer1, ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, ClosingBalPer6,
                      ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS GlPeriod,
                      'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear
FROM         dbo.GenHistory
Where GlYear = @EnterYear



UNION
SELECT     Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4,
                      0 AS ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10,
                      0 AS ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, Source, Journal, SUM(EntryValue) AS EntryValues, GlYear
FROM         dbo.GenTransaction
Where GlYear = @EnterYear
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33735236
That second select should have a GROUP BY clause.

At a guess ...

GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear

0
 
LVL 13

Accepted Solution

by:
MWGainesJR earned 1400 total points
ID: 33735264
No need for a stored procedure:
Private Sub CommandButton1_Click()


' Declare the QueryTable object
Dim qt As QueryTable
Dim EnterYear as long
EnterYear = InputBox("Enter Year")
' Set up the SQL Statement
sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _ & "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _  & "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _  & "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _
& "FROM dbo.GenHistory Where GlYear = " & EnterYear _
& " UNION " _
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _ & "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _ & "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _ & "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _ & "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _ & "dbo.GenTransaction Where GlYear = " & EnterYear

' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
 "ODBC;DSN=SysproCompany1"

' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh
End With


End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33735294
oops,  correcting the returns
Private Sub CommandButton1_Click()  
  
  
' Declare the QueryTable object  
Dim qt As QueryTable  
Dim EnterYear as long  
EnterYear = InputBox("Enter Year")  
' Set up the SQL Statement  
sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _
& "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _
& "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _
& "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _
& "FROM dbo.GenHistory Where GlYear = " & EnterYear _
& " UNION " _
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _
& "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _
& "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _
& "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _
& "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _
& "dbo.GenTransaction Where GlYear = " & EnterYear
  
' Set up the connection string, reference an ODBC connection  
' There are several ways to do this  
' Leave the name and password blank for NT authentication  
connstring = _  
 "ODBC;DSN=SysproCompany1"  
  
' Now implement the connection, run the query, and add  
' the results to the spreadsheet starting at row A1  
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)  
 .Refresh  
End With  
  
  
End Sub

Open in new window

0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33735358
Why no GROUP BY?

Taking a random table ...

SELECT
  agent_start_date,
  SUM(session_id)
FROM
  msdb.dbo.syssessions


results in ...


Msg 8120, Level 16, State 1, Line 1
Column 'msdb.dbo.syssessions.agent_start_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


0
 

Author Comment

by:Mikeyman_01
ID: 33735481
RQuadling,

There was.. I have edited it multiple times due to the fact that it wasnt working in VB...So i figured that I would retype it.. Just forgot the group by.. I realized that after i sent it...

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33735503
Just add the group by into the select statement in the vba code.....
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33735750
OK. Just making sure. Sometimes the most obvious things can be overlooked.
0
 

Author Comment

by:Mikeyman_01
ID: 33736168
MWGaines,

Getting a complie error... (cant see the error but thats what it says).. Included in image


Compile-error.JPG
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33736574
Several problems:
1.  You need a space in front of the first WHERE
2.  You need a space in front of the first GROUP BY
3.  You need space in front of the second WHERE
4.  You need to remove the quote behind the second ENTERYEAR
5.  You need a space in front of the second GROUP BY

sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _  
& "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _  
& "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _  
& "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _  
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _  
& "FROM dbo.GenHistory" _
& " Where GlYear = " & EnterYear _ 
& " GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear" _ 
& " UNION " _  
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _  
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _  
& "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _  
& "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _  
& "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _  
& "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _  
& "dbo.GenTransaction Where GlYear = " & EnterYear _
& " Where GlYear = " & EnterYear _
& " GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear"

Open in new window

0
 

Author Comment

by:Mikeyman_01
ID: 33736914
Get a where error....
Where-error.JPG
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33736975
oops....forgot the single quotes before and after the variable:
 

sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _    
& "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _    
& "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _    
& "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _    
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _    
& "FROM dbo.GenHistory" _  
& " Where GlYear = '" & EnterYear _   
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear" _   
& " UNION " _    
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _    
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _    
& "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _    
& "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _    
& "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _    
& "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _    
& "dbo.GenTransaction Where GlYear = " & EnterYear _  
& " Where GlYear = '" & EnterYear _  
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear"

Open in new window

0
 

Author Comment

by:Mikeyman_01
ID: 33737400
Still the same error...

I adjusted per your notes...
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33737478
post your code again.  I see nothing wrong with the syntax.....
0
 

Author Comment

by:Mikeyman_01
ID: 33737681
Private Sub CommandButton1_Click()
   
' Declare the QueryTable object
Dim qt As QueryTable
Dim EnterYear As Long
EnterYear = InputBox("Enter Year")
' Set up the SQL Statement
sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _
& "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _
& "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _
& "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _
& "FROM dbo.GenHistory" _
& " Where GlYear = '" & EnterYear _
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear" _
& " UNION " _
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _
& "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _
& "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _
& "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _
& "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _
& "dbo.GenTransaction Where GlYear = " & EnterYear _
& " Where GlYear = '" & EnterYear _
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear"




' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
 "ODBC;DSN=SysproCompany1"
 
' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh
End With
 
 
End Sub
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33737738
You've got 2 WHERE clauses ...

& "dbo.GenTransaction Where GlYear = " & EnterYear _
& " Where GlYear = '" & EnterYear _

Change that to ...

& "dbo.GenTransaction" &  _
& " Where GlYear = '" & EnterYear _
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33737758
You have two Where clauses in the second select.....I might have added a second on accident:
 

sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _
& "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _
& "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _
& "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _
& "FROM dbo.GenHistory" _
& " Where GlYear = '" & EnterYear _
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear" _
& " UNION " _
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _
& "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _
& "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _
& "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _
& "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _
& "dbo.GenTransaction" _
& " Where GlYear = '" & EnterYear _
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear"

Open in new window

0
 

Author Comment

by:Mikeyman_01
ID: 33738139
Getting a year error.... Sql sees the data type for the year as Decimal(4,0) should it be something other then Long as the datatype in VB??


Year-error.JPG
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33738195
Whats the data type for the year in sql?

If it's text/varchar, leave the single quotes
If it's not text/varchar, take the single quotes out
I don't think it's a data type issue or else it wouldn't be a syntax error, it would be a mismatch error.
Put the text into an msgbox and stare over it a few minutes....if it's syntax it should be easy to see from there.
0
 
LVL 40

Assisted Solution

by:Richard Quadling
Richard Quadling earned 600 total points
ID: 33738196
& " Where GlYear = '" & EnterYear _
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear"

needs to become ...

& " Where GlYear = " & EnterYear _
& " GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear"


Remove the quotes from around the GLYear test.



Is it possible to display the string before you run it? Just so we can see any errors in it?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33738270
You need a zero in the GROUP BY clause for each field that is zero in the SELECT clause.

18:  " GROUP BY Company, G1Code, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, Source, Journal, G1Year"

0
 

Author Comment

by:Mikeyman_01
ID: 33738390
It works!!! hallelujah (or however you spell it)!!!

0
 

Author Closing Comment

by:Mikeyman_01
ID: 33738427
Both experts were very patient and knowledgeable.. Really appreciate their time and energy
0

Featured Post

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

670 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