query..

  • Thread starter Thread starter Fatih
  • Start date Start date
F

Fatih

Hi,
Desperately seeking answers...I've searched through the
help manual but nothing seems to help...
If want to set a query such that only one field has to be
filled in, e.g. First Name/Last Name, what is the
criteria that I need?
 
Fatih said:
Hi,
Desperately seeking answers...I've searched through the
help manual but nothing seems to help...
If want to set a query such that only one field has to be
filled in, e.g. First Name/Last Name, what is the
criteria that I need?
Click to expand...

The SQL gets long, because you have to also check for Null values in the
controls you want to use as criteria. Here's an example that requires
at least one of the two controls to be filled in:

SELECT tblEmployees.* FROM tblEmployees
WHERE ((FirstName=[Forms]![frmLookup]![txtFirstName]
Or [Forms]![frmLookup]![txtFirstName] Is Null)
AND (LastName=[Forms]![frmLookup]![txtLastName]
Or [Forms]![frmLookup]![txtLastName] Is Null)
AND (([Forms]![frmLookup]![txtFirstName] Is Not Null)
Or ([Forms]![frmLookup]![txtLastName] Is Not Null)));

Here's an example that removes that last restriction, so that if neither
control is filled in, all records are returned:

SELECT tblEmployees.* FROM tblEmployees
WHERE ((FirstName=[Forms]![frmLookup]![txtFirstName]
Or [Forms]![frmLookup]![txtFirstName] Is Null)
AND (LastName=[Forms]![frmLookup]![txtLastName]
Or [Forms]![frmLookup]![txtLastName] Is Null));
 
Thanks!
Fatih
-----Original Message-----
Fatih said:
Hi,
Desperately seeking answers...I've searched through the
help manual but nothing seems to help...
If want to set a query such that only one field has to be
filled in, e.g. First Name/Last Name, what is the
criteria that I need?
Click to expand...

The SQL gets long, because you have to also check for Null values in the
controls you want to use as criteria. Here's an example that requires
at least one of the two controls to be filled in:

SELECT tblEmployees.* FROM tblEmployees
WHERE ((FirstName=[Forms]![frmLookup]![txtFirstName]
Or [Forms]![frmLookup]![txtFirstName] Is Null)
AND (LastName=[Forms]![frmLookup]![txtLastName]
Or [Forms]![frmLookup]![txtLastName] Is Null)
AND (([Forms]![frmLookup]![txtFirstName] Is Not Null)
Or ([Forms]![frmLookup]![txtLastName] Is Not Null)));

Here's an example that removes that last restriction, so that if neither
control is filled in, all records are returned:

SELECT tblEmployees.* FROM tblEmployees
WHERE ((FirstName=[Forms]![frmLookup]![txtFirstName]
Or [Forms]![frmLookup]![txtFirstName] Is Null)
AND (LastName=[Forms]![frmLookup]![txtLastName]
Or [Forms]![frmLookup]![txtLastName] Is Null));

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Back
Top