By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,120 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Assign the output of a select to a variable?

P: 52
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.

Expand|Select|Wrap|Line Numbers
  1. "SELECT [ID]" & _
  2. "FROM [Custom_Code]" & _
  3. "WHERE ([Project_Name]=' & Me!Project_Name" & _"') AND ([element_Name]=' & Me!Element_Name & ') )"
  4.  
  5.  
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
May 28 '07 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
The "slow" aproach you can use for this is the DLOOKUP() function.
Syntax:

DLOOKUP(<fieldname>,<table or query>,<where part>)

It will return Null when nothing has been found.

So for you:

Expand|Select|Wrap|Line Numbers
  1. intID = Dlookup("ID","Custom_Code","([Project_Name]='" & Me!Project_Name & "') AND ([element_Name]='" & Me!Element_Name & "' )")
  2.  
Nic;o)
May 28 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
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).
May 29 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.