Searching a recordset to find the first record with a NULL value in acertain field

[martinmike2]

Hello,

I would like to be able to search through a recordset and find the
first record with NULL in the SSN Field programatically.

I have no idea what is the most efficient way to accomplish this. My
idea right now is to run a query to find all the records with this
null value, then MoveFirst. I would then be updating that record with
a SSN in the SSN field.

Is this the most efffiecient way?

 

[Rick]

Hello,

I would like to be able to search through a recordset and find the
first record with NULL in the SSN Field programatically.

I have no idea what is the most efficient way to accomplish this. My
idea right now is to run a query to find all the records with this
null value, then MoveFirst. I would then be updating that record with
a SSN in the SSN field.

Is this the most efffiecient way?

Assuming an ID is how your table identifies what the first record is.
Assuming you want the whole record, I'd do:
select * from table as t1 inner join (select min(id) as minID from table
where SSN is null) as t2 on t1.id=t2.minid

 

[John Spencer]

That may not be updatable.

If so try

SELECT *
FROM TABLE
WHERE Table.IDNum =
(SELECT Min(IDNum)
FROM Table
WHERE SSN is Null)



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Rick]

That may not be updatable.

If so try

SELECT *
FROM TABLE
WHERE Table.IDNum =
(SELECT Min(IDNum)
FROM Table
WHERE SSN is Null)



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Likely not, but he didn't say it needed to be.
He said he was looking for efficiency, and it should be more efficient
than using an "in" but that would be updatable.

 

[martinmike2]

Sorry Rick, but it needs to be updateable so I can place data in the
SSN Field of the returned record. John, ill give your suggestion a
go, ill let you know whta i uncover. may be a feew days though.

 

[martinmike2]

ok, I am having a bit of a problem with this.

My SQL statement is:

INSERT INTO SSNs ( Actual_SSN )
SELECT EDVR3.SSN (WHERE SSNs1.SSN = (SELECT Min(SSN))
FROM EDVR3 LEFT JOIN SSNs1 ON EDVR3.SSN = SSNs1.Actual_SSN
WHERE (((SSNs1.Actual_SSN) Is Null) AND ((EDVR3.ACCT_CAT)<>"pl"));

It keeps telling me there is a syntax error. No matter where I put
the (WHERE SSNs1.SSN = (SELECT Min(SSN)) I get the syntax error.

 

[John Spencer]

INSERT ADDS a new record. My understanding was that you wanted to UPDATE the
field with a new value.

UPDATE SSNs
SET (Actual_SSN) = "123456789"
WHERE SSNs.SomeField =
(SELECT Min(SomeField)
FROM SSNs
WHERE [Actual_SSN] is Null)

The problem is how to get the value you want to put into the SSNs record.
If you know it and it is being displayed on a form, you can reference the
control on the form. If you are doing this in a VBA routine, then you can
build the sql statement on the fly and execute it.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[martinmike2]

John,

Worked like a champ, thanks for the help