SQL - Select Statement

[hyme]

Howdy!

I can't seem to get the proper quotes for this statement, help me, Please!

Global Num1, Num2 As Integer
Global F1, F2 As String

num1 = 1
Num2 = 2
F1 = "Hello"
F2 = "Goodbye"

SQL = "SELECT * FROM Table1
WHERE Field1 = '" & Num1 & "'" & " AND Field2 = " & Num2
& "'" & " AND Field3 = " & F1 & "'" & " AND Field4 = " & F2

Set ado1rs = New Recordset
ado1rs.Open SQL, db, adOpenStatic, adLockOptimistic


Thanx!

[barrk]

If you are passing this statement to a SQL server, you do not put quotes around numbers. Try removing the quotes and it should work.

[HunterMcCray]

Originally posted by hyme
Howdy!

I can't seem to get the proper quotes for this statement, help me, Please!

Global Num1, Num2 As Integer
Global F1, F2 As String

num1 = 1
Num2 = 2
F1 = "Hello"
F2 = "Goodbye"

SQL = "SELECT * FROM Table1
WHERE Field1 = '" & Num1 & "'" & " AND Field2 = " & Num2
& "'" & " AND Field3 = " & F1 & "'" & " AND Field4 = " & F2

Set ado1rs = New Recordset
ado1rs.Open SQL, db, adOpenStatic, adLockOptimistic


Thanx!

SQL = "SELECT Table1.* FROM Table1
WHERE Field1 = " & Num1 & " AND Field2 = " & Num2
& " AND Field3 LIKE" & Chr(39) & F1 & Chr(39) & " AND Field4 LIKE" & Chr(39)& F2 & Chr(39) & ";"

Try This. I have not used ADO yet, but this would work fine in DAO. Several notes for you:
1) Num2 is defined in your code as an integer, F2 is defined as a string; however Num1 and F1 are defined as Variants. To define Num1 and Num2 as integers in a single line you need to change your Dim statement to Dim Num1 as integer, Num2 as Integer and the same for F1 and F2 (Dim F1 as String, F2 as String). This is an odd quirk that does not make sense, but it is the way it has been since the beginning (unless they have changed it in VB6, I have not checked to make sure).
2) Assuming that Field1 and Field2 are numbers in your database then in DAO you do not need to place them in quotes (ADO may be different, but I do not think so).
3) I find it easier to use Chr(39) or Chr(34) for quotes (In DAO I use Chr(34) [The Double Quote] because I allow the use of chr(39) [The Single Quote] in search queries, I know that this is not supported in ADO).
4) I would suggest the use of variables whose names reflect their type, ie: txtF1, txtF2, intNum1, intNum2. It seems trival in a small program, but it sure helps when that program grows and you are going back over it months or years later.
5) In DAO the use of "LIKE" has distinct advantages over "=" in String comparisons. I might suggest that in many cases the use of wild cards (*) either at the beginning, end or both of a WHERE parameter returns more meaningful recordsets, and the use of wild cards requires the use of "LIKE".
6) Use ACCESS and Add New Query to test build your SQL. Hard code values into the WHERE fields where you want variables and when you are getting the recordset that you want then go to "View" and select "View SQL". Copy the SQL text and paste it to your code. Then all you have to do is replace the hardcoded values with your user provided variables. Saves a great deal of headaches.

Hope This Helps,

Hunter