Optional Parameter for Querydef with Default Built In

[Marc DVer]

I am trying to create a query that can be loaded as a querydef object
but not having to assign values to the parameters if I don't want to.

Normally when using a parameter query in VBA my code would go something
like this:

dim qry as dao.querydef
set qry = currentdb.querydefs("myquery")
qry.parameters("Par1") = "blah"

What I would like to do is build into the query a value that would be
used if I don't manually set the value of the parameter. In this case,
Par1 would be set to "blahblah" unless I specifically set the parameter
to "blah". I know I can somewhat kludge this using a form but I would
like to keep my queries as self-sufficient as possible for reuse in
other forms/reports/modules when the query is called in those places.

This is the criteria I already set for this:
like iif(isnull([Par1]),"*",[Par1])

In this case, if I do not enter anything into the parameter, or I put in
the asterisk I get all the records, while if I put in the full text of
the parameter I get only the record matching that parameter. However,
this still requires me to set the parameter, either in code or manually
when I run the query from the query gui.

A workaround, which in this case is simple, but goes against the grain
anyway, looks like this:

Public Function setQueryDefaults(qryName As String) As DAO.QueryDef
Dim qry As DAO.QueryDef
Set qry = CurrentDb.QueryDefs(qryName)
Dim i As Integer
For i = 0 To qry.Parameters.Count - 1
qry.Parameters(i) = Null
Next i
Set setQueryDefaults = qry
End Function

Of course it can be expanded to handle querydef object initialization
parameters (e.g., dbOpenDynaset), but this would just add to the
overhead to the program. Also, it assumes that the parameters on the
queries are properly set to accept null values.

Of course migrating from such this setup to one in which default
parameters are supported in the desired fashion would be somewhat of a
nightmare, assuming I rewrite the queries for the next database engine.

Marc

[PC Datasheet]

You can do something like this ---
dim qry as dao.querydef
dim param as parameter
dim SQLStrBlah As String
dim SQLStrBlahBlah As String
set SQLStrBlah = "Select ..... Where..."
set SQLStrBlahBlah = "Select ..... Where..."
set qry = currentdb.querydefs("myquery")
If <Whatever > Then
qry.sql = SQLStrBlah
Else
qry.sql = SQLStrBlah
End If
For each param in qry.parameters
param.value = EVAL(param.name)
Net param
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!
"Marc DVer" <ma**@not.in.use> wrote in message
news:Xs*******************@fe10.lga...

I am trying to create a query that can be loaded as a querydef object but
not having to assign values to the parameters if I don't want to.

Normally when using a parameter query in VBA my code would go something
like this:

dim qry as dao.querydef
set qry = currentdb.querydefs("myquery")
qry.parameters("Par1") = "blah"

What I would like to do is build into the query a value that would be used
if I don't manually set the value of the parameter. In this case, Par1
would be set to "blahblah" unless I specifically set the parameter to
"blah". I know I can somewhat kludge this using a form but I would like
to keep my queries as self-sufficient as possible for reuse in other
forms/reports/modules when the query is called in those places.

This is the criteria I already set for this:
like iif(isnull([Par1]),"*",[Par1])

In this case, if I do not enter anything into the parameter, or I put in
the asterisk I get all the records, while if I put in the full text of the
parameter I get only the record matching that parameter. However, this
still requires me to set the parameter, either in code or manually when I
run the query from the query gui.

A workaround, which in this case is simple, but goes against the grain
anyway, looks like this:

Public Function setQueryDefaults(qryName As String) As DAO.QueryDef
Dim qry As DAO.QueryDef
Set qry = CurrentDb.QueryDefs(qryName)
Dim i As Integer
For i = 0 To qry.Parameters.Count - 1
qry.Parameters(i) = Null
Next i
Set setQueryDefaults = qry
End Function

Of course it can be expanded to handle querydef object initialization
parameters (e.g., dbOpenDynaset), but this would just add to the overhead
to the program. Also, it assumes that the parameters on the queries are
properly set to accept null values.

Of course migrating from such this setup to one in which default
parameters are supported in the desired fashion would be somewhat of a
nightmare, assuming I rewrite the queries for the next database engine.

Marc

[StopThisAdvertising]

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:t0****************@newsread1.news.atl.earthli nk.net...

<snipped the message and all the advertising stuff>

To the OP: Beware of this guy!!

Steve just does *not* care about the newsgroups. He has *no ethics at all*.
Steve *only* cares about making *money*, and he acts as if the groups are his private hunting ground.

