内联SQL语句只返回重复记录中的第一条记录 [英] Inline SQL statement returning only duplicate first record in recordset

问题描述

我有一个表名为Employees与一个名为雇员ID字段。在一个单独的表，我有一个名为法师与命名的employeeStatus现场员工的一个独立的主表。我想确认谁已经终止从公司所有员工都没有在雇员表中列出。

不过，目前的code我使用如下返回的第一个记录重复在员工的主表。 Recordset对象的记录数属性的值相匹配我的期望，离职员工总数。 rs.Fields（0），但是只显示重复的主表中的第一个匹配的记录。作为从debug.print在即时窗口中看到。我已经检查以下内容：

• 追踪和字段名前导空格
• 在适当引用字符串
• SQL和VBA语法

如何解决我的code，显示所有符合条件的记录？

 公共功能validEmployee（的EmpID为String）

昏暗的星展银行作为DAO.DATABASE
昏暗的RS作为DAO.recordset
昏暗的SqlString为String
设置DBS = CurrentDb

的SqlString =SELECT [雇员] FROM [MASTER]，其中[的employeeStatus] ='终止'

集RS = dbs.openrecordset（的SqlString）
rs.moveLast
debug.print rs.recordcount
debug.print rs.fields（0）
 

解决方案

您通过记录要循环，这样的事情;

 公共功能validEmployee（的EmpID为String）

    昏暗的星展银行作为DAO.DATABASE
    昏暗的RS作为DAO.recordset
    昏暗的SqlString为String
    设置DBS = CurrentDb

    的SqlString =SELECT [雇员] FROM [MASTER]，其中[的employeeStatus] ='终止'

    集RS = dbs.openrecordset（的SqlString）
与RS
如果.recordcount＆GT; 0然后确保查询返回记录
    .moveLast举动持续再回到第一个，以确保RS知道记录计数
    .movefirst
    这样做，直到.EOF遍历，直到记录集末尾
       debug.print rs.recordcount'调试打印我们的信息
       debug.print rs.fields（0）
    循环
如果结束
结尾
rs.close'封闭
集RS =什么
 

键入的上述空气code，但它应该让你在正确的轨道。您当前的code没有遍历的记录，它只是调试印刷领域0的最后一个记录值。

I have a table named Employees with a field named EmployeeID. In a separate table, I have a separate master table of employees named Master with a field named EmployeeStatus. I am trying to validate all employees who have been terminated from the company are not listed in the Employees table.

However, current code I am using below is returning duplicate of the first record in the master table of employees. The value of record count property of the recordset object matches what I expect, the total number of terminated employees. rs.Fields(0) however only displays duplicate of the first matching record in the Master table. as seen from debug.print in the immediate window. I have already check for the following:

• Trailing and leading spaces in field names
• Proper quoting of strings
• SQL and VBA syntax

How can I fix my code to display all matching records?

Public Function validEmployee(EmpID as String)

Dim dbs As DAO.database
Dim rs As DAO.recordset
Dim sqlString as String
set dbs = CurrentDb

sqlString = "SELECT [EmployeeID] FROM [MASTER] WHERE [EmployeeStatus] = 'Terminated'"

set rs = dbs.openrecordset(sqlString)
rs.moveLast
debug.print rs.recordcount
debug.print rs.fields(0)

解决方案

You want to loop through the recordset, something like this;

        Public Function validEmployee(EmpID as String)

    Dim dbs As DAO.database
    Dim rs As DAO.recordset
    Dim sqlString as String
    set dbs = CurrentDb

    sqlString = "SELECT [EmployeeID] FROM [MASTER] WHERE [EmployeeStatus] = 'Terminated'"

    set rs = dbs.openrecordset(sqlString)
with rs
if .recordcount > 0 Then 'make sure the query returns records
    .moveLast 'move last then back to first to make sure rs knows the record count
    .movefirst
    do until .eof 'loop through until the end of the recordset
       debug.print rs.recordcount 'debug print our info
       debug.print rs.fields(0)
    loop
end if
end with
rs.close 'close off
set rs = nothing

Typed the above from aircode but it should put you on the right track. Your current code doesn't loop through the records, it is just debug printing the last records value of field 0.

这篇关于内联SQL语句只返回重复记录中的第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！