Parameter for query

[Guest]

I have form with combobox, that ask user to chose client name.
What can i do if user did'nt chose client name to display all clients.
Because , now if user did'nt chose client name the query return nothing.

query look like this:
select * from customers where customer.name = [forms]![custmers]![name]

 

[peregenem]

I have form with combobox, that ask user to chose client name.
What can i do if user did'nt chose client name to display all clients.
Because , now if user did'nt chose client name the query return nothing.

query look like this:
select * from customers where customer.name = [forms]![custmers]![name]

CREATE PROCEDURE GetCustomers
customer_name VARCHAR(35) = NULL)
AS
select * from customer
where customer.name =
IIFcustomer_name IS NULL OR LENcustomer_name) = 0, customer.name,
:customer_name)

EXECUTE GetCustomers
-- return all rows

EXECUTE GetCustomers ''
-- return all rows

EXECUTE GetCustomers 'Per'
-- return matching rows

 

[Guest]

where i have to put this code ?

"(e-mail address removed)" напиÑа:

I have form with combobox, that ask user to chose client name.
What can i do if user did'nt chose client name to display all clients.
Because , now if user did'nt chose client name the query return nothing.

query look like this:
select * from customers where customer.name = [forms]![custmers]![name]

CREATE PROCEDURE GetCustomers
customer_name VARCHAR(35) = NULL)
AS
select * from customer
where customer.name =
IIFcustomer_name IS NULL OR LENcustomer_name) = 0, customer.name,
:customer_name)

EXECUTE GetCustomers
-- return all rows

EXECUTE GetCustomers ''
-- return all rows

EXECUTE GetCustomers 'Per'
-- return matching rows

 

[Van T. Dinh]

Try:

SELECT * FROM customers
WHERE ( customer.name = [Forms]![custmers]![name] )
OR ( Forms![customers]![name] Is Null )

The other response looks more applicable to Analysis Services of MS-SQL
Server which I doubt whether it is applicable to you.

 

[peregenem]

The other response looks more applicable to Analysis Services of MS-SQL
Server which I doubt whether it is applicable to you.

It is Jet and is relevant, the title of this post suggested to me a
proc. SQL Server T-SQL arguments have to start with @, I feel.

Do this code look more familiar? Give it a try in your Jet/Access
project

Sub test()
With CurrentProject.Connection
.Execute _
"CREATE TABLE customer (name VARCHAR(35) NOT NULL)"
.Execute _
"INSERT INTO customer VALUES ('chonny')"
.Execute _
"INSERT INTO customer VALUES ('Van')"
.Execute _
"CREATE PROCEDURE GetCustomers " & _
"customer_name VARCHAR(35) = NULL) " & _
"AS " & _
"select * from customer " & _
"where customer.name = " & _
"IIFcustomer_name IS NULL OR LENcustomer_name) = 0, " & _
" customer.name, :customer_name) "
Dim rs As Object

Set rs = .Execute("EXECUTE GetCustomers")
MsgBox rs.GetString

Set rs = .Execute("EXECUTE GetCustomers ''")
MsgBox rs.GetString

Set rs = .Execute("EXECUTE GetCustomers 'Van'")
MsgBox rs.GetString

End With
End Sub

 

[Van T. Dinh]

Sorry but the Title did say Parameter for Query. Most Access users will
take it as an Access Parameter Query, not a SP.

The O.P. did post his SQL String and it is an Access Parameter Query.

I wasn't sure and that's why I wrote "looks like". The CREATE PROCEDURE may
work in JET 4 but for purely Access applications, most people still stick to
Access Parameter Query.

 

[peregenem]

Sorry but the Title did say Parameter for Query.

The Access term 'Query' is very vague. For example, I could put DELETE
FROM MyTable in a Query object but the SQL doesn't amount to a query.
Jet's CREATE PROCEDURE syntax creates Query object with different
syntax (PARAMETERS Declaration), it just a different way of initially
creating the underlying Query object.

The O.P. did post his SQL String and it is an Access Parameter Query.

May I ask you a question? If you call the OP's square brackets syntax a
'Access Parameter Query', what does one call the alternative syntax
using the PARAMETERS Declaration? Thanks.

The CREATE PROCEDURE may
work in JET 4 but for purely Access applications, most people still stick to
Access Parameter Query.

And there is a world of difference between 'common practise' and 'best
practise' I can't recommend embedding front end form fields in SQL
code. Better IMO to have generic SQL code in the 'backend' and call it
by passing parameter values from the front end.

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

The Access term 'Query' is very vague. For example, I could put DELETE
FROM MyTable in a Query object but the SQL doesn't amount to a query.
Jet's CREATE PROCEDURE syntax creates Query object with different
syntax (PARAMETERS Declaration), it just a different way of initially
creating the underlying Query object.

Just check Access Help or Access books and see if "Query" is vague.
Besides, like I wrote, just look at the Subject and you know what the O.P.
meant.

May I ask you a question? If you call the OP's square brackets syntax a
'Access Parameter Query', what does one call the alternative syntax
using the PARAMETERS Declaration? Thanks.

Whether you have the PARAMETERS declaration or not, it is still a Parameter
Query. In Access, the Expression Service tries to resolve the Parameters
automatically most of the times so there is no need to explicitly use the
PARAMETERS declaration. AFAIK, only Cross-Tab Query insists on the explicit
declaration of Parameters.

Once again, have a look at some Access books.

And there is a world of difference between 'common practise' and 'best
practise' I can't recommend embedding front end form fields in SQL
code. Better IMO to have generic SQL code in the 'backend' and call it
by passing parameter values from the front end.

I wonder where you think the SP will be executed? It is on the same machine
that runs the Front-End. Remember that there is no separate "JET Server".
Thus, there is no advantage because processing is still being done on the
same machine.

