1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
| Public CoUnT As Integer
Private Sub cal1_Click()
If CoUnT = 1 Then
txt_start.Text = cal1.Value
CoUnT = 2
Else
txt_end.Text = cal1.Value
CoUnT = 1
End If
End Sub
Private Sub UserForm_Initialize()
db_obiasa9.ConnectionString = "DSN=obiasa9;UID=admin;PWD=admin"
CoUnT = 1
cal1.Day = Day(Now)
cal1.Month = Month(Now)
cal1.Year = Year(Now)
End Sub
Private Sub cmd_ok_Click()
If txt_start.Text = "" Then myvar = MsgBox("Il faut remplir au moins 'Date de Debut'", vbCritical, "Attention!!!")
Application.ScreenUpdating = False
Workbooks.Add
SQL = " SELECT DISTINCT d.des_cl,d.cd_art,d.des_art_a,c.no_int_ord_fab, c.qte, c.qte_term, (e.qte_sto-f.qte_prison), f.qte_prison, d.cpt_cde_cl, d.cd_ext_cde_cl, d.qte_cde_cad_cl, (d.qte_cde_cad_cl-d.qte_liv_cde_cad_cl), b.px_unia_net, dateformat(d.dte_liv_conf_cde_cad_cl,'DD/MM/YY'), a.mnt_mon_cl "
SQL = SQL & "FROM obi.cl_cde_lig a,obi.cl_cde_lig_pxvte b,obi.ordfab c,obi.v_cl_cde_lig_cad1 d,obi.v_sto e,obi.v_sto_prison f "
SQL = SQL & "WHERE d.no_ste = a.no_ste "
SQL = SQL & "AND d.cpt_cde_cl = a.cpt_cde_cl "
SQL = SQL & "AND d.no_int_lig_cde_cl = a.no_int_lig_cde_cl "
SQL = SQL & "AND a.no_int_art = c.no_int_art "
SQL = SQL & "AND d.no_int_lig_cde_cl = b.no_int_lig_cde_cl "
SQL = SQL & "AND c.no_int_art = e.no_int_art "
SQL = SQL & "AND e.no_int_art = f.no_int_art "
SQL = SQL & "AND d.no_ste='01' "
SQL = SQL & "AND c.cd_etat_ordfab Between '0' And '1' "
SQL = SQL & "AND d.pt_sld_cad='0' "
SQL = SQL & "AND a.pt_force_sld='0' "
If txt_end.Text = "" Then
SQL = SQL & "AND d.dte_liv_conf_cde_cad_cl = '" & txt_start.Text & "' "
Else
SQL = SQL & "AND d.dte_liv_conf_cde_cad_cl BETWEEN '" & txt_start.Text & "' AND '" & txt_end.Text & "' "
End If
SQL = SQL & "ORDER BY d.des_cl, dateformat(d.dte_liv_conf_cde_cad_cl,'DD/MM/YY'),c.no_int_ord_fab "
Debug.Print SQL
db_obiasa9.Open
rs_obiasa9.Open SQL, db_obiasa9
Range("B3").CopyFromRecordset rs_obiasa9
rs_obiasa9.Close
db_obiasa9.Close
End Sub |
