Select statement

  • Thread starter Thread starter Maracay
  • Start date Start date
M

Maracay

Hi guys

How can I create this recordset, I have tryied in diffrent ways, but I
haven't get it, I really apprecited any help

Set rst = dbs.OpenRecordset("SELECT * FROM qry_ClientData WHERE (Province =
"BC" or Province = "ON")")
 
Maracay said:
Hi guys

How can I create this recordset, I have tryied in diffrent ways, but I
haven't get it, I really apprecited any help

Set rst = dbs.OpenRecordset("SELECT * FROM qry_ClientData WHERE (Province
=
"BC" or Province = "ON")")
Click to expand...


You can't directly use double-quotes (") inside your double-quoted string.
In this case, the simplest solution is to use single-quotes (') instead:

Set rst = dbs.OpenRecordset( _
"SELECT * FROM qry_ClientData WHERE " & _
"(Province = 'BC' or Province = 'ON')")
 
Hi guys

How can I create this recordset, I have tryied in diffrent ways, but I
haven't get it, I really apprecited any help

Set rst = dbs.OpenRecordset("SELECT * FROM qry_ClientData WHERE (Province =
"BC" or Province = "ON")")
Click to expand...

The doublequotes around the province codes are being seen as closing quotes
for the entire string. Use singlequotes instead:

Set rst = dbs.OpenRecordset("SELECT * FROM qry_ClientData WHERE (Province =
'BC'" or Province = 'ON')")

or more compactly,

Set rst = dbs.OpenRecordset("SELECT * FROM qry_ClientData WHERE (Province
IN('BC','ON')")
 
Try this:


SELECT *
FROM qryClientData
WHERE (((Province)="ON" Or (tblProvinces.Province)="BC"));
 
Hi Thanks it works, now I have another question, is possible to assign the
logic to a text box and place the text box instead of typing the logic,
because the logic may change overtime and it wont be necesary to change the
program and for other reasons I would like to try this option.

example
txtLogic = " Province = 'BC' or Province = 'ON' "

Set rst = dbs.OpenRecordset( _
"SELECT * FROM qry_ClientData WHERE " & _
"(txtLogic)")

This doesn't work because I tried, but is the idea of what I want

Thanks
 
Maracay said:
Hi Thanks it works, now I have another question, is possible to assign the
logic to a text box and place the text box instead of typing the logic,
because the logic may change overtime and it wont be necesary to change
the
program and for other reasons I would like to try this option.

example
txtLogic = " Province = 'BC' or Province = 'ON' "

Set rst = dbs.OpenRecordset( _
"SELECT * FROM qry_ClientData WHERE " & _
"(txtLogic)")

This doesn't work because I tried, but is the idea of what I want
Click to expand...


So long as you are building the SQL statement on the fly, you can do this.
In the example above, you would do it like this:

Set rst = dbs.OpenRecordset( _
"SELECT * FROM qry_ClientData WHERE " & Me.txtLogic)

You might prefer to allow for txtLogic to be blank, indicating no filtering
on the query results. In that case, this might serve:

Dim strWhere As String

strWhere = Me.txtLogic & vbNullString

If Len(strWhere) > 0 Then
strWhere = " WHERE " & strWhere
End If

Set rst = dbs.OpenRecordset( _
"SELECT * FROM qry_ClientData" & strWhere)
 
Back
Top