Sorting by the present (or a subsequent) month - microsoft.public.access.gettingstarted

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.

Use a different alias: Month is a reserved word. For a comprehensive list of names to avoid (as well as a link to a free utility to check your application for compliance), check what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "PlarfySoober" <PlarfySoober@discussions.microsoft.com> wrote in message news:94546304-3D15-4E36-81B4-8E84D4221C9B@microsoft.com... >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.

On Wed, 2 Dec 2009 13:45:01 -0800, PlarfySoober <PlarfySoober@discussions.microsoft.com> wrote: >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. -- John W. Vinson [MVP]

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" wrote: > On Wed, 2 Dec 2009 13:45:01 -0800, PlarfySoober > <PlarfySoober@discussions.microsoft.com> wrote: > > >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. > -- > > John W. Vinson [MVP] > . >

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 PlarfySoober wrote: > 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" wrote: > >> On Wed, 2 Dec 2009 13:45:01 -0800, PlarfySoober >> <PlarfySoober@discussions.microsoft.com> wrote: >> >>> 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. >> -- >> >> John W. Vinson [MVP] >> . >>

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" wrote: > 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 > > PlarfySoober wrote: > > 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" wrote: > > > >> On Wed, 2 Dec 2009 13:45:01 -0800, PlarfySoober > >> <PlarfySoober@discussions.microsoft.com> wrote: > >> > >>> 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. > >> -- > >> > >> John W. Vinson [MVP] > >> . > >> > . >

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. "John Spencer" wrote: > 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 > . >

The bracketing is wrong in the expression. Assuming that Employee List A is the table name it should be: Month([Employee List A].[Date_of_Birth])=Month(Date()) However, there seems to be something other than this not right. If Date_of_Birth is a date/time data type then the expression you used in your very first post, Month: Format([Date_of_Birth],"mm"), should not have raised an error even though Month is the name of a built in function and is best avoided as a column or other object name. The only thing I can think of to explain the behaviour would be if Date_of_Birth is itself a column returned by a query in which the original date/time value has been formatted by means of the Format function. This returns a text value, not a date, so this could explain the error. Even then both the Month and Format functions will operate on a string expression if it’s a valid date, so to raise an error the date would have to be expressed in a format unrecognized as a date by the functions. Otherwise no error should occur, so corruption must be suspected. You could try forcing it to a date/time value with: Month(CDate([Employee List A].[Date_of_Birth]))=Month(Date()) Ken Sheridan Stafford, England PlarfySoober wrote: >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. > >> Let's start over. >> >[quoted text clipped - 30 lines] >> University of Maryland Baltimore County >> . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200912/1

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 PlarfySoober wrote: > 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" wrote: > >> 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, (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. "John Spencer" wrote: > 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 > . >

Similiar Articles:

Crosstab query date sort - microsoft.public.access.queries ...
Crosstab Month Sort - microsoft ... At present I type a date ... date query criteria prompt ... Duplicate prompts in crosstab query and subsequent report ... ...

Equal list values. - microsoft.public.excel.programming ...
Sort the list by ColB and assign subsequent rows to each set. -- Jacob (MVP - Excel) "Darren ... The list of names isn't constant. it changes from month to month ...

Concatenate Field for Report/Query - microsoft.public.access.forms ...
You can do this by using Sorting and Grouping in your ... report that shows the Book Title with the subsequent ... query. ... data in the order I wish to present it to a ...

Adding a group header in Access 2002 - microsoft.public.access ...
Several months ago, John Spencer provided the ... to =1 and don't repeat the header on subsequent pages." I have successfully added a group header using the Sorting ...

Newsgroup Message Read/Unread Issue - microsoft.public.windows ...
For example: I have at present 2303 messages, 53 ... I like the simplicity of just sorting by Subject ... to be a reliable method of marking "Read" the subsequent ...

Group by fiscal months - microsoft.public.access.reports ...
Second way, is to use your present query as a source in the from cluase ... quality remarks, I would not use text months in the fiscal month query. If you attempt to sort on ...

Find the 10% value - microsoft.public.excel
... that contains data for a varying amount (each month ... Currently I'm doing this manually by sorting the column ... Present Value of equal payments of a future value ...

Access query to get monthly and quarterly report - microsoft ...
They will just present the data differently. for example: if month falls with in the start and end ... Sort Report by Month - microsoft.public.access.reports Access query ...

Report of sales by month based on a cross tab query - microsoft ...
CrossTab Error if data not present - microsoft.public ... Duplicate prompts in crosstab query and subsequent report ... ... and made it to white color. Inserted Group Sort on MONTH ...

changing sort sequence (like tab order) - microsoft.public.access ...
there is no well defined sort key such ... aging period for folders, (from 6 months ... changing sort ... up in the form if data is not present - microsoft ... changing sort ...

Sort data in a range or table - Excel - Office.com
Sort data in Excel 2007 by numbers, text such as alphabetical order, dates, colors ... Excel provides built-in, day-of-the-week and month-of-the year custom lists, and you ...

Care Plan Oversight – Part II - CPT Coding Guidelines & HCPCS ...
... agency (where the patient is not present). This includes reviewing initial or subsequent ... medical therapy, within a calendar month ... This allows the sort of flexibility ...

Create a grouped or summary report - Access - Office.com
... to group by actual value (Normal), Year, Quarter, Month ... If the Group, Sort, and Total pane is open, you can see ... levels, Access adds group footers (if not already present ...

Present Value - Homework Help from BrainMass
Present value of an ordinary annuity. For which of the following transactions ... A capital lease is entered into with the initial lease payment due one month subsequent ...

Accounting Final Flashcards - Flashcard Machine - Create, Study ...
A company that pays six months' rent in advance at the ... The present value of an annuity is always less than the ... value factors have been provided to answer the subsequent ...

7/16/2012 12:29:23 AM