name X axis at graph / chart in vb6

berikut script untuk menampilkan chart / graph di VB6 memakai MsChart.
Point pentingnya adalah bagian label X axis, disesuaikan dengan column name di tabel.
dan tidak memakai component adodc

berikut scriptnya.

untuk membuat sumbu X: syarat utamanya adalah,
kolom yang akan menjadi sumbu X, datanya harus bukan bilangan

-------------------------
Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Private Sub Form_Load()
Set cn = New ADODB.Connection
cn.Provider = "microsoft.jet.oledb.4.0"
cn.CursorLocation = adUseClient
cn.Open App.Path & "\db1.mdb"
Set rs = New ADODB.Recordset
'Untuk memanggil tabel
rs.Open "select * from grafik", cn, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = rs
MSChart1.chartType = VtChChartType2dBar
Set MSChart1.DataSource = rs

End Sub
--------------------------------

macam query di SQLServer

karena sering lupa, maka perlu ditulis disini:
biar mudah dicari bila diperlukan.

::: current date time
update pre_tbl set PRE_DATE = GETDATE() where PRE_CRDNO = 'CRD-123460' ;
insert into PRE_TBL values ('CRD-123461','V03A00049900',50,GETDATE());
SELECT * FROM tabel where tabel_date = CURDATE( );

::: group berdasarkan nama bulan, in dan out

select hari as bulan,
sum (case when a.remark = 'I' then jumlah else 0 end)Incoming,
sum (case when a.remark = 'O' then jumlah else 0 end)Outgoing
from (SELECT remark, DATENAME(month,tanggal) hari, sum(qty) jumlah from vwunion group by remark, DATENAME(month,tanggal))a
group by hari



:::: menentukan sisa quantity memakai HAVING
select DocNo,
sum (case when a.remark = 'I' then jumlah else 0 end)INCOMING ,
sum (case when a.remark = 'O' then jumlah else 0 end)OUTGOING,
(sum (case when a.remark = 'I' then jumlah else 0 end) - sum (case when a.remark = 'O' then jumlah else 0 end))SISA
from (select remark, docno, sum(qty)jumlah from vwunion group by docno,remark)a
group by DocNo
having (sum (case when a.remark = 'I' then jumlah else 0 end) - sum (case when a.remark = 'O' then jumlah else 0 end)) > '0.00'


::: untuk group berdasar row code and set to 0 if NULL
select hari as bulan,
sum (case when a.remark = 'I' then jumlah else 0 end)Incoming,
sum (case when a.remark = 'O' then jumlah else 0 end)Outgoing
from (SELECT remark, month(tanggal) hari, sum(qty) jumlah from vwunion group by remark, month(tanggal))a
group by hari


::: untuk group date berdasarkan bulan.
SELECT remark, month(tanggal) hari, sum(qty) jumlah from vwunion group by remark, month(tanggal)


:: updated tabel#1 dengan referensi tabel#2
update MITM_TBL SET MITM_STS = '1' from IN_TBL,mitm_tbl where IN_ITM = MITM_ITM and IN_DOCNO = 'IN-444444'