Need help in entering criteria in query

[cchahn]

I have a small and simple database I'm working with, so I imagine this is simple, but I can't get it to work. I created an eight field query based on a single table. In the first field "Employee" and the second field "Week Ending" I'm trying to enter criteria that will allow me to select a combination of all employees and all dates (I can get this one to work), or one employee for all dates, or one date for all employees. Each record consist of an employee name and a date plus other data. Every employee will have multiple records (actually one for every week). For example, I want to see all records for a particular date, or else I want to see all employee records for a particular date.

 

[dhookom]

My response would depend on if the query was used as the record source for a form or report. I don't generally provide a datasheet view of a table or query to users.

Are you attempting to filter a report or form?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cchahn]

I want to use the query to produce three different variations of the same report. All employees for all dates, one employee for all dates, or one date for all employees.

 

[dhookom]

I would add controls to a form:
cboEmployee to select an employee
txtDate to select a date

Then use code to open the report like:

  Dim strWhere as String
  strWhere = "1=1 "
  If Not IsNull(Me.cboEmployee) Then
    [green]'assuming a numeric field EmployeeID[/green]
    strWhere = strWhere & " And EmployeeID = " & Me.cboEmployee
  End If
  If Not IsNull(Me.txtDate) Then
    strWhere = strWhere & " And [Week Ending] = #" & Me.txtDate & "#"
  End If
  DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

This code makes assumptions about your employee field and data type as well as the name of your date field and report.

I generally provide controls for start and end dates.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cchahn]

Thanks for the help.