Dynamic WHERE statement or criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is SO frustrating, as I did this ages ago, but have since lost the code
and I've been trawling the internet all day.

All I want to do is pass an argument to a select query. The field name is
'Authority Type', in table 'Authority'. I'm positive there was a docmd
function or something similar. I tried using .parameter(), but don't quite
get how that works. I stuck some dummy criteria in the query, but the
parameter.count value never showed more than 0. Is a parameter something else
entirely? RETARDED.

Is there not something like qdf.execute(Where options) or something???? I
just need to pass the field name and lookup value!!!

Dim stDocName As String
Dim db As Database
Dim rs As DAO.Recordset
Dim qdf As QueryDef


Set db = CurrentDb
Set qdf = db.QueryDefs("Authorities") 'Authorities is a basic two-field
query

Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
MsgBox thisAuthority
rs.MoveNext
Loop
 
When you have a parameter in a query, SET the .PARAMETER object variable
before opening the RecordSet.
 
Alright, well neither of these work, so what's the syntax? Is a parameter
just a field added to the query design mode editor?

qdf.Parameters("Authority Type") = "BC"
qdf.parameters("[Authority Type]") = "BC"

I always get a "No Items in this collection" error.
 
Apologies. Somehow, I missed the emphasis on SET.

I can't get any Set commands to work, though.

Set qdf.Parameters(1) = "BC" ....returns an invalid use of Property error
 
qdf.Parameters("Authority Type") = "BC"
qdf.parameters("[Authority Type]") = "BC"

I always get a "No Items in this collection" error.
Click to expand...

So what is the SQL of the query?

Tim F
 
Back
Top