SQL syntax question with Access 97 and VB6???? 1

[1712]

I am having a little trouble with using the select/where clause to reduce my recordset. I am using DAO.

My problem is knowing when to use the double quotes or single quotes when dealing with constants.

Example: I load the select statement into a string and then open the recordset with the string variable.

Dim rsOrg as recordset

strOrg = "Select * from InnRecur "

Set rsorg = Org.OpenRecordset(strOrg)

** what I want to do is this instead of above
strOrg = "Select * from InnRecur where "
strOrg = strOrg & "rsOrg!editcode = " & "R"
strOrg = strOrg & " or rsOrg!editcode = " & "S"

I keep getting expecting additional arguments in the openrecordset statement.

Charlie

 

[listener22]

If editcode is a field of InnRecur, try removing the rsOrg! from your
two conditions. ( rsOrg hasn't been populated yet, so the conditions
should refer to the table InnRecur, not to the recordset.) Jim

oracle, vb

 

[RHicks43]

This is what you said you wanted to do:

strOrg = "Select * from InnRecur where "
strOrg = strOrg & "rsOrg!editcode = " & "R"
strOrg = strOrg & " or rsOrg!editcode = " & "S"

When you finish running this code, strOrg will be as follows:

Select * from InnRecur where rsOrg!editcode = R or rsOrg!editcode = S

Focusing just on the issue of needed quotes, I believe what this SQL statement needs to look like to work is:

Select * from InnRecur where rsOrg!editcode = "R" or rsOrg!editcode = "S"

I have modified your original code to produce this result:

strOrg = "Select * from InnRecur where "
strOrg = strOrg & "rsOrg!editcode = " & chr$(34) & "R" & chr$(34)
strOrg = strOrg & " or rsOrg!editcode = " & chr$(34) & "S" & chr$(34)

Hope this helps.
Roger

 

[Antzz]

You can also use the same thing as follows :

strOrg = "Select * from InnRecur where "
strOrg = strOrg & "rsOrg!editcode = 'R'"
strOrg = strOrg & " or rsOrg!editcode = 'S'"

As you can see, you just need to surround the text in single quotes. If numbers feel confusing to you, you can use the single quotes.

Another variant might be as follows if you are passing parameters to your query. If I assume that you are passing ls_param1 and ls_param2 which are both string variables then :

strOrg = "Select * from InnRecur where "
strOrg = strOrg & "rsOrg!editcode = '" & ls_Param1 & "'"
strOrg = strOrg & " or rsOrg!editcode = '" & ls_Param2 & "'"

Hope this helps