-- He abuses this group and others for job-hunting and advertising over and over again
-- He is insulting lots of people here when they ask him to stop this
-- He posted as Steve, Ron, Tom, Rachel, Kathy, Kristine, Heather and ??? while asking questions
(the latest 'star's': 'Access Resource' and Tom no***@email.com and Andy)
-- He tries to sell a CD ($125,--) with FREE code he gathered from these groups here
-- There even has been a 'Scam-alert' about him which has been explained recently in the thread 'To all':
http://groups.google.com/group/comp....954261f9?hl=en
-- Also recently it became clear that he has been spamming innocent people asking questions:
http://groups.google.com/group/comp....3e5f58ad?hl=en

So why would ANYBODY ever trust a person like him and hire him?
************************************************** ******

Explanation and more links on this answer:
http://home.tiscali.nl/arracom/stopsteve.html

Arno R

[Marc DVer]

What is the point of opening a query object using an already existing
query and then changing the sql property? If I understand Access
correctly, when you have a compiled query Access will optimize the way
it runs the query as it is run more. If you change the sql property,
which basically means you are erasing the existing sql statement and
putting your own in, the optimization information will be erased as it
is no longer valid, hence defeating the purpose of using a compiled
query. If for some reason I need to put in my own sql statement I don't
need to attach a query to the querydef object, but can put in my own
text in th sql property.

If the above is true, then this really doesn't help.

Marc
PC Datasheet wrote:

You can do something like this ---
dim qry as dao.querydef
dim param as parameter
dim SQLStrBlah As String
dim SQLStrBlahBlah As String
set SQLStrBlah = "Select ..... Where..."
set SQLStrBlahBlah = "Select ..... Where..."
set qry = currentdb.querydefs("myquery")
If <Whatever > Then
qry.sql = SQLStrBlah
Else
qry.sql = SQLStrBlah
End If
For each param in qry.parameters
param.value = EVAL(param.name)
Net param

[lylefair]

The above may be true sometimes, and not so true others.

In JET 3.5 this was true:

(from Jet Database Engine ProgrammersGuide)

"Parameter queries can only be implemented by using a stored query.
Because stored queries have a precompiled query plan, parameter queries
that contain parameters on indexed fields may not execute efficiently.
Because the query engine does not know the values to be passed in a
parameter in advance, it can only guess as to the most efficient query
plan. In some instances, you can substantially improve performance by
replacing a stored parameter query with a temporary query. To do this,
create the SQL string in code and pass it to the Database object's
OpenRecordset method or Execute method"

Is it still true in JET 4.0? I don't know.

I do know that the notion that "compiled" queries are noticeably faster
than "temporary" queries is not something that I can attest to.

[kaniest]

lylefair <ly***********@aim.com> wrote:

The above may be true sometimes, and not so true others.

In JET 3.5 this was true:

(from Jet Database Engine ProgrammersGuide)

"Parameter queries can only be implemented by using a stored query.
Because stored queries have a precompiled query plan, parameter
queries that contain parameters on indexed fields may not execute
efficiently. Because the query engine does not know the values to be
passed in a parameter in advance, it can only guess as to the most
efficient query plan. In some instances, you can substantially
improve performance by replacing a stored parameter query with a
temporary query. To do this, create the SQL string in code and pass
it to the Database object's OpenRecordset method or Execute method"

Is it still true in JET 4.0? I don't know.

I do know that the notion that "compiled" queries are noticeably
faster than "temporary" queries is not something that I can attest to.

With CurrentDb().CreateQueryDef("", _
"PARAMETERS x DateTime; UPDATE ... x ...;")
For Each i In x 'process all values in some paramarray x()
.Parameters!x.Value = i
.Execute
Next
End With

Parameters, temporary. Better? Déjà vu?

--
Paul

[lylefair]

I've not seen this way of doing temp queries/parameters before; looks
great (assuming no gotchas).

[kaniest]

lylefair <ly***********@aim.com> wrote:

I've not seen this way of doing temp queries/parameters before; looks
great (assuming no gotchas).

Hi, google for parametricset.
--
Paul

[Marc DVer]

kaniest wrote:

lylefair <ly***********@aim.com> wrote:
<snip>
With CurrentDb().CreateQueryDef("", _
"PARAMETERS x DateTime; UPDATE ... x ...;")
For Each i In x 'process all values in some paramarray x()
.Parameters!x.Value = i
.Execute
Next
End With

Parameters, temporary. Better? Déjà vu?

Perhaps I was a bit to vague regarding my question. When I say
"optional parameter", I mean that the parameter need not be set at all
in code. My workaround merely iterates through all the parameters and
sets them to null. If a query has alot of parameters, there could be a
performance hit. The whole point of an optional parameter is that I can
either use it or not. This is just like optional parameters in vba
functions. In vba functions the code still has to handle the existence
or not of the optional parameter. This should be the same with a
parameter query.

Also, maybe someone could clarify the whole point of parameters in
temporary queries. Since these don't get compiled at any point you
would probably have better performance creating a query string
containing all the criteria built in and then executing the query using
the whole string. I can see that if a temporary query is being reused
it might be a bit convenient to create a temporary parameter query and
pass the parameters each time the query is executed. Maybe someone can
comment on how this would perform vs. reconstructing the query string
each time.

As I mentioned in my first post one of the reasons for my madness is to
allow for easy conversion to a backend with minimal code changes. Using
queries that approximate those on other database engines would allow for
upgrading without having to code the application from scratch.

Marc

[kaniest]

Marc DVer <ma**@not.in.use> wrote:

kaniest wrote:

Parameters, temporary. Better? Déjà vu?

This hinted at some point in this thread being discussed before.
As I mentioned in my first post one of the reasons for my madness is
to allow for easy conversion to a backend with minimal code changes. Using
queries that approximate those on other database engines would
allow for upgrading without having to code the application from
scratch.

So, since there's no common sql construct for optional parameters,
it must be handled in the calling code anyway. Or?

--
Paul

[Some Bloke]

Marc DVer wrote:

What is the point of opening a query object using an already existing
query and then changing the sql property?

It's no use asking "Steve" to explain anything, he isn't capable. Once
he's got his advert into a thread it's the last you see of him.

[PC Datasheet]

Why are you using a fake name, Keith? Arno R would stick up for you.

There was no need to respond. Lyle and Paul answered Marc's question. All
you did was pollute the newsgroup with worthless garbage!!!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!


"Some Bloke" <he**@there.com> wrote in message
news:dl**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

Marc DVer wrote:

What is the point of opening a query object using an already existing
query and then changing the sql property?

It's no use asking "Steve" to explain anything, he isn't capable. Once
he's got his advert into a thread it's the last you see of him.

[StopThisAdvertising]

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:tF****************@newsread3.news.atl.earthli nk.net...

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

In some threads Steve needs/wants/choses to advertise more shit... ;-)

