1 - Liste Güncellendiğinde kodu:
Private Sub liste_AfterUpdate()
Dim TblAdi As String
kodu = liste.Column(6)
islemadı = liste.Column(7)
islem_turu = liste.Column(1)
islem_grubu = liste.Column(4)
yıldızlı_islem = liste.Column(5)
yururluk_tarihi = liste.Column(3)
'metin Kutusuna göre Tablo Adı seçme_______________
TblAdi = Switch(islem_turu = "Kamu", "tbl_kamu", _
islem_turu = "Ek-2B", "tbl_sut_2b", _
islem_turu = "Ek-2C", "tbl_sut_2c", _
islem_turu = "Turist", "tbl_turist")
acıklama = Nz(DLookup("aciklama", TblAdi, "id=" & liste.Column(0))) 'açıklama alanını alma
sonuc = IIf(acıklama Like "*[0-9][0-9][0-9][0-9][0-9]*", 1, 0) ' Açıklama boşsa yada 6 haneli sayı yoksa
'yada --> sonuc = IIf(Not acıklama Like "*[0-9][0-9][0-9][0-9][0-9]*", 0, 1)
If sonuc = 0 Then
'InStr Kullanarak veri alma____________________________
FaturaEdilemezInstr.RowSource = SqlBos
' Like Kullanarak veri alma____________________________
FaturaEdilemezLike.RowSource = SqlBos
'RegExp Kullanarak veri alma___________________________
FaturaEdilemezRegExp.RowSource = SqlBos
Exit Sub
End If
Dim SqlInStr, SqlLike, SqlRegExp As String
Dim KosulumInStr, KosulumLike, KosulumRegExp As String
'metin Kutusuna göre sorgu seçme_______________
SQLBx = Switch(islem_turu = "Kamu", Sqltbl_kamu, _
islem_turu = "Ek-2B", Sqltbl_sut_2b, _
islem_turu = "Ek-2C", Sqltbl_sut_2c, _
islem_turu = "Turist", Sqltbl_turist)
'InStr Kullanarak veri alma____________________________
KosulumInStr = " WHERE (InStr(1,'" & acıklama & "',[kodu])>0)" 'Çalıştı
SqlInStr = SQLBx & KosulumInStr & " ORDER BY yili DESC;"
FaturaEdilemezInstr.RowSource = SqlInStr
'Like Kullanarak veri alma____________________________
KosulumLike = " WHERE (('" & acıklama & "') like '*' & [kodu] & '*')" 'Çalıştı
SqlLike = SQLBx & KosulumLike & " ORDER BY yili DESC;"
FaturaEdilemezLike.RowSource = SqlLike
'RegExp Kullanarak veri alma____________________________
xSayim = Sayim(acıklama & "")
KosulumRegExp = " WHERE ([KODU] IN (" & xSayim & "'" & ")) "
SqlRegExp = SQLBx & KosulumRegExp & " ORDER BY yili DESC;"
If Len(xSayim & "") = 0 Then SqlRegExp = SqlBos
FaturaEdilemezRegExp.RowSource = SqlRegExp
End SubOption Compare Database
Global Const SqlBos As String = "SELECT '' AS ID,'' AS TÜR, '' AS YIL, " & _
"'' AS [YÜRÜRLÜK TARİHİ], '' AS GRUBU, " & _
"'' AS YILDIZLI, '' AS KOD, '' AS ADI, " & _
"'' AS AÇIKLAMA, '' AS PUAN,'' AS FİYATI " & _
"FROM tbl_secilenhizmetler"
Global Const Sqltbl_kamu As String = "SELECT tbl_kamu.id AS ID, tbl_kamu.tür AS TÜR, yili AS YIL, " & _
"yururluk_tarihi AS [YÜRÜRLÜK TARİHİ], '' AS GRUBU, " & _
"'' AS YILDIZLI, kodu AS KOD, islem_adi AS ADI, " & _
"aciklama AS AÇIKLAMA, '' AS PUAN, Format([fiyat],'Currency') AS FİYATI " & _
"FROM tbl_kamu"
Global Const Sqltbl_sut_2b As String = "SELECT tbl_sut_2b.id AS ID, tbl_sut_2b.tür AS TÜR, yili AS YIL, " & _
"yururluk_tarihi AS [YÜRÜRLÜK TARİHİ], '' AS GRUBU, " & _
"'' AS YILDIZLI, kodu AS KOD, islem_adi AS ADI, " & _
"aciklama AS AÇIKLAMA, islem_puani AS PUAN, Format([islem_puani]*0.593,'Currency') AS FİYATI " & _
"FROM tbl_sut_2b"
Global Const Sqltbl_sut_2c As String = "SELECT tbl_sut_2c.id AS ID, tbl_sut_2c.tür AS TÜR, yili AS YIL, " & _
"yururluk_tarihi AS [YÜRÜRLÜK TARİHİ], islem_grubu AS GRUBU, " & _
"yildizli_islem AS YILDIZLI, kodu AS KOD, islem_adi AS ADI, " & _
"aciklama AS AÇIKLAMA, islem_puani AS PUAN, " & _
"Format([islem_puani]*0.593,'Currency') AS FİYATI " & _
"FROM tbl_sut_2c"
Global Const Sqltbl_turist As String = "SELECT tbl_turist.id AS ID, tbl_turist.tür AS TÜR, yili AS YIL, " & _
"yururluk_tarihi AS [YÜRÜRLÜK TARİHİ], '' AS GRUBU, " & _
"'' AS YILDIZLI, kodu AS KOD, islem_adi AS ADI, " & _
"aciklama AS AÇIKLAMA, '' AS PUAN, Format([fiyat],'Currency') AS FİYATI " & _
"FROM tbl_turist"Option Compare Database
Function Sayim(Aciklama As String) As String
Sayim = ""
If Len(Aciklama & "") = 0 Then Exit Function 'GoTo 10
Dim RegEx As Object, metin As String
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "[a-zA-Z]{0,3}[0-9]{6}" '"[a-zA-Z]{0,3}[0-9]+"
RegEx.Global = True
RegEx.MultiLine = True
'RegEx.Execute (Aciklama)
Set eslesmeler = RegEx.Execute(Aciklama)
KelimeSayisi = eslesmeler.Count
For Each Kelime In eslesmeler
Kosulum = Kosulum & "','" & Kelime.Value
Next
If Kosulum <> "" Then Kosulum = Mid(Kosulum, 3) Else Kosulum = ""
Sayim = Kosulum
End Function
