How to get rows when only querying on the month of a birth date

Hi All

This is a good one that's baked my noodle!!

I have a standard table with lets say 2 cols:

NAME  string
DOB     date

Example rows may be:

NAME                       DOB
Gary Barlow               24-3-2002
John Barlow               24-11-2001
Bob Barlow               7-7-1999
Bill Barlow                 6-3-1999
etc

I need to build a query that will get me all the rows back that were born in
say March (3), which means the above would bring back Gary and Bill even
though the birth day and year might be different - is this possible??!?!?

Thanks



0
Blackberry
11/11/2007 2:24:12 PM
access 16762 articles. 3 followers. Follow

2 Replies
537 Views

Similar Articles

[PageSpeed] 28

"Blackberry" <info@NoSpamIt.com> wrote in
news:e4OHX6GJIHA.5400@TK2MSFTNGP04.phx.gbl: 

> Hi All
> 
> This is a good one that's baked my noodle!!
> 
> I have a standard table with lets say 2 cols:
> 
> NAME  string
> DOB     date
> 
> Example rows may be:
> 
> NAME                       DOB
> Gary Barlow               24-3-2002
> John Barlow               24-11-2001
> Bob Barlow               7-7-1999
> Bill Barlow                 6-3-1999
> etc
> 
> I need to build a query that will get me all the rows back that
> were born in say March (3), which means the above would bring back
> Gary and Bill even though the birth day and year might be
> different - is this possible??!?!? 
> 
> Thanks
> 
Sure, and easy too. Use the month() function to extract that part of 
the date and set your criteria aginst this calculated field.

Paste this into the SQL view of the query builder and switch back to 
design view to see how it looks. be sure to change [standard table] 
to the real name of your table. 

SELECT [name], year([DOB]) as Birthyear FROM [standard table] WHERE 
Month([DOB]) = 3

I'd also change your name field to something more meaningful, such 
as ClientName, because name is a reserved word in Access and Access 
gets confused during some macros and code, between what you've 
called name and the program designers called name.


-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
11/11/2007 2:38:49 PM
Genius!!

"Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message 
news:Xns99E56AC4D5CEFBQuintal@66.150.105.47...
"Blackberry" <info@NoSpamIt.com> wrote in
news:e4OHX6GJIHA.5400@TK2MSFTNGP04.phx.gbl:

> Hi All
>
> This is a good one that's baked my noodle!!
>
> I have a standard table with lets say 2 cols:
>
> NAME  string
> DOB     date
>
> Example rows may be:
>
> NAME                       DOB
> Gary Barlow               24-3-2002
> John Barlow               24-11-2001
> Bob Barlow               7-7-1999
> Bill Barlow                 6-3-1999
> etc
>
> I need to build a query that will get me all the rows back that
> were born in say March (3), which means the above would bring back
> Gary and Bill even though the birth day and year might be
> different - is this possible??!?!?
>
> Thanks
>
Sure, and easy too. Use the month() function to extract that part of
the date and set your criteria aginst this calculated field.

Paste this into the SQL view of the query builder and switch back to
design view to see how it looks. be sure to change [standard table]
to the real name of your table.

SELECT [name], year([DOB]) as Birthyear FROM [standard table] WHERE
Month([DOB]) = 3

I'd also change your name field to something more meaningful, such
as ClientName, because name is a reserved word in Access and Access
gets confused during some macros and code, between what you've
called name and the program designers called name.


-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com


0
Blackberry
11/11/2007 4:15:49 PM
Reply:

Similar Artilces:

Converting and sorting US dates into European format?
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C5F7E2.A79C38F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable XPP SP2 Excel 2003 SP2 Hello, I have a long list of dates in US date/time format, ie "11/16/2005 = 11:38:08", that needs to be sorted in date and time order. Preferably, I should also be able to convert this into the European = date/time format of "YYYY-mm-dd hh:mm:sec". How do you do this in the = simplest possible way in Excel? I was thinking that one should possibly first con...

