Using an SQL to filter a Recordset

[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

 

[Dirk Goldgar]

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

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()

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

 

[Chuck]

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-----

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

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()

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)


.