Date range criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am at a loss and new at this. I have a field called Hire Date (Date/Time) and I am trying to run a query that shows just the employees hired in a certian month
 
I would create a field on my query with monty(hiredate)
and on the criteria your month
the sql would be
SELECT table1.HIREDATE, Month([hiredate]) AS mo
FROM table1
WHERE (((Month([hiredate]))=3));

Hope this helps.
-----Original Message-----
I am at a loss and new at this. I have a field called
Click to expand...
Hire Date (Date/Time) and I am trying to run a query that
shows just the employees hired in a certian month
 
I am at a loss and new at this. I have a field called Hire Date (Date/Time) and I am trying to run a query that shows just the employees hired in a certian month
Click to expand...

.... in a certain year, I presume?

Try a criterion of

BETWEEN DateSerial(NZ([Enter year, leave blank for this year:],
Year(Date()), [Enter month number:], 1) AND DateSerial(NZ([Enter year,
leave blank for this year:] + 1, Year(Date()), [Enter month number:],
0)
 
I created a field in my table for month and year before I set up my query. EX: Jan, Feb. And each time a person is hired the date is include but also the month and year are singled out because I need this information to create a parameter query, one using month and the other using year. EX: In criteria of your query after you have created the field in your table, you should place this in criteria in the month field [Select Month]. Save and test. The query will prompt you to ask for the month and you type in the month exactly as you have it int he table and or form. I hope this helps.
 
Bonnie

Does this mean you have three fields in your table to store date
information? One stores the date, another stores the MonthName
(?MonthAbbreviation), and one stores the Year? If so, this is much more
work for whoever does the data entry, whoever builds the forms and reports,
and whoever builds the queries. You only need store the date, and use
date-related functions to return the Month() and Year() of that date.

By the way, if you have fields named "Date", "Month", and "Year", you will
confuse both yourself and Access -- Access considers these reserved words.
Change the name of the date field to something like "JoiningDate" (or
whatever is applicable). Again, drop the other two fields.

To build a parameter query looking for, say, a date in August (the 8th
month), you could:
add a new field to the query based on Month([YourDateField])
include a parameter something like [Enter Month Number, e.g. Jan. =
Click to expand...
1, Feb. = 2, ...]

For the year parameter, add a new field based on Year([YourDateField]) and
an appropriate parameter/prompt.

Good luck

Jeff Boyce
<Access MVP>

Bonnie said:
I created a field in my table for month and year before I set up my query.
Click to expand...
EX: Jan, Feb. And each time a person is hired the date is include but also
the month and year are singled out because I need this information to create
a parameter query, one using month and the other using year. EX: In
criteria of your query after you have created the field in your table, you
should place this in criteria in the month field [Select Month]. Save and
test. The query will prompt you to ask for the month and you type in the
month exactly as you have it int he table and or form. I hope this helps.
 
Back
Top