Results of a query

[mlopes1]

I have the following code on the OnClick of a button on a form:

strSQL = "SELECT Shelf_Life_Tbl.Shelf_Life " & _
"FROM Shelf_Life_Tbl " & _
"WHERE (((Shelf_Life_Tbl.Stock_Number)=2544) AND ((Shelf_Life_Tbl.Company)="Company1")) "

docmd.runSQL strSQL

How do I store the value returned by this query into a variable? I know it will only find one(or zero) records every time so that shouldn't be a problem.

Thanks as always,

Marco

 

[dcx693]

You have two choices: (1) Use the DLookup function to find your result. It can be rather slow for large datasets, or (2) Open your query as a recordset using ADO or DAO and assign the value returned by the query to a variable.

Let me know if you need more details.

 

[mlopes1]

If you could be so kind, could you give me a really brief example of both. My dataset currently is not large but may grow to be. So I figure learning both would be best. I really appreciate it!

Thanks

Marco

 

[dcx693]

The Dlookup would look something like this:

yourvariable=DLookup("[Shelf_Life]","Shelf_Life_Tbl","((Stock_Number)=2544) AND ((Company)="Company1"))

just check that I've got the right number of parentheses in there.

For (2) which version of Access are you running?

 

[mlopes1]

That first one was very easy for me to follow... thanks!

2) I am running AccessXP on WindowsXp.

Thanks again, this is already much help.

Marco

 

[dcx693]

mlopes1, I edited the code for that Dlookup because it didn't look right to me. Did it work the way I had it?

I'll post some more info on no 2 later on.

 

[mlopes1]

I just tried your revised version. It has one missing parantheses but otherwise worked fine. I hadn't tried your first one yet, I was just about to.

 

[dcx693]

Hmm...now that I look at it again, this looks better to me, but hey, whatever works. I hate D function syntax!

yourvariable=DLookup("[Shelf_Life]","Shelf_Life_Tbl","(Stock_Number=2544) AND (Company='Company1')")

 

[snicker]

You have two choices: (1) Use the DLookup function to find your result. It can be rather slow for large datasets, or (2) Open your query as a recordset using ADO or DAO and assign the value returned by the query to a variable.

Let me know if you need more details.

I would love an example of option #2.
Thx in advance