Referencing Field names in Parameters

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

Guest

I absolutely can not get this parameter query to work. Should the parameter
index value just be a field name, yes? i.e. parameters("myAuthority") refers
to the field "myAuthority" in my query. Weirdly, qdf.parameters.count always
shows 0.

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

qdf.parameters("myAuthority").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop


On another post, someone suggested using SET qdf.parameters("myAuthority"),
but this also results in errors. This is so basic, I'm sure!! I just need the
exact syntax!
 
No, the parameter name is not the name of the field. It is the value you put
in the Criteria row of the field you want to filter on. For example, If you
put something like [Enter myAuthority], then your statement would be:

qdf.parameters("[Enter myAuthority]") = "BC" '**Where I want to see records
like BC

The .Value is optional as it is the default property.
 
You can't have tha name of the parameter and the field in the table the same

If the Query
Select * From [Table Name] Where myAuthority = [AutorityParam]

Then the function should be
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

qdf.parameters("AutorityParam").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop
 
Thanks for this Klatuu, but I don't understand what [Enter myAuthority]
represents. Is that a possible criteria string? Why the square brackets? I
thought that indicates a field name?

Also, if that's the criteria string, where do I reference which field the
criteria is for?

Klatuu said:
No, the parameter name is not the name of the field. It is the value you put
in the Criteria row of the field you want to filter on. For example, If you
put something like [Enter myAuthority], then your statement would be:

qdf.parameters("[Enter myAuthority]") = "BC" '**Where I want to see records
like BC

The .Value is optional as it is the default property.

David said:
I absolutely can not get this parameter query to work. Should the parameter
index value just be a field name, yes? i.e. parameters("myAuthority") refers
to the field "myAuthority" in my query. Weirdly, qdf.parameters.count always
shows 0.

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

qdf.parameters("myAuthority").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop


On another post, someone suggested using SET qdf.parameters("myAuthority"),
but this also results in errors. This is so basic, I'm sure!! I just need the
exact syntax!
Click to expand...
Click to expand...
 
Sorry. I hadn't realised that [Enter ...] prompts for a search string.

So then, how do I point this criteria string to a particular field? It seems
as though I have to create a parameter item in the collection before I assign
it a value, because I still get an 'Item not found in collection' error.

Klatuu said:
No, the parameter name is not the name of the field. It is the value you put
in the Criteria row of the field you want to filter on. For example, If you
put something like [Enter myAuthority], then your statement would be:

qdf.parameters("[Enter myAuthority]") = "BC" '**Where I want to see records
like BC

The .Value is optional as it is the default property.

David said:
I absolutely can not get this parameter query to work. Should the parameter
index value just be a field name, yes? i.e. parameters("myAuthority") refers
to the field "myAuthority" in my query. Weirdly, qdf.parameters.count always
shows 0.

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

qdf.parameters("myAuthority").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop


On another post, someone suggested using SET qdf.parameters("myAuthority"),
but this also results in errors. This is so basic, I'm sure!! I just need the
exact syntax!
Click to expand...
Click to expand...
 
Have you looked at my example?
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



David said:
Thanks for this Klatuu, but I don't understand what [Enter myAuthority]
represents. Is that a possible criteria string? Why the square brackets? I
thought that indicates a field name?

Also, if that's the criteria string, where do I reference which field the
criteria is for?

Klatuu said:
No, the parameter name is not the name of the field. It is the value you put
in the Criteria row of the field you want to filter on. For example, If you
put something like [Enter myAuthority], then your statement would be:

qdf.parameters("[Enter myAuthority]") = "BC" '**Where I want to see records
like BC

The .Value is optional as it is the default property.

David said:
I absolutely can not get this parameter query to work. Should the parameter
index value just be a field name, yes? i.e. parameters("myAuthority") refers
to the field "myAuthority" in my query. Weirdly, qdf.parameters.count always
shows 0.

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

qdf.parameters("myAuthority").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop


On another post, someone suggested using SET qdf.parameters("myAuthority"),
but this also results in errors. This is so basic, I'm sure!! I just need the
exact syntax!
Click to expand...
Click to expand...
Click to expand...
 
Ofer: Which example?

Klaatu: You say that the value put into the Criteria row is effectively the
parameters argument.

So:

