Using an SQL to filter a Recordset

  • Thread starter Thread starter Chuck
  • Start date Start date
C

Chuck

Please tell me what is wrong with my syntax.

Dim db As Database
Dim rstContactHx As ADODB.Recordset

Set db = CurrentDb()
Set rstContactHx = New ADODB.Recordset

rstContactHx.Open "SELECT tblStandOrder.VenID FROM
tblStandOrder WHERE [ExpDate] Between #" & dtBegin & "#
And #" & dtEnd & "# And [Cat] = ""CA" Or [Cat] = "Both""
And [Dept] = """ & strDept & """ And [Inactive] = No;",
CurrentProject.Connection

The problem area is:

And [Cat] = ""CA" Or [Cat] = "Both""

I want to filter the recordset by either "CA" or "Both".
Your assistance with this would be greatly appreciated.

Sincerely,
Chuck
 
Chuck said:
Please tell me what is wrong with my syntax.

Dim db As Database
Dim rstContactHx As ADODB.Recordset

Set db = CurrentDb()
Set rstContactHx = New ADODB.Recordset

rstContactHx.Open "SELECT tblStandOrder.VenID FROM
tblStandOrder WHERE [ExpDate] Between #" & dtBegin & "#
And #" & dtEnd & "# And [Cat] = ""CA" Or [Cat] = "Both""
And [Dept] = """ & strDept & """ And [Inactive] = No;",
CurrentProject.Connection

The problem area is:

And [Cat] = ""CA" Or [Cat] = "Both""

I want to filter the recordset by either "CA" or "Both".
Your assistance with this would be greatly appreciated.

Sincerely,
Chuck
Click to expand...

Change that to

And ([Cat] = 'CA' Or [Cat] = 'Both')

Using single quotes instead of double quotes inside the quoted string
literal will help you avoid errors made in doubling up the quotes.

Note: The lines
Dim db As Database
and

Set db = CurrentDb()
Click to expand...

are irrelevant to the task at hand. If they exist for some other
purpose, fine; otherwise, delete them.
 
Dirk,

It has been a long week and you just made my day. I
cannot thank you enough. Have a great weekend.

Chuck
-----Original Message-----
Chuck said:
Please tell me what is wrong with my syntax.

Dim db As Database
Dim rstContactHx As ADODB.Recordset

Set db = CurrentDb()
Set rstContactHx = New ADODB.Recordset

rstContactHx.Open "SELECT tblStandOrder.VenID FROM
tblStandOrder WHERE [ExpDate] Between #" & dtBegin & "#
And #" & dtEnd & "# And [Cat] = ""CA" Or [Cat] = "Both""
And [Dept] = """ & strDept & """ And [Inactive] = No;",
CurrentProject.Connection

The problem area is:

And [Cat] = ""CA" Or [Cat] = "Both""

I want to filter the recordset by either "CA" or "Both".
Your assistance with this would be greatly appreciated.

Sincerely,
Chuck
Click to expand...

Change that to

And ([Cat] = 'CA' Or [Cat] = 'Both')

Using single quotes instead of double quotes inside the quoted string
literal will help you avoid errors made in doubling up the quotes.

Note: The lines
Dim db As Database
and

Set db = CurrentDb()
Click to expand...

are irrelevant to the task at hand. If they exist for some other
purpose, fine; otherwise, delete them.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Back
Top