One way to understand this is to think about what you are trying to end up with. Suppose your SQL statement is supposed to look like this (note that here, 3 doesn't have quotes but ILoveJane does):
Code:
SELECT * FROM tblStudentInfo WHERE ID=3 AND Password="ILoveJane"
where the values 3 and ILoveJane come from intStdNo and txtPassoword respectively. We need to construct a string that will equate to the above.
First of all notice that I've included Password=. Presumably you have a field in tblStudentInfo that is called Passoword or something similar?
Next we have to thing about how we contruct the string. So we are using the concatenator (&) to put the elements of the string together.
Supose we try this (without the single quotes).
Code:
"SELECT * FROM tblStudentInfo WHERE ID = " & int StdNo & " AND Password=" & txtPassword
Note that double quotes here are used to identify text whereas intStdNo and txtPassword do not have any quotes because they are variables and we actually want to use the value of those variables (rather than simply the textual name).
The resulting string is:
Code:
SELECT * FROM tblStudentInfo WHERE ID = 3 AND Password=ILoveJane
Now the important bit.... If ID is an integer then ID=3 will work fine in SQL. However, if it is not then it needs quotes. We'll assume it's integer since that's the implecation.
The Password=ILoveJane bit will definately note work. In SQL the text has to be enclosed in quotes. So the following should work fine:
Code:
SELECT * FROM tblStudentInfo WHERE ID = 3 AND Password="ILoveJane"
So you see that we have got add some quotes to the ILoveJane bit. You'd kind of hope you could just do this:
Code:
""" & "ILoveJane" & """
In other words I've just wrapped the double quotes that I want to add in quotes. But the SQL engine really gets confused by this. Luckily we can aim for this instead (add single quotes):
Code:
'ILoveJane'
So the string expression will be:
Code:
"'" & "ILoveJane" & "'"
But of course we're really using txtPassword not ILoveJane so:
Code:
"'" & txtPassword & "'"
And the full correct expression is:
Code:
"SELECT * FROM tblStudentInfo WHERE ID =" & intStdNo & " AND Password='" & txtPassword & "'"
A good way to test your expression is to do the following to see what the final string looks like (where mySQLstring is your string expression):
Code:
Debug.Print mySQLstring
You should also be able to copy/paste the string into the query SQL window and run it (to test it works ok).
Hi
Thanks for your reply. I tried out the sql statement you had wrote and it did ineed work. But there are some steps I don't understand.
I understand that ILoveJane has to be in quotes because it is a string. But at this point why are you using concatenators? The first & links the string but what does the second & do?
In the following expression we are linking three strings together. I've hightlighted the string parts that will be linked. The remaining pairs of double quotes that are not highlighted are there to identify the strings:
So that's why there are two concatenators (&). To glue three strings together you need two glues.
Chris
By the way, I often have to help fixed colleagues PC's. And when I do I have to ask their password. It's surprising how many of the younger girls passwords are of the format "ILoveXXXX" where XXXX is the latest boyfriend. Hence the tongue in cheek choice of password
