DOSYA İndirmek/Yüklemek için ÜCRETLİ ALTIN ÜYELİK Gereklidir!
Altın Üyelik Hakkında Bilgi
Private Sub CommandButton1_Click()
Dim Baglanti As New ADODB.Connection
Dim KayitSeti As New ADODB.Recordset
Dim Firma As String, Server As String, Database As String, Kullanıcı As String, Parola As String
Firma = Format(Sheets("SETUP").Range("B5"), "000")
Server = Sheets("SETUP").Range("B1").Value
Database = Sheets("SETUP").Range("B4").Value
Kullanıcı = Sheets("SETUP").Range("B2").Value
Parola = Sheets("SETUP").Range("B3").Value
sorgu = "SELECT CODE,DEFINITION_ FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD " & "ORDER BY DEFINITION_" & ""
Baglanti.Open "Provider=SQLOLEDB; Data Source=" & Server & "; Initial Catalog=" & Database & "; User ID=" & Kullanıcı & "; Password=" & Parola & ";"
KayitSeti.Open sorgu, Baglanti
Cells(8, 1).CopyFromRecordset KayitSeti
KayitSeti.Close
Baglanti.Close
Set KayitSeti = Nothing
Set Baglanti = Nothing
End Sub
ADODB bağlantısında hata vermesinin sebebi Librarylerin SET edilmemiş olması ile alakalı olabilir...sayın lsuersoy yardımınızı için çok teşekkürler ama
Dim Baglanti As New ADODB.Connection hata veriyor
stok taki ürünlerdede stok miktarı ve satışı gerekiyor
SELECT CLCARD.CODE AS KODU, CLCARD.DEFINITION_ AS ÜNVANI, SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT) AS BAKİYE, CASE WHEN SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT) < 0 THEN SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT) * - 1 ELSE 0 END AS [Alacak Bakiye], CLCARD.ADDR1 AS Adres1, CLCARD.ADDR2 AS Adres2, CLCARD.CITY AS Şehir, CLCARD.COUNTRY AS Ülke, CLCARD.POSTCODE AS PostaKodu, CLCARD.TELNRS1 AS TEL1, CLCARD.TELNRS2 AS TEL2, CLCARD.FAXNR AS FAKS, CLCARD.TAXNR AS Vergi_No, CLCARD.TAXOFFICE AS Vergi_Dairesi, CLCARD.DISCRATE AS iskonto, CLCARD.TOWN AS İLÇE, CLCARD.DISTRICT AS SEMT, CLCARD.STORECREDITCARDNO AS Plasiyer, CLCARD.ACTIVE AS Statüsü FROM LG_086_01_CLFLINE CLFLINE INNER JOIN LG_086_CLCARD CLCARD ON CLFLINE.CLIENTREF = CLCARD.LOGICALREF AND CLFLINE.CANCELLED = 0 GROUP BY CLCARD.CODE, CLCARD.DEFINITION_, CLCARD.ADDR1, CLCARD.ADDR2, CLCARD.CITY, CLCARD.COUNTRY, CLCARD.POSTCODE, CLCARD.TELNRS1, CLCARD.TELNRS2, CLCARD.FAXNR, CLCARD.TAXNR, CLCARD.TAXOFFICE, CLCARD.DISCRATE, CLCARD.TOWN, CLCARD.DISTRICT, CLCARD.STORECREDITCARDNO, CLCARD.ACTIVE HAVING (CLCARD.CODE LIKE 'M%') AND (CLCARD.ACTIVE = 0) ORDER BY CLCARD.BAKİYE DESC
Private Sub CommandButton1_Click()
Dim Baglanti As Object, KayitSeti As Object
Set Baglanti = CreateObject("adodb.connection")
Set KayitSeti = CreateObject("adodb.recordset")
strFirma = Format(Sheets("SETUP").Range("B5"), "000")
strServer = Sheets("SETUP").Range("B1").Value
strDatabase = Sheets("SETUP").Range("B4").Value
strKullanıcı = Sheets("SETUP").Range("B2").Value
strParola = Sheets("SETUP").Range("B3").Value
tarih1 = Format(Range("B1").Value, "yyyy-mm-dd")
tarih2 = Format(Range("C1").Value, "yyyy-mm-dd")
S = "SELECT SUM(LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_KSLINES.AMOUNT) AS Giren,"
S = S & "FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_KSCARD INNER JOIN,"
S = S & "LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_KSLINES ON LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_KSCARD.LOGICALREF = LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_KSLINES.CARDREF,"
S = S & "WHERE format(LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_KSLINES.DATE_,'yyyy-mm-dd') BETWEEN '" & tarih1 & "' AND '" & tarih2 & "') AND"
S = S & "(LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_KSLINES.SIGN = 0) AND (LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_KSCARD.LOGICALREF = 1)"
Baglanti.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanıcı & "; Password=" & strParola & ";"
KayitSeti.Open S, Baglanti, 1, 1
Range("A9:IV65536").ClearContents
Range("B5").CopyFromRecordset KayitSeti
KayitSeti.Close
Baglanti.Close
Set KayitSeti = Nothing
Set Baglanti = Nothing
strFirma = vbNullString
strServer = vbNullString
strDatabase = vbNullString
strParola = vbNullString
strKullanıcı = vbNullString
S = vbNullString
End Sub
KayitSeti.Open S, Baglanti, 1, 1
[SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]SUM[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]LG_XXX_XX_KSLINES[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]AMOUNT[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][SIZE=2] [kasatop][/SIZE]
[SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][SIZE=2] LG_XXX_XX_KSLINES [/SIZE]
[SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]LG_XXX_XX_KSLINES[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]DATE_ [/SIZE][SIZE=2][COLOR=#808080]<[/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]CONVERT[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]DATETIME[/COLOR][/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'2009-03-31 00:00:00'[/COLOR][/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] 102[/SIZE][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]LG_XXX_XX_KSLINES[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]DATE_ [/SIZE][SIZE=2][COLOR=#808080]>[/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]CONVERT[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]DATETIME[/COLOR][/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]'2009-01-01 00:00:00'[/COLOR][/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] 102[/SIZE][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][SIZE=2][COLOR=#808080]and[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]LG_XXX_XX_KSLINES[/SIZE][SIZE=2][COLOR=#808080].CARDREF[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=1[/COLOR][/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
SELECT LG_010_CLCARD.CODE AS 'KOD', LG_010_CLCARD.DEFINITION_ AS 'UNVANI', LG_010_01_INVOICE.FICHENO AS 'FATURA NO', LG_010_01_PAYTRANS.PROCDATE AS 'FATURA TARİHİ', LG_010_01_PAYTRANS.DATE_ AS 'ÖDEME TARİHİ', LG_010_01_PAYTRANS.TOTAL AS 'TUTAR'
FROM VERITABANI_ISMI.dbo.LG_010_01_INVOICE LG_010_01_INVOICE, VERITABANI_ISMI.dbo.LG_010_01_PAYTRANS LG_010_01_PAYTRANS, VERITABANI_ISMI.dbo.LG_010_CLCARD LG_010_CLCARD
WHERE LG_010_01_PAYTRANS.FICHEREF = LG_010_01_INVOICE.LOGICALREF AND LG_010_01_INVOICE.CLIENTREF = LG_010_CLCARD.LOGICALREF AND LG_010_01_PAYTRANS.CARDREF = LG_010_CLCARD.LOGICALREF AND ((LG_010_01_INVOICE.TRCODE=8) AND (LG_010_01_PAYTRANS.MODULENR=4) AND (LG_010_01_PAYTRANS.PROCDATE Between ? And ?))
ORDER BY LG_010_01_INVOICE.FICHENO