ms access - query to show Active employees for a given time frame -

i little new building have come long way.

i have built db using access 2007. have table shows employees info:

lname   fname   status   hiredate   termdate 

(status: either inactive (potential hires), active or terminated)

i can run query show me employees hire date or run 1 show term dates.

we have query give count of how many drivers still there within given month.

say joe smith hired on 01/01/2008 , terminated on 05/15/2011. if ran report in 2011 on may 31st how need build query show employee being there in month of may?

i have used >=date() , others. use between #05/01/2011# , #06/01/2011# in criteria under termdate if there not date there, nothing shows up. have dropped down line , added "null" , still nothing or employees still there , ones terminated before dates. i'm not sure doing wrong.

i'm unsure logic filter criteria on one. think goal identify drivers on staff during part of may 2011. best guess need @ least 2 conditions identify them.

• hiredate prior june 1, 2011
• termdate either null or >= may 1, 2011

if conditions sufficient, sql easy.

select e.lname, e.fname employees_info e     e.hiredate < #2011-6-1#     ,     (         e.termdate null         or         e.termdate >= #2011-5-1#     ); 

it sounds you're building query in design view ... , helpful feature. however, it's difficult describe how build query in design view. suggest create new query, switch sql view , paste in sql text. replace employees_info actual table name, , fix field names misspelled.

if query runs without error, can flip , forth between design , sql view, make change in one, , examine how represented in other view.

the sql doesn't have formatted way wrote it. chose way in hopes make where logic clear. , if make changes query design view, access reformat sql sees fit. however, formatting change should not break query.

i used yyyy-m-d format literal date values. format avoids possible confusion on parts represent day , month, such whether #05-01-2011# intended represent may 1st or jan 5th. however, when alter query, access may change them mm-dd-yyyy format. (sometimes "helpful" impulses annoying.)

i'm puzzled 1 point. seems have 1 record per employee. if so, , employee can leave reason , re-hired later, difficult capture different employment terms in single record. if you're facing situation, may need revise table designs.

if misinterpreted data, please show brief data sample, , output want query based on sample. luck this.