qdf.parameters("[Enter myAuthority]") = "BC"

I can't see how this makes sense. Is the criteria string "BC" or the [Enter
myAuthority] value?

And where do I then point which field this criteria relates to?

Ofer said:
Have you looked at my example?
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



David said:
Thanks for this Klatuu, but I don't understand what [Enter myAuthority]
represents. Is that a possible criteria string? Why the square brackets? I
thought that indicates a field name?

Also, if that's the criteria string, where do I reference which field the
criteria is for?

Klatuu said:
No, the parameter name is not the name of the field. It is the value you put
in the Criteria row of the field you want to filter on. For example, If you
put something like [Enter myAuthority], then your statement would be:

qdf.parameters("[Enter myAuthority]") = "BC" '**Where I want to see records
like BC

The .Value is optional as it is the default property.

:

I absolutely can not get this parameter query to work. Should the parameter
index value just be a field name, yes? i.e. parameters("myAuthority") refers
to the field "myAuthority" in my query. Weirdly, qdf.parameters.count always
shows 0.

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

qdf.parameters("myAuthority").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop


On another post, someone suggested using SET qdf.parameters("myAuthority"),
but this also results in errors. This is so basic, I'm sure!! I just need the
exact syntax!
Click to expand...
Click to expand...
Click to expand...
 
Look at your query in design mode.
Whatever is in the Criteria row of the field you want to filter on is what
the name of the parameter will be. The square brackets tell the query
builder it is a parameter.
Now, the best way to set this up is:
Open the query in design mode
Right click in the tables pane and select parameters
Create a parameter name (I usually prefix them with prm), so prmMyAuthority
would do.
Select a data type - This will be the data type of the field you want to
filter on
Click Ok
In the Criteria row in the column for MyAuthority, enter the parameter name
you just created. Put it in square brackets. If you don't, Access will
enclose it in quotes and you will be looking for the value "prmMyAuthority"
Now, run your query and it will pop up an input box "Enter Parameter Value"
and the name of the parameter "prmMyAuthority"
Now in your parameters statement, use "[prmMyAuthority]" and it will work
for you.




David said:
Thanks for this Klatuu, but I don't understand what [Enter myAuthority]
represents. Is that a possible criteria string? Why the square brackets? I
thought that indicates a field name?

Also, if that's the criteria string, where do I reference which field the
criteria is for?

Klatuu said:
No, the parameter name is not the name of the field. It is the value you put
in the Criteria row of the field you want to filter on. For example, If you
put something like [Enter myAuthority], then your statement would be:

qdf.parameters("[Enter myAuthority]") = "BC" '**Where I want to see records
like BC

The .Value is optional as it is the default property.

David said:
I absolutely can not get this parameter query to work. Should the parameter
index value just be a field name, yes? i.e. parameters("myAuthority") refers
to the field "myAuthority" in my query. Weirdly, qdf.parameters.count always
shows 0.

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

qdf.parameters("myAuthority").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop


On another post, someone suggested using SET qdf.parameters("myAuthority"),
but this also results in errors. This is so basic, I'm sure!! I just need the
exact syntax!
Click to expand...
Click to expand...
Click to expand...
 
Holy shit, Klaatu. Your shit is hot.

I've been using access for ages, but I've never used/seen parameters. I
guess anyone self-taught is bound to miss some fundamentals...

CHEERS!


Klatuu said:
Look at your query in design mode.
Whatever is in the Criteria row of the field you want to filter on is what
the name of the parameter will be. The square brackets tell the query
builder it is a parameter.
Now, the best way to set this up is:
Open the query in design mode
Right click in the tables pane and select parameters
Create a parameter name (I usually prefix them with prm), so prmMyAuthority
would do.
Select a data type - This will be the data type of the field you want to
filter on
Click Ok
In the Criteria row in the column for MyAuthority, enter the parameter name
you just created. Put it in square brackets. If you don't, Access will
enclose it in quotes and you will be looking for the value "prmMyAuthority"
Now, run your query and it will pop up an input box "Enter Parameter Value"
and the name of the parameter "prmMyAuthority"
Now in your parameters statement, use "[prmMyAuthority]" and it will work
for you.




David said:
Thanks for this Klatuu, but I don't understand what [Enter myAuthority]
represents. Is that a possible criteria string? Why the square brackets? I
thought that indicates a field name?

