Converting Date Field to Month

[Jeff Kaufman]

I am currently using Microsoft Access 2003 and I have a field that returns my
employees hire date in a date format (MM/DD/YYYY). I need to create a query
that will allow me to get information for only those employees that were
hired during a certain month. So for example, I would want to get everyone
that was hired in December. So I am looking for one of two things: either a
formula that will convert my current date into just the Month or a way to
write something into the crietria row so that it asks me for the month to
bring back.

I have the following query already together:

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail]
FROM Employee_Info_Qry;

Thanks

 

[Douglas J. Steele]

Add a computed field to your query:

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail], Month(Employee_Info_Qry.HireDate) AS HireMonth
FROM Employee_Info_Qry;

You can now enter the month number as a criteria.

If you'd rather enter the month name, use Format(Employee_Info_Qry.HireDate,
"mmm") to get Jan, Feb, Mar,..., or Format(Employee_Info_Qry.HireDate,
"mmmm") to get January, February, March,...

 

[BruceM]

You could create a new field based on HireDate:
HireMonth: Month([HireDate])
or to get the name of the month:
HireMonth: MonthName(Month([HireDate]))

You could then use [Enter Month] as the criteria for HireMonth, or you could
reference a control (combo box, for instance) on a form:
Forms!frmSelectMonth!cboSelectMonth

 

[fredg]

I am currently using Microsoft Access 2003 and I have a field that returns my
employees hire date in a date format (MM/DD/YYYY). I need to create a query
that will allow me to get information for only those employees that were
hired during a certain month. So for example, I would want to get everyone
that was hired in December. So I am looking for one of two things: either a
formula that will convert my current date into just the Month or a way to
write something into the crietria row so that it asks me for the month to
bring back.

I have the following query already together:

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail]
FROM Employee_Info_Qry;

Thanks

HireDate is a Date datatype field?

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail]
FROM Employee_Info_Qry
Where Month([HireDate]) = [Enter the Month number];

Enter a value, when prompted, of 1 to 12.

The above will return the month entered hires for all years.
If you meant just for a month of a specific year, then use:

Where Format([HireDate],"mm/yyyy") = [Enter the Month and Year as
mm/yyyy]

Enter, for example, 02/2008 to return records for February, 2008.

 

[Jeff Kaufman]

That worked great... Now, is there a way for me to have it calculate how many
years the employee of service someone has as of todays date? So I would be
adding another field Years of service...

Add a computed field to your query:

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail], Month(Employee_Info_Qry.HireDate) AS HireMonth
FROM Employee_Info_Qry;

You can now enter the month number as a criteria.

If you'd rather enter the month name, use Format(Employee_Info_Qry.HireDate,
"mmm") to get Jan, Feb, Mar,..., or Format(Employee_Info_Qry.HireDate,
"mmmm") to get January, February, March,...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am currently using Microsoft Access 2003 and I have a field that returns
my
employees hire date in a date format (MM/DD/YYYY). I need to create a
query
that will allow me to get information for only those employees that were
hired during a certain month. So for example, I would want to get
everyone
that was hired in December. So I am looking for one of two things: either
a
formula that will convert my current date into just the Month or a way to
write something into the crietria row so that it asks me for the month to
bring back.

I have the following query already together:

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail]
FROM Employee_Info_Qry;

Thanks

 

[Douglas J. Steele]

DateDiff("yyyy", HireDate, Date) - IIf(Format(Date, "mmdd") <
Format(HireDate, "mmdd"), 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That worked great... Now, is there a way for me to have it calculate how
many
years the employee of service someone has as of todays date? So I would
be
adding another field Years of service...

Add a computed field to your query:

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail], Month(Employee_Info_Qry.HireDate) AS
HireMonth
FROM Employee_Info_Qry;

You can now enter the month number as a criteria.

If you'd rather enter the month name, use
Format(Employee_Info_Qry.HireDate,
"mmm") to get Jan, Feb, Mar,..., or Format(Employee_Info_Qry.HireDate,
"mmmm") to get January, February, March,...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am currently using Microsoft Access 2003 and I have a field that
returns
my
employees hire date in a date format (MM/DD/YYYY). I need to create a
query
that will allow me to get information for only those employees that
were
hired during a certain month. So for example, I would want to get
everyone
that was hired in December. So I am looking for one of two things:
either
a
formula that will convert my current date into just the Month or a way
to
write something into the crietria row so that it asks me for the month
to
bring back.

I have the following query already together:

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail]
FROM Employee_Info_Qry;

Thanks

 

[Jeff Kaufman]

Okay.. .you will have to forgive me.. I am still "Green" with this and
learning more each day.. I can't figure out how to get that expression into
my query... Help Please

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail], Month(Employee_Info_Qry.HireDate) AS HireMonth,
Day(Employee_Info_Qry.HireDate) AS HireDay, Year(Employee_Info_Qry.HireDate)
AS HireYear
FROM Employee_Info_Qry;

DateDiff("yyyy", HireDate, Date) - IIf(Format(Date, "mmdd") <
Format(HireDate, "mmdd"), 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That worked great... Now, is there a way for me to have it calculate how
many
years the employee of service someone has as of todays date? So I would
be
adding another field Years of service...

Add a computed field to your query:

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail], Month(Employee_Info_Qry.HireDate) AS
HireMonth
FROM Employee_Info_Qry;

You can now enter the month number as a criteria.

If you'd rather enter the month name, use
Format(Employee_Info_Qry.HireDate,
"mmm") to get Jan, Feb, Mar,..., or Format(Employee_Info_Qry.HireDate,
"mmmm") to get January, February, March,...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am currently using Microsoft Access 2003 and I have a field that
returns
my
employees hire date in a date format (MM/DD/YYYY). I need to create a
query
that will allow me to get information for only those employees that
were
hired during a certain month. So for example, I would want to get
everyone
that was hired in December. So I am looking for one of two things:
either
a
formula that will convert my current date into just the Month or a way
to
write something into the crietria row so that it asks me for the month
to
bring back.

I have the following query already together:

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail]
FROM Employee_Info_Qry;

Thanks

 

[John W. Vinson]

Okay.. .you will have to forgive me.. I am still "Green" with this and
learning more each day.. I can't figure out how to get that expression into
my query... Help Please

SELECT Employee_Info_Qry.Manager, Employee_Info_Qry.TeamName,
Employee_Info_Qry.[Employee Name], Employee_Info_Qry.HireDate,
Employee_Info_Qry.[E-Mail], Month(Employee_Info_Qry.HireDate) AS HireMonth,
Day(Employee_Info_Qry.HireDate) AS HireDay, Year(Employee_Info_Qry.HireDate)
AS HireYear
FROM Employee_Info_Qry;

That IS a query. The language of queries is SQL; the query design grid is just
a tool to build SQL. You can open your existing query and select View... SQL
from the menu to see it (or copy and paste to a message), or you can copy and
paste this query into the SQL window.

Or, you can just type the Month(), Day() and Year() expressions as posted into
vacant Field cells in your query design grid.