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')")

 

[Pat Hartman]

The third option, is to use a query with a join. Modify the form's recordsource query to include a join to the shelf life table. That way columns from both tables will be available to your form. You do not need to duplicate the information since it is available by joining to the shelf life table any time you need it. If you really do need to store the shelf life because it may change and you always want the shelf life that was applicable at the time the row was added to the db, then you need to use the DLookup() solution. However, don't you want the stock_number and company to be variables? What good is it to look up the same product all the time?

yourvariable = DLookup("[Shelf_Life]","Shelf_Life_Tbl","Stock_Number = "& [Stock_Number] & " AND Company = & chr(034) & [Company] & chr(034))

The chr(034)'s are to get the company name surrounded by double quotes. Single quotes should not be used for names because names may themselves contain single quotes and that will cause an error. If stock_number is also text, you'll need to surround it with either single or double quotes.

 

[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