Also, if that's the criteria string, where do I reference which field the
criteria is for?

Klatuu said:
No, the parameter name is not the name of the field. It is the value you put
in the Criteria row of the field you want to filter on. For example, If you
put something like [Enter myAuthority], then your statement would be:

qdf.parameters("[Enter myAuthority]") = "BC" '**Where I want to see records
like BC

The .Value is optional as it is the default property.

:

I absolutely can not get this parameter query to work. Should the parameter
index value just be a field name, yes? i.e. parameters("myAuthority") refers
to the field "myAuthority" in my query. Weirdly, qdf.parameters.count always
shows 0.

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

qdf.parameters("myAuthority").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop


On another post, someone suggested using SET qdf.parameters("myAuthority"),
but this also results in errors. This is so basic, I'm sure!! I just need the
exact syntax!
Click to expand...
Click to expand...
Click to expand...
 
I think your last post got deleted for language, but I did see it before it
disappeared. Thanks for the compliment. I, too, am self taught, but I have
been programming in mulitple languages for 28 years.
I learned about parameters when I had a case where I had 3 criteria set in a
query and was using .parameters(0), .parameters(1), etc. Someone rearranged
the order of the query and caused my query to go crazy, so I had to figure
out how to avoid that. I guess we learn what we have to when we have to.

Good Luck.

David said:
Sorry. I hadn't realised that [Enter ...] prompts for a search string.

So then, how do I point this criteria string to a particular field? It seems
as though I have to create a parameter item in the collection before I assign
it a value, because I still get an 'Item not found in collection' error.

Klatuu said:
No, the parameter name is not the name of the field. It is the value you put
in the Criteria row of the field you want to filter on. For example, If you
put something like [Enter myAuthority], then your statement would be:

qdf.parameters("[Enter myAuthority]") = "BC" '**Where I want to see records
like BC

The .Value is optional as it is the default property.

David said:
I absolutely can not get this parameter query to work. Should the parameter
index value just be a field name, yes? i.e. parameters("myAuthority") refers
to the field "myAuthority" in my query. Weirdly, qdf.parameters.count always
shows 0.

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

qdf.parameters("myAuthority").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop


On another post, someone suggested using SET qdf.parameters("myAuthority"),
but this also results in errors. This is so basic, I'm sure!! I just need the
exact syntax!
Click to expand...
Click to expand...
Click to expand...
 
Klaatu, Berada, Niktu....

I'm now getting Datatype mismatch errors when calling

set rs = qdf.openrecordset

I defined the parameter as text (should this be the value data type?).
I'm using qdf.parameters("prmAuthority").VALUE. Should adding the .value
cause any conflict?
 
28 years, dude. Whoa. Yer techiness is older than I am! Anyhow, I really
can't believe how long this has taken, given the relative simplicity of what
I'm trying to do.

Anyway, I'm now getting either data type mismatch or 'Too Few Parameters,
expected 1'. When should you refresh parameters?

Also, do I need to specify dbOpenDynaset or anything else when calling
openrecordset? I'm not doing anything complex with the recordset. Just
cycling through the records and using the values as input to another
function. This is the final stumbling block!

Any ideas??


Klatuu said:
I think your last post got deleted for language, but I did see it before it
disappeared. Thanks for the compliment. I, too, am self taught, but I have
been programming in mulitple languages for 28 years.
I learned about parameters when I had a case where I had 3 criteria set in a
query and was using .parameters(0), .parameters(1), etc. Someone rearranged
the order of the query and caused my query to go crazy, so I had to figure
out how to avoid that. I guess we learn what we have to when we have to.

Good Luck.

David said:
Sorry. I hadn't realised that [Enter ...] prompts for a search string.

So then, how do I point this criteria string to a particular field? It seems
as though I have to create a parameter item in the collection before I assign
it a value, because I still get an 'Item not found in collection' error.

Klatuu said:
No, the parameter name is not the name of the field. It is the value you put
in the Criteria row of the field you want to filter on. For example, If you
put something like [Enter myAuthority], then your statement would be:

qdf.parameters("[Enter myAuthority]") = "BC" '**Where I want to see records
like BC

The .Value is optional as it is the default property.

:

