关于查询数据的问题请教大家,谢谢!
今天在实践书本上的一个实例时遇到一个问题,书中的源代码根本不能运行,琢磨半天也没有明白,向大家请教!
代码如下:
==============================================================
Private Sub Command3_Click()
Dim strId As String
Dim strName As String
Dim strDept As String
Dim strCheck As String
'按工号查询
If Len(Trim(MskId)) = 0 Then
strId = ""
Else
strId = "and a.emp_id= CLng('" & MskId & "')"
End If
'按姓名查询
If Len(Trim(Text1)) = 0 Then
strName = ""
Else
strName = "and b.emp_name='" & Trim(Text1) & "'"
End If
'按部门查询
If cboDept = "" Then
strDept = ""
Else
strDept = "and c.dept_name='" & cboDept & "'"
End If
'按时间查询
If cboCheckYear = "" Then
If cboCheckMonth = "" Then '年月都为空时
strCheck = ""
Else '任意年份的指定月
strCheck = "and a.check_ym in ('" & Year(Date) & cboCheckMonth & "','" & Year(Date) - 1 & cboCheckMonth & "')"
End If
Else
If cboCheckMonth = "" Then '指定年份的任意月
strCheck = "and a.check_ym in ('" & cboCheckYear & "01" & "','" & cboCheckYear & "02" & "','" & cboCheckYear & "03" & "','" & cboCheckYear & "04" & "','" & cboCheckYear & "05" & "','" & cboCheckYear & "06" & "','" & cboCheckYear & "03" & "','" & cboCheckYear & "07" & "','" & cboCheckYear & "08" & "','" & cboCheckYear & "09" & "','" & cboCheckYear & "10" & "','" & cboCheckYear & "11" & "','" & cboCheckYear & "12" & "' )"
Else '指定的年月
strCheck = "and a.check_ym in('" & cboCheckYear & cboCheckMonth & "')"
End If
End If
'打开一个数据集
strSql = "select a.emp_id,b.emp_name,c.dept_name,a.check_ym,a.w_days,a.l_nums,a.e_nums,a.h_days,a.n_days from checkin a ,employee b,department c where a.emp_id = b.emp_id and c.dept_id=b.dept_id " & strCheck & " " & strDept & " " & strName & " " & strId & " order by b.emp_id"
rs.Open strSql, dbConn, adOpenForwardOnly, adLockReadOnly
flxShow.Rows = 1
If rs.EOF Then
Else
'填写数据
Do While Not rs.EOF
flxShow.Rows = flxShow.Rows + 1
flxShow.TextMatrix(flxShow.Rows - 1, 0) = rs.Fields(0).Value
For i = 2 To rs.Fields.Count
flxShow.TextMatrix(flxShow.Rows - 1, i) = rs.Fields(i - 1).Value
Next i
rs.MoveNext
Loop
End If
Label8 = "找到" & flxShow.Rows - 1 & "条记录"
rs.Close
End Sub
===============================================================
在这一段代码中:
strSql = "select a.emp_id,b.emp_name,c.dept_name,a.check_ym,a.w_days,a.l_nums,a.e_nums,a.h_days,a.n_days from checkin a ,employee b,department c where a.emp_id = b.emp_id and c.dept_id=b.dept_id " & strCheck & " " & strDept & " " & strName & " " & strId & " order by b.emp_id"
1、所有的数据都保存在数据库的employee \ title \ duty \ department \ checkin \ 数据表中,可为什么在上面的代码中数据表的名称都变了(比如checkin a 后面多了个字母a),前面的字段的名称也变化了(比如a.emp_id 前面多了a.)。这是什么意思?
2、怎样根据一个或多个字段,比如“姓名”、“部门”等查找相应的信息,请大家提供些相应的代码让我学习下。
最后祝大家虎年吉祥,心想事成,身体健康!