Form and Query

[Guest]

I have what I hope will be a simple question. I have a form that allows me to
run a report based upon one of two criteria. The two fields are these

[Employee] and [CertEXPdate]

How do I word my selection criteria in qry so that if the employee field on
my form is blank I get the records for the date I put in [certexpdate] field
and if the certexpdate field is blank I get the records for that employee?

 

[Guest]

Try something like

Select * From TableName Where
[Employee] Like Nz(Forms![FormName]![Employee],"*") And [CertEXPdate] Like
Nz(Forms![FormName]![CertEXPdate],"*")

The Nz will replace the Null (blank field in the form) with *, and the like
with * will return all records

 

[John Spencer]

If you are using the query grid (design view) then enter the following as
criteria. WARNING: Access will reformat the Criteria when you save and
close. This works to return records even if nulls are in the fields you are
checking.

Field: Employee
Criteria(1): Forms![yourForm]![YourEmployeeControl] OR
Forms![yourForm]![YourEmployeeControl] is Null

Field: CertExpDate
Criteria(1): Forms![yourForm]![YourExpControl] OR
Forms![yourForm]![YourExpControl] is Null


In the SQL window that will look like
SELECT ...
FROM [Your Table]
WHERE (Employee = Forms![yourForm]![YourEmployeeControl] OR
Forms![yourForm]![YourEmployeeControl] is Null)
AND (CertExpDate= Forms![yourForm]![YourExpControl] OR
Forms![yourForm]![YourExpControl] is Null)

Until Access gets a chance to rearrange it.

Of course if Employee and CertExpDate always contain values you can use.

WHERE Employee Like NZ(Forms![yourForm]![YourEmployeeControl],"*")
AND CertExpDate=
NZ(Forms![yourForm]![YourExpControl],[CertExpDate])


I would avoid using CertExpDate LIKE
(NZ(Forms![yourForm]![YourExpControl],"*") if CertExpDate is a date field.
Using
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Wei Lu [MSFT]]

Hi Jason,

How is everything going? Please feel free to let me know if you need any
assistance.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.