How to do this using SQL ? Please help!!

[paul]

Private Sub Find_Click()
Dim var As String
var = varHomePhone
With Me.Data1.Recordset
.FindFirst "HomePhone='" & var & "'"
If .NoMatch Then
MsgBox " You Must first add this customer"
AddCust.Show vbModal

End If
End With

End Sub
Can any one give me the code to do this in SQL?
Thanks
Paul

[JHausmann]

[not tested]

Dim DB as database
dim rs as recordset

Set db = workspaces(0).Opendatabase("dbname")
set rs = db.openrecordset ("select * from tablename where HomePhone='" & varHomePhone & "'", dbopensnapshot)

if rs.eof = true then
MsgBox " You Must first add this customer"
AddCust.Show vbModal
end if

[SmithVoice]

Get in the habit of using chr(34) in place of single or double quote characters around the criteria.

"SELECT * FROM tbl WHERE tbl.Fld = " & chr(34) & x & chr(34)

Will allow you to save and then search for strings that have embedded quotes (users want to hold names like "O'Brien"? now you can) and make building string variables easier.

-robert

[JHausmann]

Better yet, assign a constant to contain the value:

QUOTE = chr(34)

"SELECT * FROM tbl WHERE tbl.Fld = " & QUOTE & x & QUOTE

Cost you next to nothing and is infintely more readable.

[chriskeeble]

Even easier is to just use "double double-quotes" (wow!)

Eg. mystring = "Here is a quote ("") for you."

? mystring would show:

Here is a quote(") for you.

So: you can use it in SQL like this

strSQL = "SELECT * FROM myTable WHERE mystring = """ & strCheckString & """ AND... etc

Notice the THREE quotes after mystring =... - the third ends the first bit of the string - looks a bit strange but you soon get used to it and don't have to remember to declare a quote character or the ascii no of the quote character.

Hope it helps!

------------------
Chris Keeble
www.giglist.com
mailto:chris.keeble@giglist.com

[JHausmann]

I've been known to do it all 3 ways. Which one I use depends on, 1) how much time I have, 2) the last place I applied SQL code (I work on MVS DB/2, SQL Server 6.5 [just starting 7.0], and Access and 3) how permanent the code is going to be, for me.