Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to store a query result in a variable?

Posted on 2008-10-12
16
Medium Priority
?
1,184 Views
Last Modified: 2013-11-27
Hi,

I have created a query which retuarns a single string value in result. I want to store this resulted string value in a variable for further comparison with other variables. Please help me with syntax.

Thanks
Praveen Parmar
0
Comment
Question by:parmarparveen
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 336 total points
ID: 22697151
Hello parmarparveen,

Dim rs As DAO.Recordset
Dim SomeString As String

Set rs = CurrentDb.OpenRecordset("NameOfQuery")
SomeString = rs![NameOfField]  'or SomeString = rs.Fields(0).Value, where the column indexing starts at 0
rs.Close
Set rs = Nothing

Regards,

Patrick
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft Access MVP)
DatabaseMX (Joe Anderson - Microsoft Access MVP) earned 332 total points
ID: 22698078
Another approach:

Dim sStr As String
sStr = DLookup("[YourQueryFieldName]","[YourQueryName]")

'comparison code here

mx

0
 
LVL 44

Expert Comment

by:GRayL
ID: 22699039
Just an observation, Patrick, Joe.  Would this use less resources?

SomeString = CurrentDb.OpenRecordSet("qryName")!fldName

and is it as faster or slower than the DLookup()
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 75
ID: 22699058
Well, how about running a test in a loop of 100K times to see ?

mx

0
 
LVL 44

Expert Comment

by:GRayL
ID: 22699091
Did that already.  You lose by a factor of 2:1 ;-)
0
 
LVL 75
ID: 22699262
Cool.  That's a good trick!

What about MP's scheme, similar to yours but with the extra line of code.  Run that.

mx
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 332 total points
ID: 22700160
Here was my test.  Draw your own conclusions.

ts=timer: for n = 1 to 1000: id = dlookup("drid","doctors"): next n:? timer-ts
 4.766968
ts=timer: for n = 1 to 1000: id = currentdb.OpenRecordset("doctors")!drid: next n:? timer-ts
 2.432983

0
 

Author Comment

by:parmarparveen
ID: 22700272
Thanks
0
 
LVL 75
ID: 22700595
Public Function mCheckTime()
    Dim x As Long, n As Long, ID
    Dim ts
    x = 50000
    ts = Timer: For n = 1 To x: ID = DLookup("FIELD1", "query36"): Next n
    'ts = Timer: For n = 1 To x: ID = CurrentDb.OpenRecordset("query36")!FIELD1: Next n
    Debug.Print Timer - ts
End Function

DLookup:               71.59399
Open Recordset:   64.45398

mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22706346
parmarparveen:  What didn't you like about the answer such that you rewarded a B with absolutely no feedback?  A  share would have been in order as both mx and mathewspatrick provided two ways of getting the variable, and I provided a variation on the theme, along with some test methodology and test results.  Mx went the extra mile and gave you his test methodology and results.  I am requesting you ask Community Support to re-open the question so it may be closed properly.  You should never close a question in that manner without giving the participants to correct any shortcoming you have observed.  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22723186
0
 
LVL 75
ID: 22738341
oops ... parmarparveen ... gRay meant that you should split the points between the 3 of us, and really gRay had the most efficient solution.  Can you once again Request Attention and fix this?

thx.mx
0
 

Author Closing Comment

by:parmarparveen
ID: 31505430
Thanks
0
 
LVL 75
ID: 22738361
thank you.

mx
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question