I absolutely can not get this parameter query to work. Should the parameter
index value just be a field name, yes? i.e. parameters("myAuthority") refers
to the field "myAuthority" in my query. Weirdly, qdf.parameters.count always
shows 0.

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

qdf.parameters("myAuthority").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop


On another post, someone suggested using SET qdf.parameters("myAuthority"),
but this also results in errors. This is so basic, I'm sure!! I just need the
exact syntax!
Click to expand...
Click to expand...
Click to expand...
 
I would suggest this:
Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)

As to your data type mismatch, I don't know why that is happening. You only
need to establish the parameter value before you open the recordset. It
remains in effect as long as the recordset is open. If you need to change
parameter values, then you have to close the recordset, change the parameter
value, and reopen it.

Post back the code the way it is now and lets have a look.

David said:
28 years, dude. Whoa. Yer techiness is older than I am! Anyhow, I really
can't believe how long this has taken, given the relative simplicity of what
I'm trying to do.

Anyway, I'm now getting either data type mismatch or 'Too Few Parameters,
expected 1'. When should you refresh parameters?

Also, do I need to specify dbOpenDynaset or anything else when calling
openrecordset? I'm not doing anything complex with the recordset. Just
cycling through the records and using the values as input to another
function. This is the final stumbling block!

Any ideas??


Klatuu said:
I think your last post got deleted for language, but I did see it before it
disappeared. Thanks for the compliment. I, too, am self taught, but I have
been programming in mulitple languages for 28 years.
I learned about parameters when I had a case where I had 3 criteria set in a
query and was using .parameters(0), .parameters(1), etc. Someone rearranged
the order of the query and caused my query to go crazy, so I had to figure
out how to avoid that. I guess we learn what we have to when we have to.

Good Luck.

David said:
Sorry. I hadn't realised that [Enter ...] prompts for a search string.

So then, how do I point this criteria string to a particular field? It seems
as though I have to create a parameter item in the collection before I assign
it a value, because I still get an 'Item not found in collection' error.

:

No, the parameter name is not the name of the field. It is the value you put
in the Criteria row of the field you want to filter on. For example, If you
put something like [Enter myAuthority], then your statement would be:

qdf.parameters("[Enter myAuthority]") = "BC" '**Where I want to see records
like BC

The .Value is optional as it is the default property.

:

I absolutely can not get this parameter query to work. Should the parameter
index value just be a field name, yes? i.e. parameters("myAuthority") refers
to the field "myAuthority" in my query. Weirdly, qdf.parameters.count always
shows 0.

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

qdf.parameters("myAuthority").value = "BC" '**Where I want to see records
like BC


Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
rs.MoveNext
Loop


On another post, someone suggested using SET qdf.parameters("myAuthority"),
but this also results in errors. This is so basic, I'm sure!! I just need the
exact syntax!
Click to expand...
Click to expand...
Click to expand...
 
Klaatu. You are truly a worthy opponent. Sometime in the future, our paths
will cross again.

This time, you will taste defeat.

etc. etc....

Instead of posting that dull code, stick this in Excel and check it out!
(You'll need to ctrl+break to quit out!). I'm sure you can see that it's
nothing harmful. It is ART.

Sub colourJive()
'
' Macro8 Macro
' Macro recorded 20/09/2005 by Atos Origin UK
'
'

Application.Workbooks.Add
ActiveWindow.Zoom = 40
Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.ColumnWidth = 1.57
Range("A2").Select



upperbound = 1
lowerbound = -1

For i = 2 To 200000
xRnd = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
yRnd = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

x = ActiveCell.Row
y = ActiveCell.Column
If xRnd = -1 And x <> 1 Then
Cells(x + xRnd, y).Select
ElseIf xRnd = 1 And x < 120 Then
Cells(x + xRnd, y).Select
End If

x = ActiveCell.Row
If yRnd = -1 And y <> 1 Then
Cells(x, y + yRnd).Select
ElseIf yRnd = 1 And y < 120 Then
Cells(x, y + yRnd).Select
End If

colourUBound = 30
colourLBound = 1
colourRnd = Int((colourUBound - colourLBound + 1) * Rnd +
colourLBound)
ActiveCell.Interior.ColorIndex = colourRnd

Next
End Sub
 
Back
Top