Solved

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

Posted on 2010-09-20
40
594 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
  • 14
  • 14
  • 10
  • +2
40 Comments
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
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
Comment Utility
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
Comment Utility
Oops, was thinking date, not year. ok change it to:
 "Execute GLDrillDown(" & inputbox("Enter Year") & ")"

0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
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
Comment Utility
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
Comment Utility
MWGaines,

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

Expert Comment

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

Expert Comment

by:RQuadling
Comment Utility
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
Comment Utility
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:RQuadling
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:Norie
Comment Utility
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:RQuadling
Comment Utility
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
Comment Utility
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:RQuadling
Comment Utility
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
Comment Utility
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
Comment Utility
Can you post the code that is in the SP?
0
 

Author Comment

by:Mikeyman_01
Comment Utility
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:RQuadling
Comment Utility
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 350 total points
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
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:RQuadling
Comment Utility
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
Comment Utility
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
Comment Utility
Just add the group by into the select statement in the vba code.....
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
OK. Just making sure. Sometimes the most obvious things can be overlooked.
0
 

Author Comment

by:Mikeyman_01
Comment Utility
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
Comment Utility
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
Comment Utility
Get a where error....
Where-error.JPG
0
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
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
Comment Utility
Still the same error...

I adjusted per your notes...
0
 
LVL 13

Expert Comment

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

Author Comment

by:Mikeyman_01
Comment Utility
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:RQuadling
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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:RQuadling
RQuadling earned 150 total points
Comment Utility
& " 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
Comment Utility
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
Comment Utility
It works!!! hallelujah (or however you spell it)!!!

0
 

Author Closing Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now