MS Access Find First Empty Cell

Question

I have a table with unique numbers in the 1st column (course numbers). column 2 contains course numbers assigned. I need my form to find the first empty cell in column 2 and populate my form with the associated course number.

e.g.

Course Number	Course Name
1234	course a
1235	course b
1236	course c
1237
1238

I need the database to return 1237 in my form.

Any help will be greatly appreciated.

Answer 1

My question is why are you prepopulating the course numbers. As a general rule placeholder records are not a recommended technique. I'm not saying placeholder records should never be used, but I would have to understand why they are being used to justify the technique.

I've done a few training databases. I will usually have a courses. When a new course is being added I have a form where the course is added, an ID is assigned, the title is entered and any other info about the course. I don't understand the need for creating blank records with just a course number. If you don't have to do that, then there is no need for finding the first blank record.

Answer 2

Actually forgot a "t"

DMin("[Course Number]","[Your Table Name]","[Course Name] Is Not Null")

Except I think the criterion should be "[Course Name] Is Null", not "Not Null".

Answer 3

Actually forgot a "t"

DMin("[Course Number]","[Your Table Name]","[Course Name] Is Not Null")

Answer 4

The concept of "first" -- as in "first empty cell" -- is a very slippery thing, since tables have no inherent order.  As someone (I don't remember who) has said, a table should be thought of as a "big bag of records".

So your question can be answered only if we impose an order on the records.  If you want to say that, for this purpose, your table's records are in ascending sequence by [Course Number], then what you really want is to find the minimum [Course Number] from the records that have no [Course Name]. That can be returned by this query:

SELECT Min([Course Number]) AS MinCourseNumber

FROM Courses

WHERE [Course Name] Is Null;

Or you could use the DMin function to get it:

      DMin("[Course Number]", "Courses", "[Course Name] Is Null")

Answer 5

You can use an expression like:

DMin("[Course Number]","[Your Table Name]","[Course Name] Is No Null")