Seeking help on a query - novice user - looking for best practice to use in designing a query

[shell159]

I am a novice user of Access. My last database class was in 1999 and it was using Paradox 5.0, not Access, so forgive my simplistic nature. I have tried to search for an answer myself, but I am beginning to think I may be over my head. I am working on a database for my employer to track employee training hours. I currently have 3 tables (not including my switchboard).

One table is a course list table that tracks the class information and contains the following fields.
ID (Auto Number & PK)
Course Title
7 fields that track break down of course hours
Total Training Hours (calculated based on adding 6 of the 7 hour fields)

The 2nd table tracks employees and contains the following fields:
Employee ID (PK)
First Name
Last Name
Employee Name (Calculated to combine first and last)

My third table is to actually track who took which class and when. It has the following fields.
ID (auto number & PK)
Course Date


Course Title FK
Employee ID FK

I am trying to design a query that would prompt the user to input a date range and input an employee id or leave either on blank to see all records. I can get a query to work with the date ranger or leave blank for all. I can get a query to work with inputting an employee id or leave blank to show all. I can't get both to work a the same time. I can get the input boxes for both but the results aren't properly filtered.

For example, if I put in a date range and an ID it seems to work, but if I leave one of the two blank to see all records it breaks. A date range entered without an employee id returns everything, not just the dates specified.

I am afraid that my logic is flawed and that I may need something other than a simple query to accomplish this but I don't know how. Any help would be appreciated.

[CJ_London]

what is the query you are using at the moment - my guess is you need to include an 'is null' i.e.

myfield = thiscontrol or thiscontrol is null

[shell159]

Attached is a picture of my query box. I was afraid that I might not state it exactly right typing it out. I am using parameters for the Employee ID with Is Null and Is Not Null. I have a similar parameter query for Course Date on another form that works with specifying a single date. I couldn't find how to do the same thing for a date range so I was using Course Date = Between [Enter Start Date] And [Enter End Date].

Thanks for your help!

[CJ_London]

I might not state it exactly right typing it out

no need to type it, go to the sql view and copy and paste

not sure what you query is intended to do but at the moment it will return records between start and end date and you don't enter an employee id (it will be null) or you enter an employee id

if you want it to return between dates and either all (if no employee id entered) or for a single employee, try repeating your between criteria on the next line.

The other way is to remove the [Enter Employee..] column and the second row of the criteria and put in the first row

nz([Enter Employee],[Employee ID])

[shell159]

THANK YOU SO MUCH! It doesn't let me leave the dates blank to see all training for all employees but I can just build that as a separate report. At least I can filter what I have by training periods now. I can't believe it was that simple, but part of me knew it had to be. Thank you!!!!!

[CJ_London]

It doesn't let me leave the dates blank to see all training for all employees

you can apply the same principle

between nz([Enter Start],[Course Date]) and nz([Enter End],[Course Date])

[shell159]

That is exactly what I needed. I switched all of my parameter queries to the nz functions you suggested. I did not know that function existed! Thank you so much for your help!

[June7]

Might find this of interest http://allenbrowne.com/QueryPerfIssue.html