brain cramp on setting rs criteria

[mike]

hi. i have recordset like so:

Set rs = db.OpenRecordset("SELECT * " & _
"FROM [qryPrograms] WHERE ([Territory]= rsMain!
[Territory]) AND (EndDate BETWEEN Now() AND (Now()+45))")

i'm getting an error that says "too few parameters:
expected 1". it works fine when i replace rsMain!
[Territory] with the hardcoded territory name. i think i
need something like below but can't seem to get it to
work. any help would be great. thanks!!

( & "[Territory]= rsMain![Territory]" & )

 

[Dirk Goldgar]

hi. i have recordset like so:

Set rs = db.OpenRecordset("SELECT * " & _
"FROM [qryPrograms] WHERE ([Territory]= rsMain!
[Territory]) AND (EndDate BETWEEN Now() AND (Now()+45))")

i'm getting an error that says "too few parameters:
expected 1". it works fine when i replace rsMain!
[Territory] with the hardcoded territory name. i think i
need something like below but can't seem to get it to
work. any help would be great. thanks!!

( & "[Territory]= rsMain![Territory]" & )

Would I be right in thinking that rsMain is another recordset that you
have opened and positioned to the record you're interested in? You
can't reference that recordset within the SQL string; you must get the
value from rsMain!Territory and embed that value in the SQL string. Try
this:

Set rs = db.OpenRecordset( _
"SELECT * FROM [qryPrograms] " & _
"WHERE ([Territory]='" & rsMain![Territory] & _
'") AND (EndDate BETWEEN Date() AND (Date()+45))")

Note the following:

1. I've assumed that Territory is a text field, because you referred to
the "territory name", so I enclosed the value from rsMain!Territory in
single quotes (') within the SQL string. That won't work if the field
is numeric or if the value contains the single-quote character.

2. I took the liberty of changing Now() to Date() for your criterion on
EndDate, because I guessed that you are interested in dates only, not
dates+times. If that guess was wrong, you should change it back.

 

[mike]

Got it. It was ([Territory]= '" & rsMain![Territory] & "')