String SQL Query

[uGek]

quick question, I am trying to run an sql query through a module against one
my access tables with a string variable that changes while my app is running.
here is my query.

strSql = "SELECT * FROM tblSettings WHERE (((tblSettings.fldCorpID)=" &
strUID & "))" ------- it is not working -----

I have usually in the past ran queries with numbers instead of strings and I
have never had this problem through sql but this time I have tried everything
I could think of to make it run but it is not working!! can anyone help me...

usually the query that does work looks like this

strSql = "select * from table where flddate =" & inttbldate1

but like I said it is different because what I am querying is numbers
instead of a string.

 

[uGek]

thank you so much! that worked perfectly...

Try

dim strSQL as string

strSQL = ""
strSQL = strSQL & "SELECT * "
strSQL = strSQL & "FROM tblSettings "
strSQL = strSQL & "WHERE tblSetttings.fldCorpID = '" & strUID & "'"

What the key here is the single quote that is between the strUID.
If you copy it out to notepad and enlarge the font size, you'll see the
difference.
Try that and see what happens.

HTH

Rob Mastrostefano

--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

 

[Tim Ferguson]

strSql = "SELECT * FROM tblSettings WHERE (((tblSettings.fldCorpID)=" &
strUID & "))" ------- it is not working -----

You need to be able to pass in the quotation marks:

1: double up internal quotes:

strSQL = "SELECT etc etc =""" & strUID & """));"

2: use single quotes internally instead:

strSQL = "SELECT etc etc = '" & strUID & "'))"

3: use a public function and defer all the nasty stuff:

strSQL = "SELECT etc etc = " & jetString(strUID) & "))"


In all cases, you need to remember to scan strUID itself for any type of
quotation marks inside (unlikely for a UID, but common in names, and
almost inevitable for user-created input). Therefore, (3) is the best
practical answer and goes something like

public function jetString(SomeText as String) As string
' jet uses double quotes naturally
const c_char = 34

jetString = Chr$(c_char) & _
replace(sometext, chr$(c_char), string(2,c_char)) & _
chr$(c_char)
end function

HTH


Tim F