Assign the output of a select to a variable?

[Birky]

Assign the output of a select to a variable?

I am unable to find a way to assign the results of an SQL statement to a variable. I know how to assign the SQL statement to a variable but again no luck assigning the results. I have the need to assign the primary key of a table, which is an auto number data type) therefore a numeric value. My select is very basic for I am just looking for the primary key where two options are met.

"SELECT [ID]" & _
"FROM [Custom_Code]" & _
"WHERE ([Project_Name]=' & Me!Project_Name" & _"') AND ([element_Name]=' & Me!Element_Name & ') )"

So instead of returning the select to the variable I need to return the numeric value which meets the where clause above.

Can you help me?

Thanks
Birky

[nico5038]

The "slow" aproach you can use for this is the DLOOKUP() function.
Syntax:

DLOOKUP(<fieldn ame>,<table or query>,<where part>)

It will return Null when nothing has been found.

So for you:

intID = Dlookup("ID","Custom_Code","([Project_Name]='" & Me!Project_Name & "') AND ([element_Name]='" & Me!Element_Name & "' )")

Nic;o)

[NeoPa]

Nico's response will give you the value you're looking for.
Strangely, there is no straightforward way in Access, directly to obtain a single result from a SQL SELECT query. Probably because it would be difficult to handle SELECT queries with multiple records. I suppose it could always just get the first returned.
It is possible to get the result using VBA DAO (or ADODB) code, but it actually wouldn't be any faster. DLookup() is what you're after (see Nico's post #2).