Bosna i Hercegovina



#1 16.05.2012-11:33
Gjoreski Offline
Super Moderator
Registrovan/a od: 02.02.2009-22:24
Komentari: 1,535


Subject: Queries to VBA
Imam edno Queri od koe imam izvadeno raport sega mi treba nesto slicno da go napravam za pecatejne preku VBA no ne mozam da go izvedam.
Eve go cql od od Querito
DownloadIzvorni kod (Visual Basic):
  1. SELECT tblSmetki_Stavki.Stavka, Sum(tblSmetki_Stavki.Kolicina) AS Kolicina, tblSmetki_Stavki.Procent, tblSmetki_Stavki.Ed_Cena
  2. FROM tblSmetki INNER JOIN tblSmetki_Stavki ON tblSmetki.ID_Smetka = tblSmetki_Stavki.Smetka_Br
  3. WHERE (((tblSmetki.Data) Between [Forms]![frmPregled_Kasa_Restoran]![txtDataOD] And [Forms]![frmPregled_Kasa_Restoran]![txtDataDO]) AND ((tblSmetki.Magacin)=[Forms]![frmPregled_Kasa_Restoran]![cboMagacin]))
  4. GROUP BY tblSmetki_Stavki.Stavka, tblSmetki_Stavki.Procent, tblSmetki_Stavki.Ed_Cena
  5. ORDER BY Sum(tblSmetki_Stavki.Kolicina) DESC;

Eve kako go vadam recordsetot vo VBA
DownloadIzvorni kod (Visual Basic):
  1. Dim db As DAO.Database
  2. Dim rs As DAO.Recordset
  3. Set db = CurrentDb()
  4. Set rs = db.OpenRecordset("qryKasa_Artikli_Top")
  5. While Not rs.EOF
  6.   MsgBox rs.Fields(1)
  7.   rs.MoveNext
  8. Wend
  9. Set rs = Nothing
↑  ↓

#2 16.05.2012-12:11
zxz Offline
Administrator
Registrovan/a od: 03.02.2009-16:22
Komentari: 9,794


Subject: Re: Queries to VBA
Koji je tip polja ovo:
txtDataDO i txtDataOD
i
cboMagacin]
Pozdrav
↑  ↓

#3 16.05.2012-12:42
Gjoreski Offline
Super Moderator
Registrovan/a od: 02.02.2009-22:24
Komentari: 1,535


Subject: Re: Queries to VBA
Toa se polijna od formata za kade sto ke se stampa eve ke stavam i primer

Prilozi:
Informacije o fajlu: rar  Primer.rar
Preuzimanja: 76
Veličina: 119.31 KB

↑  ↓

#4 16.05.2012-13:25
zxz Offline
Administrator
Registrovan/a od: 03.02.2009-16:22
Komentari: 9,794


Subject: Re: Queries to VBA
DownloadIzvorni kod (Visual Basic):
  1. Function QueryP()
  2. Dim Db As Database
  3. Dim Rs As Recordset
  4. Dim SQL As String
  5. Dim Datum(1 To 2) As String
  6. Dim Magacin As Integer
  7.  
  8.  
  9. Set Db = CurrentDb()
  10. Magacin = Forms![frmPregled_Kasa_Restoran]![cboMagacin]
  11. Datum(1) = [Forms]![frmPregled_Kasa_Restoran]![txtDataOD]
  12. Datum(1) = DatumSQL(Datum(1))
  13. Datum(2) = [Forms]![frmPregled_Kasa_Restoran]![txtDataDO]
  14. Datum(2) = DatumSQL(Datum(2))
  15. SQL = "SELECT Stavka, Sum(Kolicina) AS Suma, Procent,Ed_Cena " _
  16.     & "FROM tblSmetki " _
  17.     & "INNER JOIN tblSmetki_Stavki ON tblSmetki.ID_Smetka = tblSmetki_Stavki.Smetka_Br " _
  18.     & "WHERE Data Between " & Datum(1) & " AND " & Datum(2) & "AND Magacin=" & Magacin _
  19.     & " GROUP BY Stavka, Procent, Ed_Cena " _
  20.     & "ORDER BY Sum(Kolicina) DESC;"
  21. Set Rs = Db.OpenRecordset(SQL)
  22. While Not Rs.EOF
  23.   MsgBox Rs.Fields(1)
  24.   Rs.MoveNext
  25. Wend
  26. End Function
  27.  
  28.  
  29. Function DatumSQL(Datum As String)
  30. Dim DatumS As String
  31.  
  32. DatumS = Format(Datum, "mm-dd-yyyy hh:nn:ss")
  33. DatumSQL = "#" & DatumS & "#"
  34. End Function

Pozdrav
↑  ↓

#5 16.05.2012-13:58
Gjoreski Offline
Super Moderator
Registrovan/a od: 02.02.2009-22:24
Komentari: 1,535


Subject: Re: Queries to VBA
ova funkcionira fala zxz pozdrav
↑  ↓

#6 16.05.2012-20:53
Bosanec Offline
Clan
Registrovan/a od: 02.02.2009-21:12
Komentari: 128


Subject: Re: Queries to VBA
evo jedan adin za pretvorbu kverija u vba
alat je izuzetno dobar ne treba razbijat glavu Happy

pozdrav

Prilozi:
Informacije o fajlu: rar  SQL Grabber.rar
Preuzimanja: 95
Veličina: 18.42 KB

↑  ↓

#7 16.05.2012-21:42
zxz Offline
Administrator
Registrovan/a od: 03.02.2009-16:22
Komentari: 9,794


Subject: Re: Queries to VBA
Bosanec to postavi u ovu temu.
http://www.icentar.ba/showtopic.php?id=37
Pozdrav
↑  ↓

Stranice (1): 1


All times are GMT +01:00. Current time: 21.11.2018-11:32.