basic question

  • Thread starter Thread starter David
  • Start date Start date
D

David

I have a query set up with [enter name] as my criteria so
the user can input the criteria. How can I (in addition
to this input criteria, have the query include all
records if value is NULL because the user just hit
ENTER? Thank you for the assistance.
 
This question crops up all the time (understandably).

It is one of the reasons why parameters are not really very useful. You may
be able to work around the issue with:
LIKE [Enter name] & '*'
but that's inefficient, works properly with text fields only, and has
undesirable side effects (such as including "Johnstone" etc when you just
wanted "Johns".)

In general, Access works better if you can generate the SQL dymamically,
such as building a Filter string to apply to a form, reassigning a
RecordSource to an SQL statement, or using the WhereCondition of an
OpenReport action.
 
David said:
I have a query set up with [enter name] as my criteria so
the user can input the criteria. How can I (in addition
to this input criteria, have the query include all
records if value is NULL because the user just hit
ENTER? Thank you for the assistance.
Click to expand...

Hi David,

In addition to Allen's sage advise,
some use the following work around:

SELECT ...
FROM ...
WHERE ([somefield]=[enter name])
OR ([enter name] IS NULL);

This works well "for now" but...

- this technique breaks down when you
have "many" parameters because
Access can "rewrite your OR's" and
you can end up with an unexpected WHERE
clause losing your original logic.

- "pulling parameters from a form" will allow
you to test & verify what the user enters and
give meaningful feedback to your users.
(I don't mean the technique of substituting
"Forms!frmA!txtA" for [enter name] in
your Criteria line --
I mean what Allen has covered briefly)

Some good examples can be found here:
http://www.rogersaccesslibrary.com/


and a powerful example within that site
is Duane's DH Query By Form
(not an example I think you want to
start with though):

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Fo
rm'

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
I technically misspoke when I said:
you can end up with an unexpected WHERE
clause losing your original logic.
Click to expand...

The "logic" is preserved...
but the "clearity of the logic" can be "lost."

I have not experienced a "rewrite"
where the "logic was lost."

Gary Walter
Gary Walter said:
David said:
I have a query set up with [enter name] as my criteria so
the user can input the criteria. How can I (in addition
to this input criteria, have the query include all
records if value is NULL because the user just hit
ENTER? Thank you for the assistance.
Click to expand...

Hi David,

In addition to Allen's sage advise,
some use the following work around:

SELECT ...
FROM ...
WHERE ([somefield]=[enter name])
OR ([enter name] IS NULL);

This works well "for now" but...

- this technique breaks down when you
have "many" parameters because
Access can "rewrite your OR's" and
you can end up with an unexpected WHERE
clause losing your original logic.

- "pulling parameters from a form" will allow
you to test & verify what the user enters and
give meaningful feedback to your users.
(I don't mean the technique of substituting
"Forms!frmA!txtA" for [enter name] in
your Criteria line --
I mean what Allen has covered briefly)

Some good examples can be found here:
http://www.rogersaccesslibrary.com/


and a powerful example within that site
is Duane's DH Query By Form
(not an example I think you want to
start with though):

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Fo
rm'

Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
 
Back
Top