Arno R

[Keith W]

"PC Datasheet" <no****@nospam.spam> wrote in message
news:tF****************@newsread3.news.atl.earthli nk.net...

Why are you using a fake name, Keith? Arno R would stick up for you.

Because that was posted from home and that's the handle I now use from
there. Trust me, I do not need to hide from the likes of you. For the
pollution and wothless garbage, read on.
If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

[PC Datasheet]

So all the times in the past and including this post where we see Keith W,
you are posting from work on your employer's time?????????

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!


"Keith W" <he**@there.com> wrote in message
news:43**********@glkas0286.greenlnk.net...

"PC Datasheet" <no****@nospam.spam> wrote in message
news:tF****************@newsread3.news.atl.earthli nk.net...

Why are you using a fake name, Keith? Arno R would stick up for you.

Because that was posted from home and that's the handle I now use from
there. Trust me, I do not need to hide from the likes of you. For the
pollution and wothless garbage, read on.

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling?
Need
room reservations scheduling? Need employee work scheduling? Contact me!

[Keith W]

"PC Datasheet" <no****@nospam.spam> wrote in message
news:ai****************@newsread2.news.atl.earthli nk.net...

So all the times in the past and including this post where we see Keith W,
you are posting from work on your employer's time?????????

What makes you think that ??!!!????!!!!!??111!555745!!!!FFF??

?

[StopThisAdvertising]

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:ai****************@newsread2.news.atl.earthli nk.net...

<snipped rubbish>

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
We don't see ANY of these users here to defend you or support you...
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

Need to advertise like a maniac? Contact Steve!

Arno R

[PC Datasheet]

If you post by a pseudoname from home, WHERE DO YOU post by Keith W from????

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!


"Keith W" <he**@there.com> wrote in message
news:43**********@glkas0286.greenlnk.net...

"PC Datasheet" <no****@nospam.spam> wrote in message
news:ai****************@newsread2.news.atl.earthli nk.net...

So all the times in the past and including this post where we see Keith
W, you are posting from work on your employer's time?????????

What makes you think that ??!!!????!!!!!??111!555745!!!!FFF??

?

[StopThisAdvertising]

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:JT****************@newsread2.news.atl.earthli nk.net...

If you post by a pseudoname from home, WHERE DO YOU post by Keith W from????

Is this any of *your* business Steve??, or Ron, Tom, Rachel, Kathy, Kristine, Heather and ???

<snipped all the bloody advertising>

Arno R

[jimfortune]

PC Datasheet wrote:

So all the times in the past and including this post where we see Keith W,
you are posting from work on your employer's time?????????

If you consider that unprofessional behavior please advise the rest of
us in this NG of an effective strategy for dealing with such scofflaws
in the future.

James A. Fortune

[John Marshall, MVP]

<ji********@compumarc.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

If you consider that unprofessional behavior please advise the rest of
us in this NG of an effective strategy for dealing with such scofflaws
in the future.

James A. Fortune

First, you should not use such an obvious fake name like "James Fortune".
James is obviously a fake name.Try something like "Mac Spreadsheet". The
"Mac" adds a bit of ethnic authenticity to the name.

Second, you should not engage in a battle of wits with an unarmed opponent.
;-)

John... Visio MVP

[Keith W]

"PC Datasheet" <no****@nospam.spam> wrote in message
news:JT****************@newsread2.news.atl.earthli nk.net...

If you post by a pseudoname from home, WHERE DO YOU post by Keith W
from????

It is indeed none of your business and it beggars belief why anyone would be
interested.