Access 2010 DLookUp [英] Access 2010 DLookUp

问题描述

第一次使用MS Access，如果有人可以指出正确的方向，则会遇到一些问题.

Working with MS Access for the first time and coming across a few problems if someone could please point me in the right direction.

所以我正在做一个模拟数据库(看起来很傻)，只是为了学习来龙去脉，并且目前需要DLookUp的帮助.

So I'm doing a mock database (so it looks silly) just to learn the ins and outs and need some help with DLookUp at the moment.

我的数据库有两个表，其中包含以下字段: C_ID课程PK和学生FK

My database has two tables, with the following fields: C_ID the PK in Courses and FK in Student

tblCourse: C_ID, Title, Subject                              
tblStudent: S_ID, C_ID, Name, EnrollDATE  

正如我所说，这仅用于测试/学习.因此，我想要的是一个过滤器，该过滤器根据EnrollDates是NULL给出C_ID的列表.

As I said this is just for testing/learning. So what I want is to have a filter that gives me a list of C_ID's based on which EnrollDates are NULL.

所以过滤器是:

Expr1: DLookUp("[tblStudent]![C_ID]","tblStudent","isNull([tblStudent]![EnrollDATE])")

我也尝试过使用条件

 [tblStudent]![EnrollDATE] = Null

目前，我只返回空白字段.非常感谢您的帮助，请让我详细说明一下我的解释是否正确.

Currently I get just blank fields returned. Any help is greatly appreciated, and please ask me to elaborate if my explanation is off.

谢谢！

推荐答案

正确的语法如下:

DLookup("C_ID", "tblStudent", "EnrollDate is null")

1. 指定列时无需包括表名
2. 在Access中，使用xxx is null或xxx is not null
检查Null

请注意，DLookup仅返回一个值(如果条件匹配多行，则该值取自任何行)，因此您不能使用它来获取以下内容的列表C_ID回来.

Note that DLookup only returns one value (if the criteria matches more than one row, the value is taken from any row), so you can't use it to get a list of C_IDs back.

您实际要做的是从一个表中选择数据，然后根据另一表中的数据进行过滤，对吗?
例如，选择所有至少有一个学生的EnrollDATE为空的课程?

What you actually want to do is select data from one table, and filter that based on data from the other table, correct?
Like, selecting all courses where at least one student has an empty EnrollDATE?

如果是，则完全不需要DLookup，有两种不同的方法可以做到这一点:

If yes, you don't need the DLookup at all, there are two different ways how to do that:

1)进行子选择:

select *
from tblCourse
where C_ID in
(
    select C_ID
    from tblStudents
    where EnrollDATE is null
)

2)通过联接表:

select tblCourse.*
from tblCourse
inner join tblStudent on tblCourse.C_ID = tblStudent.C_ID
where tblStudent.EnrollDATE is null

这是SQL，因此您需要在查询中切换到SQL视图访问.

This is SQL, so you need to switch to SQL View in your query in Access.

这篇关于Access 2010 DLookUp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！