To modify the design of some objects in the database, you need exclusive
access to the database file. That's why it is more convenient to split the
database to Front-End and Back-End (Table only). This way, users still can
access the data while modifications are being done on the Front-End.
Therefore, there are advantages in splitting an Access application where
only Tables reside in the Back-End mdb file.

Have a look at some Access books and see what various authors recommend.
Most Access book I read have only JET Tables in the Back-End and the rest in
the Front-End. Perhaps, most authors are wrong and you are right.

I am sure you can write lots about advantages of n-tier architecture but
this is not the intended / primary market for Access using the JET database
engine.

 

[peregenem]

Just check Access Help or Access books and see if "Query" is vague.

The Access help glossary informs me a query is a 'A formalized
instruction to a database to either return a set of records or perform
a specified action on a set of records'. However, In the wider SQL
world outside of Access, there is a distinction between a SQL DML
'query' (returns rows) and a SQL DML 'statement' (modifies something).
There is also a distinction between rows and records but that's another
matter

I revise my 'vague'. The Access developers have taken liberties with
the generally accepted meaning of SQL query!

In Access can I put a SQL statement (as distinct from a query) into a
Query object (big Q) but that doesn't make it a query (small q).

Whether you have the PARAMETERS declaration or not, it is still a Parameter
Query.

OK, I run a CREATE PROCEDURE statement it creates a Query Parameter
Query object in the database. So we're all talking about the same
underlying thing, right? So why have you jumped on me for using a
different syntax to create the object? You want to be friendly to
newbies, even if they don't speak 'Access' speak, don't you?

I wonder where you think the SP will be executed?

I tried to use an Access term and got it wrong. Sorry I was trying
to make a logical (not physical) distinction between code used in front
end application and code used at the database engine level. No need to
put the whole SQL code in the front end app when all the database
engine needs to know is the parameter values for a commonly used query
or statement.

Most Access book I read have only JET Tables in the Back-End and the rest in
the Front-End. Perhaps, most authors are wrong and you are right.

Most SQL authors recommend to have database code in one place i.e. in
the database, rather than in one or more front end applications. There
are all sorts of reasons: maintenance (i.e. change code only in one
place), SQL injection attacks, etc. You need to read more widely than
the Access world, I feel.

 

[Van T. Dinh]

We know that the O.P. refer to Query as commonly known in Access!

IIRC, this is a newsgroup for questions about Access Queries, not questions
about the wider SQL world. Right?

I certainly didn't want to jump on you whether you are a newbie or not.
However, you obviously know JET & ADO well outside of Access but the O.P. is
not likely to have the same knowledge and I simply wanted to direct the O.P.
to the usual way of doing this in Access. If you felt that I "jumped" on
you, my apologies for giving the wrong impression.

Let me repeat that what I wrote applies to Access database applications and
this is a newsgroup for Access Queries and hence Access, not other SQL
database engines. I know what 2-tier, 3-tier, n-tier mean. I am aware of
the separation of GUI / business rules / data. I know what SQL authors
recommend. However, I am replying to questions in an *Access Queries
newsgroup* and I think it is more appropriate to give advices the Access way
and as applicable to Access unless the O.P. gives indications that he/she is
not using Access with JET as the database enigine. Like I pointed out in my
previous reply, there are advantages in database maintenance in Access to
have only Tables in the Back-End and that how most Access authors recommend.

Perhaps, you should read more Access books?

Let's leave it at that. Perhaps it will be more helpful to the O.P. if you
reply to his follow-up question rather than this sub-thread. The O. P.
certainly didn't know what to do with your advice from what he/she wrote in
the follow-up post.

I certainly see no point in prolonging this sub-thread ...

--
HTH
Van T. Dinh
MVP (Access)

 

[peregenem]

where i have to put this code ?

If you are using Access2003, use 'ANSI-92' mode and execute each
statement individually and in sequence using a Query object:

http://office.microsoft.com/en-us/assistance/HP030704831033.aspx

Otherwise, or alternatively, put it in VBA as I posted to the
'sub-thread' and I repost here

Sub test()
With CurrentProject.Connection
.Execute _
"CREATE TABLE customer (name VARCHAR(35) NOT NULL)"
.Execute _
"INSERT INTO customer VALUES ('chonny')"
.Execute _
"INSERT INTO customer VALUES ('Van')"
.Execute _
" CREATE PROCEDURE GetCustomers " & _
"customer_name VARCHAR(35) = NULL) " & _
"AS " & _
"select * from customer " & _
"where customer.name = " & _
"IIFcustomer_name IS NULL OR LENcustomer_name) = 0, " & _
" customer.name, :customer_name) "
Dim rs As Object

Set rs = .Execute("EXECUTE GetCustomers")
MsgBox rs.GetString

Set rs = .Execute("EXECUTE GetCustomers ''")
MsgBox rs.GetString

Set rs = .Execute("EXECUTE GetCustomers 'Van'")
MsgBox rs.GetString

End With
End Sub

 

[peregenem]

Perhaps it will be more helpful to the O.P. if you
reply to his follow-up question rather than this sub-thread.

I've now done this.

I known you want to end this discussion but please indulge me this
final point. You know the 'best practise' to which SQL authors
subscribe. Access/Jet is a subset of the wider SQL world. So why do you
think that 'best practise' in the wider world is not to be recommended
here merely because it is not 'common practise' in the Access world? I
don't mean that rhetorically; I'm interested in your reasoning for not
trying to change the Access world (you seem to be in strong position to
do so) e.g. imperative to conform, personal reputation, reluctance to
innovate...? Thank you.

 

[Guest]

10 x all of you
"(e-mail address removed)" напиÑа: