Building a Query by Form Interface

[takeagoodlook911]

Hello, I'm trying to create a method where a user selects some
criteria in a form and a query then generates the results. I have
been able to pass numbers successfully in the query but not text. If
anyone has an easy way of developing this let me know. The code below
works for numbers but not text Help me please.

Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.*"
strFROM = "UFRRecords s "
If Check2 Then
strWHERE = strWHERE & " s.[FlagField1] = " & Combo0
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 5)
BuildSQLString = True

End Function

* I believe the issue with text is occuring with "& Combo0 but im
clueless on how to reslove it.

 

[SusanV]

Text must be enclosed in quotes, so if you have a string variable, you need
to do the following in all instances:

WHERE Field = '" & stringvariable & "'"

(WHERE Field = singlequote doublequote& stringvariable & doublequote
singlequote doublequote )

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Guest]

I've got a function I use to wrap text in quotes. I've seen people use the
combination of an apostrophe and a quote and multiple quotes and find both of
these difficult to read in my code. Because of this, I wrote a simple
function Quotes( ) that I use whenever I need to wrap text in quotes. You
would use this something like:

strSQL = "SELECT * FROM yourTable " _
& "WHERE [someTextField] = " & quotes(me.txt_box)

Public Function Quotes(ByVal TextToQuote As Variant) As String

If IsNull(TextToQuote) Then TextToQuote = ""

Quotes = Chr$(34) & TextToQuote & Chr$(34)

End Function

HTH
Dale