Re: Syntax for multiple WHERE in recordset?

"Kahuna" <none@xxxxxxxxxxxx> wrote in message
news:umQ$U0ElFHA.1968@xxxxxxxxxxxxxxxxxxxx
> Hi Folks
>
> Heres what I have (and this work fine BTW):
>
> rst_con_calc.Open "SELECT tbl_conversion.conv_to FROM tbl_conversion
> WHERE tbl_conversion.conv_from=" & "'" & cmb_content_from & "'" & "",
> cnn, adOpenDynamic, adLockReadOnly
>
> I need another two criteria in the WHERE clause but this syntax is
> pretty dark stuff! What I need is something like:
>
> WHERE tbl_conversion.conv_from=" & "'" & cmb_content_from & "'" &
> AND "_ "tbl_conversion.conv_to=" & "'" & cmb_content_to & "'" & AND "_
> "tbl_conversion.conv_typeconvert=" & "'"cmb_typ_conv &"'" & "", cnn,
> adOpenDynamic, adLockReadOnly

Have you tried it? Are you getting an error? the only thing I see wrong is
missing & by youyr line continuations but with the wrapping done by the
newsreader it's hard to read that part anyway.

Why do you have the single quotes separated out like that? In other words,
why would you write
WHERE tbl_conversion.conv_from=" & "'" & cmb_content_from & "'" & AND "
instead of
WHERE tbl_conversion.conv_from='" & cmb_content_from & "' AND "
(not that it matters much except for making it harder to read; I see this
often and have never understood where people get it from)

If your variables can potentially contain ' characters then you may want to
ensure that they don't cause a problem. The easiest way I've found is to
add a function to handle it:

WHERE tbl_conversion.conv_from=" & SQ(cmb_content_from) & _
" AND tbl_conversion.conv_to=" & SQ(cmb_content_to) & _
" AND tbl_conversion.conv_typeconvert=" & SQ(cmb_typ_conv)

public function SQ(byval TextIn As String) as String)
SQ="'" & replace(textin,"'","''") & "'"
end function

Either that or use ADO command objects with parameters (which are also
somewhat more secure as they guard against "SQL injection attacks")

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

.

Relevant Pages

  • Re: Running 2 reports in a form together
    ... Form where the report is run from is called "clientanddate" ... Dim strReport As String 'Name of report to open. ... here is an example that tests criteria and builds a filter string to use as a parameter in OpenReport ...
    (microsoft.public.access.forms)
  • Re: Running 2 reports in a form together
    ... Form where the report is run from is called "clientanddate" ... Dim strReport As String 'Name of report to open. ... What I need it to do is also filter in the date report using the clients name ... here is an example that tests criteria and builds a filter string to use ...
    (microsoft.public.access.forms)
  • Re: Help please with autofilter and dates
    ... Dim myDate as Variant 'string or date ... display the selection criteria as below (I still have to include error traps ... I used Format to change the Criteria for Field1 to US date format as ... The AutoFilter method of a Range object is a very curious beast. ...
    (microsoft.public.excel.programming)
  • Error 438 when setting up the treeview control
    ... Public Function SetupTreeView(SetupType As String) ... Dim f As Form, firstnode As String, nodX As Node, NodX2 As Node, NodX3 As Node, NodX4 As Node, db As Database, rs As Recordset, rs2 As Recordset, criteria As String, rs3 As Recordset, rs4 As Recordset, criteria2 As String, criteria3 As String ... ElseIf SetupType = "AddWizOnly" Then ... 'Tree Level 1, Root ...
    (comp.databases.ms-access)
  • Re: Running 2 reports in a form together
    ... here is an example that tests criteria and builds a filter string to use as a parameter in OpenReport ... Since a filter is applied on the recordset, they do not have to be on the report object ...
    (microsoft.public.access.forms)
Quantcast