SQL variable assigning

[Guest_imported]

Does anyone know how to take the contents from a field and assign it to a variable?
I have tried using a SELECT TOP 1 Query, but it won't assign it as I only want the ID number of a record, and that is a primary key.. So the types are wrong!.. I have tried using a recordset.. But again a similar problem.. It could just be me.. But if anyone can help me with the appropriate format, or at least another suggestion then that would be great!

Cheers.

 

[WMcGregor]

What are you trying to accomplish? You might want to look at the Dlookup function to see if that will retrieve what you are looking for...

 

[Guest_imported]

Effectively.. I am trying to do a sub query!...
I want the primary key value of a record, depending on if the criteria of one of its fields is NULL. Then once I have that ID, I can use an update query to assign a new value to another field.. Then DO WHILE & loop to do the procedure again a finate number of times (again this is another one of the variables in the SQL query!)
So do you think DLookup would work? What is the format.. do you know?

 

[Guest_imported]

Effectively.. I am trying to do a sub query!...
I want the primary key value of a record, depending on if the criteria of one of its fields is NULL. Then once I have that ID, I can use an update query to assign a new value to another field.. Then DO WHILE & loop to do the procedure again a finate number of times (again this is another one of the variables in the SQL query!)
So do you think DLookup would work? What is the format.. do you know? & can I assign this to either a string or interger variable?

 

[WMcGregor]

This is the format for a Dlookup function:

Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = "
& Forms!Shippers!ShipperID)

CompanyName reflects the field name in the Shippers table, the 3rd parameter is the equivalent of the Where clause... Still not sure if it will do what you want because I am still fuzzy on how your process is to work...

 

[Guest_imported]

That should do it! Thankyou for your help WMcGregor.
If I need some more help.. I know where to come!