Sorting by the present (or a subsequent) month

  • Thread starter Thread starter PlarfySoober
  • Start date Start date
P

PlarfySoober

I have a table with employees' names. I need to show birthdays (and hire
dates) somehow for the current or a subsequent month.

1. Is this best done by a query or by a report?
2. If by a query, I have tried Month: Format([Date_of_Birth],"mm")
in a separate column but get a "Data type mismatch" error.

The Date_of_Birth column is a date/time field.

My query has last_name, first_name and Date_of_Birth fields, and the above.

Thanks.
 
I have a table with employees' names. I need to show birthdays (and hire
dates) somehow for the current or a subsequent month.

1. Is this best done by a query or by a report?
2. If by a query, I have tried Month: Format([Date_of_Birth],"mm")
in a separate column but get a "Data type mismatch" error.

The Date_of_Birth column is a date/time field.

My query has last_name, first_name and Date_of_Birth fields, and the above.

Thanks.
Click to expand...

The simplest way is to calculate this year's birthday anniversary. In a vacant
Field cell put

HappyHappy: DateSerial(Year(Date()), Month([Date_Of_Birth]),
Day([Date_Of_Birth]))

You can then use a criterion on this field such as

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

to find this month's birthdays; you can sort by it, put other criteria on it,
etc.
 
John,

Thanks for your reply. I'm afraid I lack some of the sophistication that,
I'm ashamed to say, hasn't come my way yet. I used to use dBase, way back
when, but a lot has changed.

When you say to put the expression in an "empty field cell", you mean
somewhere to the right of the data I have in my table, right?

And should this expression exist in each record?

I assume that "HappyHappy" is what I would call a variable elsewhere. Also
right?

Thanks.

John W. Vinson said:
I have a table with employees' names. I need to show birthdays (and hire
dates) somehow for the current or a subsequent month.

1. Is this best done by a query or by a report?
2. If by a query, I have tried Month: Format([Date_of_Birth],"mm")
in a separate column but get a "Data type mismatch" error.

The Date_of_Birth column is a date/time field.

My query has last_name, first_name and Date_of_Birth fields, and the above.

Thanks.
Click to expand...

The simplest way is to calculate this year's birthday anniversary. In a vacant
Field cell put

HappyHappy: DateSerial(Year(Date()), Month([Date_Of_Birth]),
Day([Date_Of_Birth]))

You can then use a criterion on this field such as

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

to find this month's birthdays; you can sort by it, put other criteria on it,
etc.
Click to expand...
 
In query design view:
== In an empty "cell" on the field row enter
HappyHappy: DateSerial(Year(Date()), Month([Date_Of_Birth]),
Day([Date_Of_Birth]))
== You can uncheck the Show button for this field
== Enter in the criteria cell under this calculated field
BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

Another way to handle this for any one month
== In an empty "cell" on the field row enter
HappyHappy: Month([Date_Of_Birth])
== You can uncheck the Show button for this field
== Enter in the criteria cell under this calculated field, the number of the
month you want data returned on. For instance, 12 for December.

In both the above cases you will have to select which fields you want to
display in the results.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John,

Thanks for your reply. I'm afraid I lack some of the sophistication that,
I'm ashamed to say, hasn't come my way yet. I used to use dBase, way back
when, but a lot has changed.

When you say to put the expression in an "empty field cell", you mean
somewhere to the right of the data I have in my table, right?

And should this expression exist in each record?

I assume that "HappyHappy" is what I would call a variable elsewhere. Also
right?

Thanks.

John W. Vinson said:
I have a table with employees' names. I need to show birthdays (and hire
dates) somehow for the current or a subsequent month.

1. Is this best done by a query or by a report?
2. If by a query, I have tried Month: Format([Date_of_Birth],"mm")
in a separate column but get a "Data type mismatch" error.

The Date_of_Birth column is a date/time field.

My query has last_name, first_name and Date_of_Birth fields, and the above.

Thanks.
Click to expand...
The simplest way is to calculate this year's birthday anniversary. In a vacant
Field cell put

HappyHappy: DateSerial(Year(Date()), Month([Date_Of_Birth]),
Day([Date_Of_Birth]))

You can then use a criterion on this field such as

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

to find this month's birthdays; you can sort by it, put other criteria on it,
etc.
Click to expand...
Click to expand...
 
John,

Thanks for sticking with this. I'm sorry, the query still returns a "Data
Type Mismatch in Query Expression" error.

Here's what I have:

A table with names & vital information, including names and birthdates.

In Query Design View appears Employee_First, Employee_Last and Date_of_Birth
as fields to display.

In the next field I inserted

HappyHappy: HappyHappy:
DateSerial(Year(Date()),Month([Date_Of_Birth]),Day([Date_Of_Birth]))

and as criteria,

Between DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)

Don.

John Spencer said:
In query design view:
== In an empty "cell" on the field row enter
HappyHappy: DateSerial(Year(Date()), Month([Date_Of_Birth]),
Day([Date_Of_Birth]))
== You can uncheck the Show button for this field
== Enter in the criteria cell under this calculated field
BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

