Get 1 field from 1 row Access 2007 VBA

[SBCUser666]

This is driving me crazy. I want 1 field from the first row of a table.
SELECT TOP 1 fielda FROM mytable

Every row in the table will have the same value in fielda. I just want to check to see if the value is correct. I really don't want to have to process a record set for 1 field.

Is there a way to do an in-line query for 1 row and how do I later reference the data returned?

 

[Endre]

If every row will have the same value in that field then why not just run a DISTINCT query for that field:

SELECT DISTINCT FIELD1 FROM TABLE1;

By definition you will only return one row in your recordset, then check that field to see if it's the value you require.

Or a totals/group query will do the same

SELECT FIELD1 FROM TABLE1 GROUP BY FIELD1;


e.g.

DIm rs as recordset
Set rs = currentdb.openrecordset("SELECT DISTINCT FIELD1 FROM TABLE1;")

if rs!FIELD1 = "Value check" then


etc.

If you don't want to use a recordset use DLOOKUP with one of the above queries - Help will tell you how to use DLOOKUP. Else if your still stuck ask again...

 

[SBCUser666]

I did not want to use a record set. To me, way to much processing to get just 1 field from any 1 row.

DLookup worked great. Thanks!