query..

[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?

 

[Dirk Goldgar]

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?

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));

 

[Faith]

Thanks!
Fatih

-----Original Message-----

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?

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)


.