473,320 Members | 2,109 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Assign the output of a select to a variable?

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
2 3122
nico5038
3,080 Expert 2GB
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
32,556 Expert Mod 16PB
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

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

Similar topics

1
by: Duncan Bloem | last post by:
Hello, I am not a stylesheet designer, but I am wondering if (and how) the following XML file can be transformed to a custom defined text output file. from XML file: <?xml version="1.0"...
5
by: Marcel Akkerman | last post by:
Hi, Does anyone have a clue how to reduce the number of nodes using XSLT? When outputing all nodes in order I could just use <xsl:for-each select="name"> But what if I, besides sorting and...
3
by: Mike Conmackie | last post by:
Greetings, I am trying to create a node in the output tree using a variable. Here are some fragments that I hope will explain the problem better. <xsl:stylesheet...
1
by: thomas | last post by:
I'm building a guitar website which uses XML and XSLT. http://www.madtim67.com/guitar/index.html You can search either by artist or song. I used the <xsl:if test="contains(artist ,$text1)"> line...
1
by: SV | last post by:
I have two input XML files. One lists all the expected elements (default.xml) with default values and the other is the actual data (data.xml). For any elements that are missing in data.xml, I want...
5
by: keeps21 | last post by:
Ok, so here goes. I need to retrieve data, (ie. id, title, introtext.) from my database and store each in a variable. I then need to repeat this for the next 4 records. So I would have 5...
2
by: mivey4 | last post by:
Hi. I am having a problem trying to find the proper way to assign a system object (or rather in this case a system object name) to a variable to use in my T-SQL code. I have to run a report...
0
by: Random | last post by:
I'm using xslt to parse data into a comma-delimted format. For a reason I can't fathom, the output is coming up with all the data for each row in a single column. The commas are there, but the...
6
by: John Larson | last post by:
Hi All, I am some information from INSPEC database records in XML to build a relational database of my own. I am currently trying to extract information by doing an XSLT transform of the XML...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.