|
(1)表
4 b9 n6 Y4 I- ?CREATE TABLE logistics (8 |/ v$ F6 A% u! x' T/ T& m& e
id int primary key,' \- ^- H b' Z# V' \$ G+ k5 q
Product VARCHAR(255), p, s. i# T6 l( z
insert_timestamp Date: T4 b! T6 v& Y" g% i9 T! O
);
, _% P5 j. l& P2 EINSERT INTO logistics' O/ y0 ^' i0 C' ^, { X" k2 d% ?
(id, product, insert_timestamp: O @. F, v1 W
)
G6 J( u* @: x* p" r6 s) KVALUES ) Z( i) \# T g7 Z
("1", "roduct_A", "2020-02-24 18:15:48"),7 T- e3 J- f, e
("2", "roduct_B", "2020-02-24 20:30:17"), W* T3 i% W! z0 q5 `6 `1 l; j
("3", "roduct_C", "2020-02-24 23:54:58"),8 Z6 t2 {6 ]3 r' Y
("4", "roduct_D", "2020-02-25 08:09:30"),
$ i/ P$ S+ v* b/ ?. Y- X v5 Z("5", "roduct_E", "2020-02-25 10:17:15");% L6 k$ T A6 | g! o, U9 J# j
(2)中的VBA" |/ T( h1 d7 P
值Cell B1 = 2020-02-24
$ [& K2 z, L. Z6 L7 GSub Get_Data()
6 k' V; G Z) [6 @4 _* }Dim conn As New ADODB.Connection
! f" h4 U' L5 D/ ]2 l' R6 M: r, EDim rs As New ADODB.Recordset, R2 ?+ O; M: K7 W
Dim dateVar As Date3 J9 x' P7 l& e6 O! x
Set conn = New ADODB.Connection
, U' s6 i. q- A+ ~ conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=bi; UID=username; PWD=password; OPTION=3"
# m5 R) b( q; q conn.Open
2 K- g. \+ K' F9 ` strSQL = " SELECT " & _
6 A, I! j {& y% V1 e0 c " cID " & _
- i7 h8 a1 S, }- T9 P " FROM logistics " & _
! Z+ Q5 i7 F7 U4 V0 g8 A. ^ " WHERE DATE(insert_timestamp) BETWEEN """ & Format(Sheet1.Range("B1").Value, "YYYY-MM-DD") & "00:00:00" & """ " & _6 x: s6 Q# a7 @& ~) b2 g+ \( V
" AND """ & Format(Sheet1.Range("B1").Value, "YYYY-MM-DD") & "23:59:59" & """ " & _
8 @ c O& x! E0 F+ l ` " GROUP BY 1 "- d$ A: k5 ]! _, j5 h r' Z6 M
0 x. y9 M3 B P5 z
Set rs = New ADODB.Recordset- _9 a4 h2 p' D
rs.Open strSQL, conn, adOpenStatic5 Z' @% S- g, y( v7 `9 a2 e% _* b) D _
Sheet1.Range("A1").CopyFromRecordset rs
" A7 X' C, m' f$ M rs.Close
) y! l' |4 O( C conn.Close3 C; ]- ?6 u7 e3 g, N7 M
End Sub! B- ?# _- c( Q% [) A% {3 a8 _4 P1 p
我想在子句中的语句中VBA使用日期的Cell B1内部运行查询。 当我在之外运行此查询时,它运行完美:BETWEEN``WHERE
, U3 j- h& u! j1 ~7 LVBA
$ G* M& E) x# S" v, u& RSELECT
4 P) F2 J: V) L3 L2 l7 S# E: X8 ]Product- l; ]% m, |* e- |4 V" k
FROM logistics
, Y) v# Y1 L7 l X# U- `* M5 eWHERE DATE(insert_timestamp) BETWEEN "2020-02-24 00:00:00" AND "2020-02-24 23:59:59";
& U) i7 g2 N2 d7 C, @' J一次,我在中运行它VBA不会给我一个错误,但也不会给我任何结果。
+ ^* s7 s6 B I9 W4 w' f1 ]+ B我认为问题是由sql和中的组合引起VBA的strSQL。 : K3 o& z6 W2 C) J
我必须如何更改VBA才能使其正常工作?8 [# ]& a. S U; Q+ ^
: L6 y% r4 p+ f! w& k! x8 d o$ ~ g解决方案:
, \2 r- T6 I5 f8 ~/ \2 x( J3 E" J9 Y 8 u; r: T6 F2 X
$ ? E* ~ b: s: e5 l: W7 K$ X
您在日期和时间之间缺少空格…
2 A% X _$ H6 e6 p1 L8 D/ V打开VBE并调试打印公式以查看结果(制造商确保您具有立即窗口[视图菜单/立即窗口])。/ \7 [) o* {2 r( t
Sub test()% j2 M- y& H: T$ d/ A! ]
Debug.Print Format(Sheet1.Range("B1").Value, "YYYY-MM-DD") & "23:59:59"
. k- R3 R! Z/ E& mEnd Sub, \1 _' p: M V# z' }
结果 2020-03-1123:59:59
: K6 r1 q9 H" Q' T& S+ }只需在DD之后添加空格即可,如下; |: o9 J# s, o
Format(Sheet1.Range("B1").Value, "YYYY-MM-DD ") & "23:59:59" |
|