Query Advise

[Guest]

Can anyone explain the difference between the Query | Parameter dialogue box
and setting of a parameter in the query design view criteria field. I thought
using either would have the same result.

If I enter....... Like [EnterSurname] & "*" in the query criteria field and
run the query I get a box with the text "Enter Surname" in it. Enetering a
surname returns all such surnames that I am looking for.

Likewise if I enter........ Like [Enter Contacts Surname] & "*" in the
criteria parameter dialogue box I get the full text as it is above and the
result returned is every entery in my table.....

Any advise would be appreciated

 

[Guest]

Thanks Naeem
Much appreciated.

The Below example shows you how to correctly use the PARAMETERS keyword in a
query. I never use it myself, and i put the parameter after the WHERE cluase
normally. but it makes no big difference where if you use PARAMETERS or not.

If you use PARAMETERS keyword, then it will not be visible in query design
view and users need to go to Query parameters window in order to see it.

that's a difference, which at some points can be confusing for users that
want to modify and debug your query. specially if you have some nested
queries and one of them has a Parameter.

hope it helps.

PARAMETERS [Type the value Name] Text ( 3 );
SELECT mytable.Field1
FROM mytable
where field1 = [Type the value Name]

Can anyone explain the difference between the Query | Parameter dialogue box
and setting of a parameter in the query design view criteria field. I thought
using either would have the same result.

If I enter....... Like [EnterSurname] & "*" in the query criteria field and
run the query I get a box with the text "Enter Surname" in it. Enetering a
surname returns all such surnames that I am looking for.

Likewise if I enter........ Like [Enter Contacts Surname] & "*" in the
criteria parameter dialogue box I get the full text as it is above and the
result returned is every entery in my table.....

Any advise would be appreciated

 

[Guest]

Hi.

A parameter must be entered into the design grid in order to be used in the
query. Entering a parameter into the Query | Parameter dialogue box merely
allows the user to specify a parameter's data type. Any parameter entered in
that dialog box should also exist in the design grid, otherwise you are
specifying a data type for a parameter you are not using.

To illustrate the above, create a parameter in the design grid, specify the
data type in the Query | Parameter dialogue box, then view the SQL of the
query. Note the WHERE clause. Now, remove the parameter from the design
grid only, and view the SQL again. Note that the WHERE clause is no longer
present, so the query will return all records, even it still prompts for the
parameter listed in the Query | Parameter dialogue box.

As an example of why you might want to specify a parameter's data type,
let's say you have an Integer field in your query, with a parameter in the
design grid of [EnterInteger], and you specify a data type of "Integer" in
the Query | Parameter dialogue box for the [EnterInteger] parameter. You
will then only be able to enter integers when the query prompts for that
parameter. If a non-integer value is entered, a dialog box will pop up
stating that the value is not valid, and the prompt would await a valid
entry. If the data type had not been specified, the query would accept a
non-integer value as a parameter, and would return no results (since there
would be exactly zero non-integer values in your integer field).

So, to summarize:
A parameter in the design grid tells the query to use that parameter.
A parameter in the Query | Parameter dialogue box tells the query what type
of data is acceptable for that parameter.

-Michael

 

[Guest]

Thanks Michael
It make more sense now....setting the data type.
I had looked at the SQL, I don't know how I overlooked the "Where" clause
was missing...thanks again, Dermot

Hi.

A parameter must be entered into the design grid in order to be used in the
query. Entering a parameter into the Query | Parameter dialogue box merely
allows the user to specify a parameter's data type. Any parameter entered in
that dialog box should also exist in the design grid, otherwise you are
specifying a data type for a parameter you are not using.

To illustrate the above, create a parameter in the design grid, specify the
data type in the Query | Parameter dialogue box, then view the SQL of the
query. Note the WHERE clause. Now, remove the parameter from the design
grid only, and view the SQL again. Note that the WHERE clause is no longer
present, so the query will return all records, even it still prompts for the
parameter listed in the Query | Parameter dialogue box.

As an example of why you might want to specify a parameter's data type,
let's say you have an Integer field in your query, with a parameter in the
design grid of [EnterInteger], and you specify a data type of "Integer" in
the Query | Parameter dialogue box for the [EnterInteger] parameter. You
will then only be able to enter integers when the query prompts for that
parameter. If a non-integer value is entered, a dialog box will pop up
stating that the value is not valid, and the prompt would await a valid
entry. If the data type had not been specified, the query would accept a
non-integer value as a parameter, and would return no results (since there
would be exactly zero non-integer values in your integer field).

So, to summarize:
A parameter in the design grid tells the query to use that parameter.
A parameter in the Query | Parameter dialogue box tells the query what type
of data is acceptable for that parameter.

-Michael

Can anyone explain the difference between the Query | Parameter dialogue box
and setting of a parameter in the query design view criteria field. I thought
using either would have the same result.

If I enter....... Like [EnterSurname] & "*" in the query criteria field and
run the query I get a box with the text "Enter Surname" in it. Enetering a
surname returns all such surnames that I am looking for.

Likewise if I enter........ Like [Enter Contacts Surname] & "*" in the
criteria parameter dialogue box I get the full text as it is above and the
result returned is every entery in my table.....

Any advise would be appreciated