Multiplying in a row
Hi I need to multiply a row of numbers by 30%, the row extends from A4 to W4. How do I do it. thanks Daniel You want A4:W4 to be 130% of their curretn values? Type 1.3 in a cell. Then, copy that cell. Select A4:W4. Go to Edit | Paste Special. Choose Multiply and click Okay. -- tj "Daniel - Sydney" wrote: > Hi > > I need to multiply a row of numbers by 30%, > the row extends from A4 to W4. > > How do I do it. > > thanks > > Daniel Thanks for your help Daniel "tjtjjtjt" wrote: > You want A4:W4 to be 130% of their curretn val...

Creating new row in other table
Dear All, Please teach me, how to make a new row/list in other table by entering number in other table. Sample, In the table A and in the field "Details", I put the number 2 and it automatically creates two rows in table B. Can this be done in MS Access? Thank you for your help. Regards, Maulwy Why? What business need are you attempting to solve with this approach? It's generally considered not very good database design to be creating 'empty' rows in a second table. Why do you want empty rows? And if the number you enter in TableA is a 'limit&...

issue with row source of listbox
Hi, I need help with the following issue. In the form, i have a listbox whose row source made up of two different tables. In one of the field i set it up as follows Expr1: IIf(Not IsNull([Subject_Name]) Or [Subject_Name]<>"",[Subject_Name],[OldSubject_Name]) I was trying to get it to show up in the listbox in the view mode but to no avail. Is it to do with the fact that it's not one of the fields exist in the table? How do i work around this? Thank you in advance On Mon, 11 Feb 2008 20:38:00 -0800, Associates <Associates@discussions.microsoft.com> wrote: &...

Max row's available is too small
Hi, I had two questions...I currently have Excel 97 and the maximum row' available is 65000 and some. If I upgrade to the newest version doe this increase? If not, what are some ways you guys go about getting around thi restriction... What i'm going to be doing is using an Add-In from an external sourc that lets me do certain SQL queries....but the data returned from thes queries may easily be larger than 65k rows.... Any ideas? Thanks, Fran -- Message posted from http://www.ExcelForum.com Frank, The maximum number of rows in all versions of Excel since 97 is 65536. There i...

text date to date format
I have column filled with: 'Apr 3 2005 4:37PM And I need to change it to: 04/03/05 4:37PM How do I get a textual date column into a sortable date/time column? I'm having problems because it sorts everything by alpha then numeric. For example, I have "Apr 15, 2005" before "Aug 10, 2003". I need to get these sorted by year, month, day...not alphabetically. Thanks in advance for your help! you are right: the filter is not a time but a alpha sort you may change the cell format or copy the value in an other cell and put the new format in the new column. ...

Coverting information in rows to colums
Information is in one column many rows. a1 1234 a2 henry a3 jones a4 43 melrose st a5 wallyvile need to transpose this information into seperate colums example column a 1234 column b henry column c jones etc this spreadsheet has over 300 address See the instructions for Vertical to Horizontal Addresses at: http://www.officearticles.com/excel/data_cleanup_tips_for_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "bbc1" <bbc1@discussions.microsoft.com> wrote in message news:E004F1FC-A626-4862-96E5-D44464D454FC@microsoft.com...

Missing underline and Date
I am new to Excel and was trying to type a word then do underline after that for a line to write on, but as soon as I click of the cell the line disappears. Why, and can I keep that from happening. Also I would like to be able to add a start date and the other dates automatically follow. Ex: If I type in April 13, 1994 for a Sunday the next day Monday would read April 14, 1994. Is this possible and how is it done in terms a newbie would understand. Don't understand the first part of your question, and the second part is none to clear also. But, as far as dates incrementing: Enter ...

More than 65000 Rows
Hello All, Sorry if this has been covered before - couldn;t find a thread when searching though.. Do you know if there's any way to increase the number of rows in a worksheet? Thanks in advance Chris ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ http://www.google.com/groups?num=50&hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&q=row++limit+group%3A*excel*&btnG=Google+Search The bottom line : No. HTH Paul ---------------------------...

