Assign result of sql query to a variable... help plz

[cytomatrix]

Hello guys,
This is my first post. I am just try to create a car booking database for fun. I wanna assign the result of SQL query to a variable. SQL query is quiet simple.

SELECT Customers.[First name]
FROM Customers
WHERE (((Customers.[Customer ID])=10));

Instead of '10', I wanna assign a variable. Somebody already asked a similar question
http://www.access-programmers.co.uk/forums/showthread.php?t=65550&highlight=vba+sql+variables but didnt get a working solution. I wanna do something like that. I tried alot of things most of them didnt work. I am not a VBA expert and i am using Access 2000. Please help me. Thanks a lot.

Cheers

 

[jgc31]

The answer depends on whether the field is a number or a string . Assuming [Customer ID] ( it is better practice not to have spaces in your field names so CustomerID or Customer_ID are prefered ) is a numeric data type the code would be

"SELECT Customers.[First name]
FROM Customers
WHERE (((Customers.[Customer ID])=" & intCustomerID & "));"

if however it is a string then

"SELECT Customers.[First name]
FROM Customers
WHERE (((Customers.[Customer ID])=""" & strCustomerID & """" & "));"

or equivalent

"SELECT Customers.[First name]
FROM Customers
WHERE (((Customers.[Customer ID])='" & strCustomerID & "'" & "));"

single quotes in red

Hope this helps

 

[cytomatrix]

Thanks. Actually i am trying to assign the result of that query to a variable. Sql bit is fine. I just want its result to be assigned to a variable. Thanks a lot again in advance.

 

[Superock]

The answer depends on whether the field is a number or a string . Assuming [Customer ID] ( it is better practice not to have spaces in your field names so CustomerID or Customer_ID are prefered ) is a numeric data type the code would be

"SELECT Customers.[First name]
FROM Customers
WHERE (((Customers.[Customer ID])=" & intCustomerID & "));"

if however it is a string then

"SELECT Customers.[First name]
FROM Customers
WHERE (((Customers.[Customer ID])=""" & strCustomerID & """" & "));"

or equivalent

"SELECT Customers.[First name]
FROM Customers
WHERE (((Customers.[Customer ID])='" & strCustomerID & "'" & "));"

single quotes in red

Hope this helps

Thx for a good post, I have a few questions though just to understand fully.

1. Could you write the sql without the round brackets?
2. Why use single quotes?
3. What would the syntax be if you had a field that was UserName: Form!FirstName & " " & Form!LastName (This is the way it looks if I build it with expresion builder)?

Thx

 

[jgc31]

Sorry I should read more slowly.

At least two possibilities one is to use the Dlookup function check out the VBA help for full details but should look similar to

strCustomerName = Dlookup("[First name]","Customers","[Customer ID])=10")

Dlookup can be problematic especially if there is more than one record matching the where criteria.

The other alternative is to open a recordset based on your query. This would enable you to check whether the result is unique and take some sort of action if there is more than one record ( or indeed no records ) in the recordset. Something along the lines of

Dim dbs as DAO.database
Dim rst as DAO.recordset
Dim strSQL as string
Dim strCustomerName as string

strSQL="SELECT Customers.[First name]"
strSQL= strSQL & " FROM Customers"
strSQL= strSQL & " WHERE (((Customers.[Customer ID])=10));"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Move.last

if rst.recordcount <> 1 then

do something about non unique or no record

End if

strCustomerName = rst.fields("[First name]")

rst = close

set rst = nothing
set dbs = nothing

 

[jgc31]

@Superock

1. The round brackets are not needed but query builder puts them in. If you have a statement with and / or expressions they can be useful.

2. Access uses double quotes by default, however some versions of SQL will only use single quotes. Both work equally well inside access but there maybe times if connecting to non jet dbs where double quotes wont work.

3. I believe CustomerName = """ & Form!FirstName & " " & Form!LastName & """" is correct ( I am sure someone will correct me if I am not ! ). If you check locals window when stepping through in debug mode you can check that the syntax is correct eg CustomerName = "John Smith"

 

[cytomatrix]

Worked..

Thanks alot jgc. It worked. I really appreciate it.