Need help storing a value in a Variable Using SQL

[codeWarrior456]

I would like to know if there is a way to store a value in a variable using an SQL statement. If so, how is this done?

Here is an example of what I am trying to do. I have a database that is taking care of video rentals. Before the computer processes the order I want it to check the status of the video to make sure that it is not marked as checked out. I want to store this status in a variable but do not know how. I tried the following:

DoCmd.RunSQL ("SELECT [Branch Inventory].Status INTO strStatus FROM [Branch Inventory] WHERE VidNum = '1';&quot

However, instead of getting the value I want stored in a variable what happens is a table named strStatus is created containing the value that I was looking for. How do I fix this.

Thanks in advance.

 

[Steve101]

Try:

SearchVideoNumber = 1
YourVariable = DLOOKUP("Status", _
"Branch Inventory", _
"VidNum = " & ' & _
SearchVideoNumber & "';&quot

In this case, the DLookup function will return the Status field value of the Branch Inventory Table, where the VidNum field has a value of '1'.

If no matching entry is found for the SearchVideo number, then the function will return a NULL, which you will have to deal with (unless of course you assume that this will never happen; a dangerous assumption, unless your user interface has already prevented this situation from occuring).

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[codeWarrior456]

Thanks for the help. While the syntax wasn't quite right you got me on the right track. After I modified the last part to look the following:

"[VidNum] = '" & SearchVideoNumber & "'"

It worked like a charm. Thanks again.

 

[Steve101]

Yes, I forgot to treat the variable as a string; glad its sorted out,
Cheers,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)