Where do I get Visio 2003 electronics template?
On Wed, 4 Feb 2009 13:06:52 -0800, splendidbcpgm <splendidbcpgm@discussions.microsoft.com> wrote: >Where do I get Visio 2003 electronics template? Would you like to have a look at my http://www.electronics.sandrila.co.uk/ http://www.electrical.sandrila.co.uk/ http://www.electronics-packages.sandrila.co.uk/ -- Regards, Paul Herber, Sandrila Ltd. http://www.sandrila.co.uk/ ...

Forms and Reports from CrossTab queries
Hi All, I have read a few postings in this area, but they all seem to be very specific to particular situations. Can someone please guide me towards the best solution to my dilema? I have a form with two list boxes which contain many variables (answers to specific questions from a survey). When two different questions are slected I run a cross tab query resulting in a dymanic result of rows and columns based on the selections. I now want to display the result as a datasheet on my form and in a similar fashion as a report. Any help is, of course, greatly appreciated. Regards, John D...

Changing row height in a CListCtrl
Hello, I'm trying to change the row height of a list control by means of a slider. Currently, we're doing that by changing the font height. This works perfectly, except for one thing: The font actually changes as well (d'uh). For the items itself it's not really a huge issue, I'm using custom draw and can set the font height to a "fixed" value. But I can't do that for the headers. Is there perhaps another way to change the row height of a list control, or is changing the font height the only way to do it? Thanks, -- Maarten Kools ___________________...

how do I get old versions of modified files?
I made a mistake and modified a file, how do I get back the old version? Thanks From your backup, which, of course you take every evening !!! John G "Power Image" <Power Image@discussions.microsoft.com> wrote in message news:910DFC71-460A-4169-A56E-AB90D905E9C5@microsoft.com... >I made a mistake and modified a file, how do I get back the old version? >Thanks ...

Manufacturing BOM Where Used query
Winthin manufacturing there is a where-used inquiry window that will show all BOMs that a given item appears in. You can also expand that view to see what BOMs and item's parent is used in. Some of our BOMs are 10 layers deep. In our environment we need to know which category of finished goods each purchased item is used in. Currently we maintain this manually and store the info in an inventory user-defined field. Over time as the number of our finished goods and purchased parts has increased this has become an unmangeable process. I need to find a way to query the BOM tables a...

Can't get out of selection? Assistance would be most appreciated...
Perhaps I have done something inadvertently. I am in an Excel workbook and I appear to be in the middle of selecting a block of cells. Everytime I move the cursor, the cell selection area changes. I can't Esc out, or use the Enter key. Mousing over the menu, the menu does not respond. Rather silly really, but I desperately need to use this workbook and nothing seems to function. Even when the cursor is moving over the menu items, the cells selected in the workbook area still seems to be changing. I can't use the menu - the arrow keys don't work. Does anyone know how to &quo...

Create field from append query based on linked table name
Here's the setup: Two linked tables called 'PHD' and 'XANS' bring in daily data from two CSV files. A union table-query puts the common data in both into the same name fields. This table-query is called 'SOLS_DATA_MERGE'. I then created a new table called 'SOLS_MAIN' and I ran an append query called 'SOLS_DATA_APPEND' to append the data in the table-query, 'SOLS_DATA_MERGE' into the new table, 'SOLS_MAIN'. The main reason for this was so that I could assign my data a primary key. Even though I have achieved my goal of merging the da...

Display No. of Rows in Status Bar
Hello: I'm using Excel 2003. Up until recently, whenever I apply an autofilter, my status bar would display the number of rows returned, e.g.: "25 of 100 records found". For some reason it has stopped doing that, and now just says "Filter Mode". I right clicked on the status bar, and chose "Count". It didn't help. Right now I have it on "None", which is what it was on when the row count was displaying before. I don't believe I turned anything off, but I'd sure like to have that feature back. OBTW: I had this problem a mont...

Typing the date into Excel
Hi, and thanks in advance. I've recently updated from Excel 2003 to Excel 2007. I'm having a problem with entering dates: (1) I enter a date, say: 5/17/2009 or 5-17-2009 (2) The cell displays 0.000146 or -2021. (3) I format the cells as "date". (4) The cell displays as 1900/01/00 or ##### (indefinite) (5) The formula bar displays =5/17/2009 or =5-17-2009 (6) It doesn't matter if I format the cells as dates in advance or after entering the date. I'm guessing that there is a setup option somewhere that is wrong, but I can't find it. The documentation, and my ...

Date & Control Button Focus
I have a form that has the calendar in it and 3 buttons that represent 1st, 2nd, and 3rd shift. We input up to 50 forms per day. The forms may be entered the next day. The calendar will always go to todays date because of the code Now() used. How do I make the date stay at the last date that was enetered? How do I make the 1st, 2nd, and 3rd shift buttons stay at last shift selected after data has been entered? This would allow the data entry person not to have to change date or shift if forms are grouped together. default values -- Hi Bill, "The calendar will always go to t...

To goup in each month on date column
I am using office 2007 and I have an excel sheet with the following Date OS 2/1/2009 200.30 2/1/2009 300.00 3/2/2009 400.00 5/2/2009 600.00 How I can group the excel to know outstanding in each month. To group January os and February os and so on.... Please help with thanks and Regards Try =SUMPRODUCT((TEXT(A2:A10,"mmyyyy")="022009")*(B2:B10)) with the date in cell C1 =SUMPRODUCT((TEXT(A2:A10,"mmyyyy")=TEXT(C1,"mmyyyy")*(B2:B10)) -- Jacob "pol" wrote: > I ...

Payment due date notification
I have the 2004 Standard Edition and I miss its ability to notify me (by popup or email) when a due date for a payment comes up. I usually don't run Money unless I need to enter something in it, so sometime I miss payments that I have to mail or handle outside of Money. Is there a version that provides such notification capability? Thanks, RP MoneyExpress was pretty broken right up until the removed it with M07. In M07 or M+ (can't recall which) they added back in a thing called Money Insights. I think it purports to do the same kinds of things but I'm not sure it will p...

CALCULATE DATE AND TIME
hOW DO i calculate the total time between two columns that contain a date and a time. Just subtract one from the other. If you want it in hours, format it as [h]:mm. If you want days and it will not exceed 31 days, format it as d hh:mm:ss. If it can be more than 31 days you will need something like =(INT(A1-B1)-(MOD(A1,1)>MOD(B1,1))&" days ")&TEXT(MOD(MOD(B1,1)-MOD(A1,1),1),"hh:mm") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Pam C" <PamC@discussions.microsoft.com> wrote in message news:78112373-65A9-450D-...

Archive thrown off by modification date
We are running Exchange 2003, and Outlook 2003 in cached mode. We are trying to impose storage limits on the user mailboxes, and encourage the users to use AutoArchive within Outlook to help keep their mailboxes clean. Some users have noticed that certain messages are not archiving as expected. In looking into this, we've discovered that many e-mail messages have a recent "Modified" date, and so they are not archived. For example, several messages from a month or two ago, have a modified date of yesterday. Many of these messages are modified at the exact same date and time. These...

Excel query
Hello I am trying to add a drop-down list to a cell with info like different brands of smoke detectors and the corresponding prices. When I select a brand the list disappears and leaves me with the brand and price in that cell. Is this possible? Judy Hi On empty sheet, enter the table (starting from A1) Brand1 Price1 Brand2 Price2 .... Let's assume your table fills the range A1:B20 Rename the sheet p.e. as "PriceTable" Select from menu Insert.Name.Define Into name field enter some name, p.e. PriceList Into source field, enter the formula: =OFFSET($A$1,,,COUNTIF($A...

When do uncashed electronic payments get re-credited to my account
I sent an electronic payment via Money 2004 to a friend, but before she received it we worked out some other compensation. When she received the $700 check, she tore it up instead of cashing it. However, even before she received the check, $700 has already been taken out of my account! When do I get this money back? I've been using e-payments through Microsoft Money for about three years, and this is the first time I've had this situation. I hadn't been aware 'til now that these paper checks that get sent out apparently take my money out immediately and move it to ...