Problem assigning SQL statement to ADODB.RecordSource property 1

[Paladyr]

How can I assign the following SQL statement to a string value so that when I assign ADODB.RecordSource to this string value it will work:

SELECT R.Year, R.Semester, R.Period, R.Course, R.Teacher FROM tblResults AS R WHERE R.Year Like "*" & "2001" & "*" AND R.Course Like "*" & "csc" & "*";

When I try to run this, it accepts the SQL statement but then returns no records. I cut and pasted this into access and it works just fine. This is due Next Thursday so I hope to have this done fairly soon so I can finish all my other exam stuff. Thanks for the help!

 

[lovehuman]

Try using single quotes instead of double quotes

 

[Paladyr]

Tried that already, any other ideas??? All the examples I saw on the internet had very simple SQL statements in them, like:

Select * From tblResults

And that is it, nothing more. This query acutally works without any quotes at all. If I type in the following, it returns the correct records:

SELECT * FROM tblResults WHERE R.Year Like 2001

 

[sunaj]

Hi,

Try using '%' instead of '*'.
In JET SQL % is any combination of charcarters (whereas in e.g. DOS it is *)

Sunaj

 

[Paladyr]

"Select * From tblResults R Where R.Year = " + "%" + "200" + "%"

Tried that and it did not work. Also tried it using an "_" instead of "%". Any other ideas?

 

[Paladyr]

Also doesn't work if I replace the "=" with "Like".

 

[lovehuman]

I tried this with Northwind database and it worked perfectly fine.

Select * From employees R Where R.hiredate LIKE '%' + '199' + '%'

See if it works for u

 

[petermeachem]

I think your quotes are muddled up:-

"SELECT R.Year, R.Semester, R.Period, R.Course, R.Teacher FROM tblResults AS R WHERE R.Year Like *" & "2001" & "* AND R.Course Like *" & "csc" & "*;"

The whole thing should be in quotes cos its a string. If you include any variables then you have to end the first string, concatenate your variable

like this

"SELECT * FROM TABLE WHERE Thing = " & myvariable

In addition, if thing is a string, surround it with single quotes so access knows where it starts and finishes.

"SELECT * FROM TABLE WHERE Thing = '" & myvariable & "'"

Dates use # instead of '. Homework, what do you do if myvariable contains a '?


Peter Meachem
peter@accuflight.com

 

[lovehuman]

Yes Boss, you are right the quotes are muddled up b'cuz i tried to make it simple for u. The string should be:

mYear = "199"
"Select * From employees R Where R.hiredate LIKE '%" & mYear & "%'"

And for your information if you write SQL for MS-SQL server you cannot use "#" in dates. Yes if you are writing SQL for Acces then you have to use #. Also you cannot use * with SQL Server, instead % is used.

And I did this homework 4 years back for your information

 

[petermeachem]

This is for lovehuman

1) NOWHERE in the thread does it mention sql server
2) Northwind is mentioned. An Access database
3) Homework is a generic term. Doesn't mean you are at school

There is absolutely no need to be rude here. I am taking my time to help those who request it, you didn't even ask the question in the first place. Peter Meachem
peter@accuflight.com

 

[sunaj]

down boys, down!

 

[rtshort]

SELECT R.Year, R.Semester, R.Period, R.Course, R.Teacher FROM tblResults AS R WHERE R.Year Like '%" & "2001" & "%'" AND R.Course Like '%" & "csc" & "%'";

Not really sure but this should do the trick. Kinda new at it though. Every day above ground is a GOOD DAY!!!

 

[Paladyr]

That worked! Thanks for all the help! Quick question. Do you have any links to a site that has a lot of examples or tutorials for standardized SQL? Also, what purpose does the apostrophe serve in the SQL statement:

"Select * From employees R Where R.hiredate LIKE '%" & mYear & "%'"

Are they used instead of the quotes character so that VB will know to put a quote there?

 

[rtshort]

the (')along with the (%)lets sql know that the characters between the '%xxx%' are not a complete word and to look for the partial letters in the column the query was ran on. (e.g. '%ou%') will return all of the words with the letters "ou" in them. Like, house, mouse, pour, and etc. It is more confusing to run an SQL statement within VB code because VB requires " as a delimiter and SQL requires ' for a delimiter. As far as a site with examples the following link is one I found particularly helpful. It has a database you can test queries on as well as a good tutorial. ;-)

http://www.sqlcourse.com/intro.html

Every day above ground is a GOOD DAY!!!

 

[Paladyr]

Well, that wasn't exactly what I was asking, I know how the '%ou%' works in an SQL statement, my question was more about how that code gets translated as a string. I believe '_ou_' works as well as a wild card. I can't really explain what I was thinking but just be happy knowing I figured it out . Thanks for the site info, I'll definitely check it out .