Another way to handle this for any one month
== In an empty "cell" on the field row enter
HappyHappy: Month([Date_Of_Birth])
== You can uncheck the Show button for this field
== Enter in the criteria cell under this calculated field, the number of the
month you want data returned on. For instance, 12 for December.

In both the above cases you will have to select which fields you want to
display in the results.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John,

Thanks for your reply. I'm afraid I lack some of the sophistication that,
I'm ashamed to say, hasn't come my way yet. I used to use dBase, way back
when, but a lot has changed.

When you say to put the expression in an "empty field cell", you mean
somewhere to the right of the data I have in my table, right?

And should this expression exist in each record?

I assume that "HappyHappy" is what I would call a variable elsewhere. Also
right?

Thanks.

John W. Vinson said:
On Wed, 2 Dec 2009 13:45:01 -0800, PlarfySoober

I have a table with employees' names. I need to show birthdays (and hire
dates) somehow for the current or a subsequent month.

1. Is this best done by a query or by a report?
2. If by a query, I have tried Month: Format([Date_of_Birth],"mm")
in a separate column but get a "Data type mismatch" error.

The Date_of_Birth column is a date/time field.

My query has last_name, first_name and Date_of_Birth fields, and the above.

Thanks.
The simplest way is to calculate this year's birthday anniversary. In a vacant
Field cell put

HappyHappy: DateSerial(Year(Date()), Month([Date_Of_Birth]),
Day([Date_Of_Birth]))

You can then use a criterion on this field such as

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

to find this month's birthdays; you can sort by it, put other criteria on it,
etc.
Click to expand...
Click to expand...
.
Click to expand...
 
Let's start over.

First do you want to filter the records to just a specific month? Filter means
to restrict the records returned to those that match some criteria. Sorting
means to order the records that are returned into some specific order.

If what you want is to filter the records:

== In a field box in the query enter
DateSerial(Year(Date()),Month([Date_Of_Birth]),Day([Date_Of_Birth]))
This will get automatically titled Expr1
== In the criteria box under this calculated field you can enter
Between DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)

If you just want a specific month you can use the following in place of the above:

== In a field box in the query enter
Month([Date_Of_Birth])
This will get automatically titled Expr1
== In the criteria box under this calculated field you can enter
Month(Date())
That will give you the current month. If you want a specific month enter
the month number instead of the expression.

If you still get data mismatch errors then you need to check the data type of
the Date_of_Birth field in the table. If it is something besides a DateTime
field that would explain the mismatch error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
John,

I'm afraid this is becoming quite hopeless. I have received several
suggestions as to how to do this, and uniformly, I get a "Data type mismatch"
error.

As for the choices you kindly gave me, I don't know the difference between
"filtering" the data and "just want a specific month," because to me I am
filtering by a specific month.

The first suggestion, filtering, gave me the standard data type mismatch.

The second, a new error: "Syntax error in query expression '((([Employee
List A],[Month(Date_of_Birth])])=Month(Date())))."

The Date_of_Birth field is without any doubt a datetime field.
 
In the query design view, you should have something like the following:

Field: Expr1: Month([Employee List A].[Date_of_Birth])
Table: <<BLANK>>
Criteria: Month(Date())

In SQL View the WHERE clause would have something like

Month([Employee List A].[Date_of_Birth])=Month(Date())


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John,

I'm afraid this is becoming quite hopeless. I have received several
suggestions as to how to do this, and uniformly, I get a "Data type mismatch"
error.

As for the choices you kindly gave me, I don't know the difference between
"filtering" the data and "just want a specific month," because to me I am
filtering by a specific month.

The first suggestion, filtering, gave me the standard data type mismatch.

The second, a new error: "Syntax error in query expression '((([Employee
List A],[Month(Date_of_Birth])])=Month(Date())))."

The Date_of_Birth field is without any doubt a datetime field.

John Spencer said:
Let's start over.

First do you want to filter the records to just a specific month? Filter means
to restrict the records returned to those that match some criteria. Sorting
means to order the records that are returned into some specific order.

If what you want is to filter the records:

== In a field box in the query enter
DateSerial(Year(Date()),Month([Date_Of_Birth]),Day([Date_Of_Birth]))
This will get automatically titled Expr1
== In the criteria box under this calculated field you can enter
Between DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)

If you just want a specific month you can use the following in place of the above:

== In a field box in the query enter
Month([Date_Of_Birth])
This will get automatically titled Expr1
== In the criteria box under this calculated field you can enter
Month(Date())
That will give you the current month. If you want a specific month enter
the month number instead of the expression.

If you still get data mismatch errors then you need to check the data type of
the Date_of_Birth field in the table. If it is something besides a DateTime
field that would explain the mismatch error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
.
Click to expand...
Click to expand...
 
John, (and others who kindly replied),

OK, here's what worked:

I created a Query with names, etc., and two expressions.
Expr1:Year([Hire_Date]) and Expr2: Month([Hire_Date]), and for the former set
criterion 2009 and for the latter, 9 (because I need people who have been
here 90 days). And it works, at least rudimentarily, and gives me some
breathing room to absorb the other ideas presented.

Thanks to all.

Don.
 
Back
Top