May 5, 2010 at 6:36 am
This was removed by the editor as SPAM
May 5, 2010 at 7:21 am
SQL Server Error :
Server: Msg 8120, Level 16, State 1, Line 1
Column 'LG_086_01_STLINE.STOCKREF' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT
SIPFIS.DATE_ AS Tarih,
SIPFIS.TIME_ AS Saat,
SIPFIS.FICHENO AS Siparis_No,
STOK.PRODUCERCODE AS Barkodu,
STOK.CODE AS [Stok Kodu],
STOK.NAME AS [Stok Adi],
SIPSATIR.AMOUNT AS Miktar,
LG_086_01_GNTOTST.ONHAND AS [Eldeki Miktar],
SIPSATIR.PRICE AS [SATIS BR.Fiyat],
SIPSATIR.VATMATRAH / SIPSATIR.AMOUNT AS [NET SATIS BR.Fiyat],
SIPSATIR.TOTAL AS [Brüt.Tutar],
SIPSATIR.VATMATRAH AS [Net Tutar],
OUTREMCOST AS BrMaliyet,
SIPSATIR.AMOUNT * OUTREMCOST AS [Toplam Maliyet],
SIPSATIR.VATMATRAH - SIPSATIR.AMOUNT * OUTREMCOST AS NETKAR,
LG_086_PRCLIST.PRICE As Fiyat
FROM LG_086_01_ORFICHE SIPFIS
LEFT JOIN LG_086_01_ORFLINE SIPSATIR ON SIPSATIR.ORDFICHEREF = SIPFIS.LOGICALREF
LEFT JOIN LG_086_ITEMS STOK ON STOK.LOGICALREF = SIPSATIR.STOCKREF
LEFT JOIN LG_086_01_GNTOTST ON SIPSATIR.STOCKREF = LG_086_01_GNTOTST.STOCKREF
LEFT JOIN LG_086_PRCLIST ON CARDREF=STOK.LOGICALREF AND LG_086_PRCLIST.PTYPE = 2
LEFT JOIN (SELECT OUTREMCOST , STOCKREF
FROM LG_086_01_STLINE
WHERE(TRCODE IN (51,8,1))
AND OUTREMCOST <> 0
AND LPRODSTAT = 0
AND LINETYPE = 0
GROUP BY OUTREMCOST )
OUTREMCOST ON OUTREMCOST.STOCKREF = STOK.LOGICALREF
WHERE (SIPFIS.FICHENO = '00076166') AND (LG_086_01_GNTOTST.INVENNO = 0)
If the GROUP BY function if disabled, two rows of full turns in 1083. Whereas only six lines are in order of receipt. VBA code that was converted into a version of SQL code, obtained from the EXCEL solution is as follows.
Private Sub CommandButton1_Click()
'Bir butonumuz var o butona tiklayinca sirasi ile asagidaki islemler gerçeklesecek
Dim Baglanti As Object, KayitSeti As Object
'Baglanti ve KayitSeti nesnelerini tanimliyoruz
Set Baglanti = CreateObject("adodb.connection")
'Baglanti nesnesinin türünü belirliyoruz
Set KayitSeti = CreateObject("adodb.recordset")
'KayitSeti nesnesinin türünü belirliyoruz
strFirma = Format(Sheets("SETUP").Range("B5"), "000")
'Ilgili EXCEL çalisma kitabinda SETUP adinda bir sayfa var,Bu sayfadaki B5 hücresine LOGO Firma numarasini yazacagiz
strServer = Sheets("SETUP").Range("B1").Value
'Ilgili EXCEL çalisma kitabinda SETUP adinda bir sayfa var,Bu sayfadaki B1 hücresine LOGO nun SERVER IP sini yazacagiz
strDatabase = Sheets("SETUP").Range("B4").Value
'Ilgili EXCEL çalisma kitabinda SETUP adinda bir sayfa var,Bu sayfadaki B4 hücresine LOGO nun DATABASE adini yazacagiz
strKullanici = Sheets("SETUP").Range("B2").Value
'Ilgili EXCEL çalisma kitabinda SETUP adinda bir sayfa var,Bu sayfadaki B2 hücresine SQL in USER adini yazacagiz
strParola = Sheets("SETUP").Range("B3").Value
'Ilgili EXCEL çalisma kitabinda SETUP adinda bir sayfa var,Bu sayfadaki B3 hücresine SQL USER inin sifresini yazacagiz
'tarih1 = Format(Range("I2"), "yyyy-mm-dd")
'Ilgili EXCEL çalisma kitabinda verilerin listelenecegi bir sayfa var,Bu sayfadaki I2 hücresine TARIH yazacagiz , formati GG.AA.YY seklinde olacak
' S degiskeni ve degiskenin alacagi degeri getiren T-SQL kodunu VBA formatinda yaziyoruz
S = "SELECT SIPFIS.DATE_ AS Tarih, SIPFIS.TIME_ AS Saat, SIPFIS.FICHENO AS Siparis_No, LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD.CODE, LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD.DEFINITION_, "
S = S & "STOK.PRODUCERCODE AS Barkodu, STOK.CODE AS [Stok Kodu], STOK.NAME AS [Stok Adi], SIPSATIR.AMOUNT AS Miktar,LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_GNTOTST.ONHAND AS [Eldeki Miktar], "
S = S & "(SELECT PRICE From LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_PRCLIST Where CARDREF=STOK.LOGICALREF AND LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_PRCLIST.PTYPE = 2) As Liste_Fiyati,"
S = S & "SIPSATIR.PRICE AS [Satis.Br.Fiyat],SIPSATIR.VATMATRAH / SIPSATIR.AMOUNT AS [NET SATIS BR.Fiyat],"
S = S & "(SELECT TOP 1 OUTREMCOST FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_STLINE "
S = S & "WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR (TRCODE = 8 OR (TRCODE=1))) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0 "
S = S & "ORDER BY DATE_ DESC) AS BrMaliyet, SIPSATIR.TOTAL AS [Brüt.Tutar], SIPSATIR.VATMATRAH AS [Net Tutar], "
S = S & "SIPSATIR.AMOUNT * (SELECT TOP 1 OUTREMCOST FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_STLINE WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR "
S = S & "(TRCODE = 8 OR (TRCODE=1))) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0 ORDER BY DATE_ DESC) AS [Toplam Maliyet], "
S = S & "SIPSATIR.VATMATRAH - SIPSATIR.AMOUNT * (SELECT TOP 1 OUTREMCOST FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_STLINE WHERE STOCKREF = STOK.LOGICALREF AND "
S = S & "(TRCODE = 51 OR (TRCODE = 8 OR (TRCODE=1))) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0 ORDER BY DATE_ DESC) AS NETKAR, "
S = S & "(CASE WHEN CONVERT(FLOAT,SIPSATIR.VATMATRAH)*100>0 THEN CONVERT(FLOAT,(SIPSATIR.VATMATRAH - SIPSATIR.AMOUNT * (SELECT TOP 1 OUTREMCOST FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_STLINE WHERE STOCKREF = STOK.LOGICALREF AND "
S = S & "(TRCODE = 51 OR (TRCODE = 8 OR (TRCODE=1))) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0 ORDER BY DATE_ DESC)))/CONVERT(FLOAT,SIPSATIR.VATMATRAH)*100 ELSE 0 END) AS KARORANI "
S = S & "FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_ITEMS STOK INNER JOIN LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_ORFLINE "
S = S & "SIPSATIR ON STOK.LOGICALREF = SIPSATIR.STOCKREF RIGHT OUTER JOIN LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_ORFICHE SIPFIS ON SIPSATIR.ORDFICHEREF = SIPFIS.LOGICALREF INNER JOIN "
S = S & "LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD ON SIPFIS.CLIENTREF = LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD.LOGICALREF LEFT OUTER JOIN "
S = S & "LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_GNTOTST ON SIPSATIR.STOCKREF = LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_GNTOTST.STOCKREF "
S = S & "WHERE (SIPFIS.FICHENO = '" & Format(Sheets("Analiz-2").Range("D3"), "00000000") & "') AND (LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_GNTOTST.INVENNO = 0) "
Baglanti.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanici & "; Password=" & strParola & ";"
'If Baglanti.State = 1 Then MsgBox "Veritabani ile baglanti kuruldu..."
'On Local Error Resume Next
KayitSeti.Open S, Baglanti, 1, 1
'If Err Then MsgBox "Hata Olustu"
'If KayitSeti.State = 1 Then MsgBox "Kayitsetine SQL sorgusu ile veriler aktarildi"
Range("A9:IV65536").ClearContents
Range("A9:IV65536").ClearFormats
Cells(9, 1).CopyFromRecordset KayitSeti
'Baglanti.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanici & "; Password=" & strParola & ";"
'Baglanti Nesnesini açiyoruz.
' KayitSeti.Open S, Baglanti, 1, 1
'Kayit Seti Nesnesini açiyoruz.
' Range("A9:IV65536").ClearContents
'Tarih kistasina göre butona her tiklandiginda veriler yeniden listelenecegi için , daha önceki çok verinin üzerine az veri yazilmasi gerekebilir...listelenen verileri sildiriyoruz..tekrar yazdiriyoruz.
' Cells(9, 1).CopyFromRecordset KayitSeti
'KayitSeti nesnesinin SQL sorgusundan çektigi bilgilerin , verilerin listelenecegi sayfada 9.cu satir 1.ci sütundan itibaren listelenebilmesi için
KayitSeti.Close
Baglanti.Close
Set KayitSeti = Nothing
Set Baglanti = Nothing
strFirma = vbNullString
strServer = vbNullString
strDatabase = vbNullString
strParola = vbNullString
strKullanici = vbNullString
S = vbNullString
'Worksheet_Change(ByVal Target As Range)
[F9:S65536].Select
'*****
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'*****
[F9].CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
Range("D1:S7").Select
Range("F1").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("F8:S8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
Range("F1:G2").Select
Columns("K:M").Select
Selection.NumberFormat = "#,##0.00000_ ;[Red]-#,##0.00000 "
Columns("O:O").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Columns("N:Q").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Range("F1:G2").Select
End Sub
Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
End Sub
Private Sub CommandButton1_GotFocus()
End Sub
Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
End Sub
Private Sub CommandButton1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
End Sub
Private Sub CommandButton2_Click()
Range("D3").Value = Format(Range("D3").Value + 1, "00000000")
End Sub
Private Sub CommandButton3_Click()
Range("D3").Value = Format(Range("D3").Value - 1, "00000000")
End Sub
Private Sub CommandButton4_Click()
Dim say As Byte, bul As Range
say = Range("J108").End(3).Row
Set kontrol = Range("J9:J" & say).Find("Elde Stok Yok", , , 1)
If Not kontrol Is Nothing Then
MsgBox "Daha Önce Analiz Yapilmis", 64, "Www.ExcelVBA.Net"
Exit Sub
End If
If WorksheetFunction.CountIf(Range("J9:J" & say), 0) = 0 Then MsgBox "Miktar hatasi yok,Depodaki tüm miktarlar yeterli": Exit Sub
For Each bul In Range("J9:J" & say)
If CStr(bul.Value) = CStr(0) Then
bul.Value = "Elde Stok Yok"
bul.Font.ColorIndex = 3
bul.Font.Bold = True
bul.Offset(0, -1).Font.ColorIndex = 2
bul.Offset(0, -1).Font.Bold = True
bul.Offset(0, -2).Font.ColorIndex = 2
bul.Offset(0, -2).Font.Bold = True
bul.Offset(0, -3).Font.ColorIndex = 2
bul.Offset(0, -3).Font.Bold = True
bul.Offset(0, -4).Font.ColorIndex = 2
bul.Offset(0, -4).Font.Bold = True
bul.Offset(0, 1).Font.ColorIndex = 2
bul.Offset(0, 2).Font.ColorIndex = 2
bul.Offset(0, 2).Font.Bold = True
bul.Offset(0, 3).Font.ColorIndex = 2
bul.Offset(0, 3).Font.Bold = True
bul.Offset(0, 4).Font.ColorIndex = 2
bul.Offset(0, 4).Font.Bold = True
bul.Offset(0, 4).Value = 0
bul.Offset(0, 5).Font.ColorIndex = 2
bul.Offset(0, 5).Font.Bold = True
bul.Offset(0, 5).Value = 0
bul.Offset(0, 6).Font.ColorIndex = 2
bul.Offset(0, 6).Font.Bold = True
bul.Offset(0, 6).Value = 0
bul.Offset(0, 7).Font.ColorIndex = 2
bul.Offset(0, 7).Font.Bold = True
bul.Offset(0, 7).Value = 0
bul.Offset(0, 8).Font.ColorIndex = 2
bul.Offset(0, 8).Font.Bold = True
bul.Offset(0, 8).Value = 0
bul.Offset(0, 9).Font.ColorIndex = 2
bul.Offset(0, 9).Font.Bold = True
bul.Offset(0, 9).Value = 0
Range(bul.Offset(0, -9).Address(False, False) & ":" & bul.Offset(0, 9).Address(False, False)).Interior.ColorIndex = 1
'Else
'MsgBox "MIKTAR HATASI YOK !"
End If
Next bul
End Sub
Private Sub CommandButton5_Click()
Dim say As Byte, bul As Range
say = Range("L108").End(3).Row
Set kontrol = Range("L9:L" & say).Find("Fiyat YOK", , , 1)
If Not kontrol Is Nothing Then
MsgBox "Daha Önce Analiz Yapilmis", 64, "Www.ExcelVBA.Net"
Exit Sub
End If
If WorksheetFunction.CountIf(Range("L9:L" & say), 0) = 0 Then MsgBox "Satis fiyati girilmemis stok yok": Exit Sub
For Each bul In Range("L9:L" & say)
If CStr(bul.Value) = CStr(0) Then
bul.Value = "Fiyat YOK"
bul.Font.ColorIndex = 5
bul.Font.Bold = True
bul.Offset(0, -1).Font.ColorIndex = 6
bul.Offset(0, -1).Font.Bold = True
bul.Offset(0, -2).Font.ColorIndex = 2
bul.Offset(0, -2).Font.Bold = True
bul.Offset(0, -3).Font.ColorIndex = 2
bul.Offset(0, -3).Font.Bold = True
bul.Offset(0, -4).Font.ColorIndex = 2
bul.Offset(0, -4).Font.Bold = True
bul.Offset(0, -5).Font.ColorIndex = 2
bul.Offset(0, -5).Font.Bold = True
bul.Offset(0, 1).Font.ColorIndex = 2
bul.Offset(0, 1).Font.Bold = True
bul.Offset(0, 1).Value = 0
bul.Offset(0, 2).Font.ColorIndex = 2
bul.Offset(0, 2).Font.Bold = True
bul.Offset(0, 2).Value = 0
bul.Offset(0, 3).Font.ColorIndex = 2
bul.Offset(0, 3).Font.Bold = True
bul.Offset(0, 3).Value = 0
bul.Offset(0, 4).Font.ColorIndex = 2
bul.Offset(0, 4).Font.Bold = True
bul.Offset(0, 4).Value = 0
bul.Offset(0, 5).Font.ColorIndex = 4
bul.Offset(0, 5).Font.Bold = True
bul.Offset(0, 5).Value = 0
bul.Offset(0, 6).Font.ColorIndex = 2
bul.Offset(0, 6).Font.Bold = True
bul.Offset(0, 6).Value = 0
bul.Offset(0, 7).Font.ColorIndex = 2
bul.Offset(0, 7).Font.Bold = True
bul.Offset(0, 7).Value = 0
Range(bul.Offset(0, -9).Address(False, False) & ":" & bul.Offset(0, 7).Address(False, False)).Interior.ColorIndex = 3
End If
Next bul
End Sub
Private Sub CommandButton6_Click()
'Worksheet_Change(ByVal Target As Range)
[F9:S65536].Select
'*****
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'*****
[F9].CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
Range("D1:S7").Select
Range("F1").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("F8:S8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
Range("F1:G2").Select
Columns("K:M").Select
Selection.NumberFormat = "#,##0.00000_ ;[Red]-#,##0.00000 "
Columns("O:O").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Columns("N:Q").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Range("F1:G2").Select
End Sub
Private Sub ToggleButton1_Click()
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [d3].Address Then
[H4] = "Siparis Numarasi Sorgulaniyor"
If Not Len(Target.Value) = 8 Then
MsgBox "Hatali Siparis Numarasi girdiniz !"
[H4] = "Hatali Uzunluk"
Exit Sub
End If
Dim rekortseti As Object, baglan As Object
Set baglan = CreateObject("adodb.connection")
Set rekortseti = CreateObject("adodb.recordset")
strFirma = Sheets("SETUP").Range("B5").Value
strServer = Sheets("SETUP").Range("B1").Value
strDatabase = Sheets("SETUP").Range("B4").Value
strKullanici = Sheets("SETUP").Range("B2").Value
strParola = Sheets("SETUP").Range("B3").Value
q = "SELECT FICHENO from LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_ORFICHE" ''' Bu SQL Sorgusunu sirketinize uyarlayin
baglan.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanici & "; Password=" & strParola & ";"
'baglan.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanici & "; Password=" & strParola & ";"
Set rekortseti = New ADODB.Recordset
rekortseti.Open q, baglan, adOpenForwardOnly, adLockReadOnly, adCmdText
If rekortseti.EOF Then
MsgBox "Hatali Siparis Numarasi girdiniz !"
[H4] = "Kayitli Siparis No. Yok !"
Else
[H4] = "Analiz yapabilirsiniz..."
End If
rekortseti.Close
baglan.Close
Set rekortseti = Nothing
Set baglan = Nothing
strFirma = vbNullString
strServer = vbNullString
strDatabase = vbNullString
strParola = vbNullString
strKullanici = vbNullString
q = vbNullString
End If
End Sub
Sub ELDEKIMIKTAR()
Dim i As Integer
On Error Resume Next
For i = 1 To 100
If Cells(i, 9).Value = 0 Then
Cells(i, 9).Value = "ELDE STOK YOK"
End If
Next i
End Sub
Sub ForNext_03()
For sayac = 1 To 1500
Cells(sayac, 1).Value = sayac
Next sayac
End Sub
Private Sub ELDEKI_MIKTAR(ByVal Target As Range)
'Biçimlendirme krtiterinin belirlendigi alan. burda A sütununa yazilan degerler sözkonusu
If Intersect(Target, [J:J]) Is Nothing Then Exit Sub
On Error GoTo Son
'Renklendirme yapacaginiz araligi belirleryin. Burda A ile N sütunlari arasi
adr = "A" & Target.Row & ":N" & Target.Row
' Select Case LCase(.Value) küçük harfe duyarli yapabiliriz.
' Select Case UCase(.Value) büyük harfe duyarli yapabiliriz.
Select Case Target
' Interior.ColorIndex yerine .Font.ColorIndex kullanilark biçimlendirmeyi fonta göre yapmak mümkün.
Case Null: Range(adr).Interior.ColorIndex = 20
Case "SENET": Range(adr).Interior.ColorIndex = 19
Case "POS": Range(adr).Interior.ColorIndex = 40
Case "PROTESTO MASRAFI": Range(adr).Interior.ColorIndex = 15
Case "KREDI": Range(adr).Interior.ColorIndex = 35
'Bos satir renklendirmesi
Case "": Range(adr).Interior.ColorIndex = 46
End Select
Son:
End Sub
Sub Bicim()
Dim say As Byte, bul As Range
say = Range("J108").End(3).Row
For Each bul In Range("J9:J" & say)
If CStr(bul.Value) = CStr(0) Then
bul.Value = "Elde Stok Yok"
bul.Font.ColorIndex = 2
bul.Font.Bold = True
bul.Offset(0, -1).Font.ColorIndex = 2
bul.Offset(0, -1).Font.Bold = True
Range(bul.Offset(0, -9).Address(False, False) & ":" & bul.Offset(0, 7).Address(False, False)).Interior.ColorIndex = 1
End If
Next bul
End Sub
May 5, 2010 at 7:31 am
This was removed by the editor as SPAM
May 5, 2010 at 7:42 am
Turned a result of the latest changes you've made. But failed, not the correct result. 6 lines of data would return the query results. But just 64 lines of data returned. In the far left or far right of the LEFT JOIN of the table, we can decide what should be the do not know how.
SQL Messages
(64 row(s) affected)
May 5, 2010 at 7:54 am
Here is something that would really help; provide the DDL (CREATE TABLE statement(s)) for the table9s) involved, sample data (as a series of INSERT INTO tablename statements) for the table(s) involved, expected results based on the sample data (note, this is really important to check our code).
For all of this you can cut it down the tables to just those columns needed for the query and (data selected, joins betwee tables, indexes). The sample data should be just that, sample data. It should represent the problem you are trying to solve, and since you have outer joins in your query you would want to be sure this is represented. The expected resluts is the most important, it shows us what the results of the query should return to be correct.
Doing this will help us help you, and in return you will get tested code.
May 5, 2010 at 7:54 am
I dare say. Field OUTREMCOST somehow got removed from the group by function. Consider these criteria to your query, know what to redesign?
May 5, 2010 at 8:03 am
Mr. Lynn Pettis, I understand what you said. You have a question. Reduced to the MDF file, your site may I upload? How? Does changing the file extension according to the principals?
Best Regards
May 5, 2010 at 8:15 am
This was removed by the editor as SPAM
May 5, 2010 at 9:21 am
lsuersoy (5/5/2010)
Mr. Lynn Pettis, I understand what you said. You have a question. Reduced to the MDF file, your site may I upload? How? Does changing the file extension according to the principals?Best Regards
Not what is needed. Please read the first article I reference below in my signature block regarding asking for help. Follow those instructions on what you should provide to get the best help possible. The only thing not covered in the article is the need for the expected results, and this is import for us to test our code against.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply