HELP..... select statement newbie

[andrest]

I'm trying to query a table based on the value of a variable (which comes from another table) but it doesn't work.

My code works like this.

Dim myVar as String

myVar = "1001"

AdoJobs.CommandType = adCmdText
AdoJobs.RecordSource = "SELECT * FROM Jobs, Clients WHERE Jobs.ClientID = Clients.ClientID AND Clients.ClientID = '1001'"
AdoJobs.Refresh

But it doesn't work like this!

Dim myVar as String

myVar = "1001"

AdoJobs.CommandType = adCmdText
AdoJobs.RecordSource = "SELECT * FROM Jobs, Clients WHERE Jobs.ClientID = Clients.ClientID AND Clients.ClientID = 'myVar'"
AdoJobs.Refresh

How do I make it work using a variable? Can someone show me how, actually write the code, cause I don't get it with a String. I can use queries with Dates, Numbers.. but this as me puzzeled.

Andre

 

[CajunCenturion]

You might want to try this:

AdoJobs.RecordSource = "SELECT * FROM Jobs, Clients WHERE Jobs.ClientID = Clients.ClientID AND Clients.ClientID = '" * myVar & "'"

Althought it may be hard to see in the post, the single quotes are still in the statement.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Got too quick, should be

AdoJobs.RecordSource = "SELECT * FROM Jobs, Clients WHERE Jobs.ClientID = Clients.ClientID AND Clients.ClientID = '" & myVar & "'" Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Schroeder]

The way you've written your query, your SQL statement will be sent to the database looking like:

SELECT * FROM Jobs, Clients WHERE Jobs.ClientID = Clients.ClientID AND Clients.ClientID = 'myVar'

I mean, the database is not receiving the value of myVar, it's receiving the word "myVar".
You need to build the SQL statement like this:

AdoJobs.RecordSource = "SELECT * FROM Jobs, Clients WHERE Jobs.ClientID = Clients.ClientID AND Clients.ClientID = '" & myVar & "'"

VisualBasic will put this together and send:

SELECT * FROM Jobs, Clients WHERE Jobs.ClientID = Clients.ClientID AND Clients.ClientID = '1001'

You've effectively done this in your first example. Your SQL statement is worded strangely. It should still work but my guess is that it would run very slowly. In what way is it not working? Returning an error? Not giving you the results you expected? Running too slowly?

Usually an SQL statement like that would be done thusly:

"SELECT * FROM Jobs INNER JOIN Clients ON Jobs.ClientID = Clients.ClientID WHERE Clients.ClientID = '" & myVar & "'"

 

[andrest]

Thanks alot, it works fine with your second post.

Andre

 

[Schroeder]

Oops. I see now, you said the first example does work.