Gjoreski |
16.05.2012 12:33 |
Predmet: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 QueritoPreuzmiIzvorni kôd (Visual Basic):SELECT tblSmetki_Stavki.Stavka, Sum(tblSmetki_Stavki.Kolicina) AS Kolicina, tblSmetki_Stavki.Procent, tblSmetki_Stavki.Ed_Cena
FROM tblSmetki INNER JOIN tblSmetki_Stavki ON tblSmetki.ID_Smetka = tblSmetki_Stavki.Smetka_Br
WHERE (((tblSmetki.Data) Between [Forms]![frmPregled_Kasa_Restoran]![txtDataOD] And [Forms]![frmPregled_Kasa_Restoran]![txtDataDO]) AND ((tblSmetki.Magacin)=[Forms]![frmPregled_Kasa_Restoran]![cboMagacin]))
GROUP BY tblSmetki_Stavki.Stavka, tblSmetki_Stavki.Procent, tblSmetki_Stavki.Ed_Cena
ORDER BY Sum(tblSmetki_Stavki.Kolicina) DESC;
Eve kako go vadam recordsetot vo VBA
PreuzmiIzvorni kôd (Visual Basic):Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryKasa_Artikli_Top")
While Not rs.EOF
MsgBox rs.Fields(1)
rs.MoveNext
Wend
Set rs = Nothing
|
zxz |
16.05.2012 13:11 |
Predmet:Re: Queries to VBA
Koji je tip polja ovo:
txtDataDO i txtDataOD
i
cboMagacin] |
Gjoreski |
16.05.2012 13:42 |
Predmet:Re: Queries to VBA
Toa se polijna od formata za kade sto ke se stampa eve ke stavam i primer |
Prilozi: Primer.rar (Velicina datoteke:119.31 KB)
|
zxz |
16.05.2012 14:25 |
Predmet:Re: Queries to VBA
PreuzmiIzvorni kôd (Visual Basic):Function QueryP()
Dim Db As Database
Dim Rs As Recordset
Dim SQL As String
Dim Datum(1 To 2) As String
Dim Magacin As Integer
Set Db = CurrentDb()
Magacin = Forms![frmPregled_Kasa_Restoran]![cboMagacin]
Datum(1) = [Forms]![frmPregled_Kasa_Restoran]![txtDataOD]
Datum(1) = DatumSQL(Datum(1))
Datum(2) = [Forms]![frmPregled_Kasa_Restoran]![txtDataDO]
Datum(2) = DatumSQL(Datum(2))
SQL = "SELECT Stavka, Sum(Kolicina) AS Suma, Procent,Ed_Cena " _
& "FROM tblSmetki " _
& "INNER JOIN tblSmetki_Stavki ON tblSmetki.ID_Smetka = tblSmetki_Stavki.Smetka_Br " _
& "WHERE Data Between " & Datum(1) & " AND " & Datum(2) & "AND Magacin=" & Magacin _
& " GROUP BY Stavka, Procent, Ed_Cena " _
& "ORDER BY Sum(Kolicina) DESC;"
Set Rs = Db.OpenRecordset(SQL)
While Not Rs.EOF
MsgBox Rs.Fields(1)
Rs.MoveNext
Wend
End Function
Function DatumSQL(Datum As String)
Dim DatumS As String
DatumS = Format(Datum, "mm-dd-yyyy hh:nn:ss")
DatumSQL = "#" & DatumS & "#"
End Function
|
Gjoreski |
16.05.2012 14:58 |
Predmet:Re: Queries to VBA
ova funkcionira fala zxz pozdrav |
Bosanec |
16.05.2012 21:53 |
Predmet:Re: Queries to VBA
evo jedan adin za pretvorbu kverija u vba
alat je izuzetno dobar ne treba razbijat glavu
pozdrav |
Prilozi: SQL Grabber.rar (Velicina datoteke:18.42 KB)
|
zxz |
16.05.2012 22:42 |
Predmet:Re: Queries to VBA
Bosanec to postavi u ovu temu.
http://www.icentar.ba/showtopic.php?id=37 |