Query Date Help

I need to create a query that will do the following:

these are my fields: resign date, hire date

I need to subtract the resign date from hire date but I want it to return 
the answer as years, months & days

ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days

is there a way to use the DateDiff function to return years, monts & days?
0
Utf
3/20/2008 8:31:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
648 Views

Similar Articles

[PageSpeed] 59

Look at this link on "A More Complete DateDiff Function"

http://www.accessmvp.com/djsteele/Diff2Dates.html

-- 
Good Luck
BS"D


"aldunford" wrote:

> I need to create a query that will do the following:
> 
> these are my fields: resign date, hire date
> 
> I need to subtract the resign date from hire date but I want it to return 
> the answer as years, months & days
> 
> ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days
> 
> is there a way to use the DateDiff function to return years, monts & days?
0
Utf
3/20/2008 11:17:00 PM
This worked great thanks so much!! I have one more question.

Now that i have the format I need as 7 years 4 months & 19 days for my prior 
service. How can I substract that from new hire date of 11/17/1986 to get 
6/28/1979?

I appreciate all your time and help!!



"Ofer Cohen" wrote:

> Look at this link on "A More Complete DateDiff Function"
> 
> http://www.accessmvp.com/djsteele/Diff2Dates.html
> 
> -- 
> Good Luck
> BS"D
> 
> 
> "aldunford" wrote:
> 
> > I need to create a query that will do the following:
> > 
> > these are my fields: resign date, hire date
> > 
> > I need to subtract the resign date from hire date but I want it to return 
> > the answer as years, months & days
> > 
> > ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days
> > 
> > is there a way to use the DateDiff function to return years, monts & days?
0
Utf
3/21/2008 2:59:01 PM
Reply:

Similar Artilces:

Epay dates
When I file an epay payment, I put in the due date. For some reason, the program puts in the current date. For example, if I pay a bill today that is due on July 15, I put in 7/15. When the payment is submitted, the program changes the date to 7/1 (today). The problem is, when the program tracks my balance, it looks like the 7/15 bill has already been paid. That makes my balance look lower--and often overdrawn. I want the date of an epay to stay the date the payment will be made. I cannot find any setting to make the program do this and the help is no help. --DS ...

A Template or a Macro with the current date
Hi, Can i add the current date to a new template of a new macro? That is, i want to have a template where the current date is already mentioned.(or a macro that added the current date together with other features) When i tried to replay a macro of the add current date shortcut (ctrl+;) it added the same date that was recorded already and not the current one. Thanks a lot, Iris. Iris One way: Range("A1") = Date HTH Otto "iris" <sassonir@bgumail.bgu.ac.il> wrote in message news:18fcd0e0.0309291307.8079802@posting.google.com... > Hi, > > Can i add the ...

How to create report from Pivot Table View of Union Query?
Hi all, I have create a Union ALL Query. I want to generate a report from it. The Pivot Table View of this Union Query shows exactly what I want (having Row Heading, Column Heading and Detailed Data), so how can I make it as a Report? I have tried to create new report, but there is no options of making a pivot table (something like Crosstab Query and Pivot Table View of the Union Query). Also tried to export to Excel, but it shows only count of records. I want all detailed records. Thank you. -- Message posted via http://www.accessmonster.com Hi Duane, I tried to create a crosstab qu...

Calculate As of Date Check Book Opening Balance
Hi Is there any way to calculate as of opening and closing balance of a given check for Payments and Deposits Listing report Module = Banak Management Regards Irfan ...

Need one help
Dear Sir/Madam, I need some help that I am working in cousulting firm and we providing solution to clinet as web base and mail base. We are microsoft ceritfied partner and we using win2k3 ent. server on this server exchange2k3 and other is win2k3 with IMail. Till upto now we are proving clients mail solution on Imail base but now we are planing to charge more for mail solution and those are interested to pay then we give them exchange mail solution. Now I having one question in my mind is that in IMail I create diffrent diffrent domain for different web site according to clients. Now o...

please help #8
I am trying to switch info from one XP machine running office 2000 to another XP machine running office 2000 and I have a ton of Macros. How do I successfully move these Macros in excel, so I don't have to recreate them? Hi if you have stored your macros in your personal.xls just copy thie file to your new machine -- Regards Frank Kabel Frankfurt, Germany Bob Garber wrote: > I am trying to switch info from one XP machine running > office 2000 to another XP machine running office 2000 and > I have a ton of Macros. How do I successfully move these > Macros in excel, so I...

Thanks for all the help on Year-to-date query!
It's so great to know that there are folks out there that can take the time to help someone whose 78 year old brain is slowly turning to gorgonzola cheese! Again, thanX Mikie Francisco 9 @ ATT N E T ...

Query from Multiple Workbooks
Hello, I have 12 files in a directory with a sheet name Sales containing Sales data for each month having more than half a million rows in each workbook sheets The field names are all the same in all the files like date, item, itemgroup, branch, unit, salesman, amount etc I am looking for a way to get a query from all these workbook and get a summarised report based on criteria like sales by salesman, by month, by product, by product group, by date, by branch etc. Is this will be possible through code?. Itry to use MS query but not successful Using Excel 2007 and Win XP ...

Date in an Edit Control
Hi i would like to enter a Date value into an edit box To get very spcific... i have an edit box when selected ,the user must be able to just select the date he requires such as 11 02 04 ,.....only the numbers are required and this has to be stored into a fil See this link http://www.codeproject.com/editctrl/datetimedit.asp "NoviceJohn" <anonymous@discussions.microsoft.com> wrote in message news:602F22CD-C213-4F15-B007-39DA6A459BC9@microsoft.com... > Hi i would like to enter a Date value into an edit box. > > To get very spcific.... > i have an edit box when...

Query to Redistribute a Table
Hi I am struggling with how to query a table with the current structure: Field1 Field2 ...Field14 Jan Feb Mar Apr... x y ...z 10 15 13 16... etc, It represents sales per month to customers per delivery adress, product and so forth.The numbers under each month respresents the sales for that month. I need to be able to query so I can see all detalis (ie fileds 1-14) per row per month, ie redistribute that original table so I can use month as a field and still use all the original fields. My new table needs to be something link Month Field 1 Field2 ... Field14 New ...

match dates in two columns get value from third
I have a data set on daily basis and another data set on weekly basis. i want to put the weekly data in daily data set by putting a formula which picks values from col c for relevant dates in daily set. example - let us say this is the data set ColA Col B Col C jan1 jan1 8 jan2 jan7 7 jan3 jan4 jan5 jan6 jan7 I want the data from colC against dates in ColA as in ColD below. it should leave other cells blank (colA and ColB are dates format) ColA Col B Col C ColD jan1 jan1 ...

Help with mutiple profiles / accounts
Hi there, I have multiple different pop3 accounts i would like to use from 1 profile, is this possible? Please help. I have 3 different pop 3 accounts which i have setup. I also have 3 different data folders that the mail from each account drops into, this works fine. However, how do I set it so that when I reply to an email from 'pop1' it uses the pop1 smtp server and account details and when I reply or create emails for 'pop2' it uses pop2 details (the same for pop3). This was possible in outlook express, in the email window, there was a simply a drop down from which...

Exchange Server Problem (I need help)
Hello to you all! The medical clinic I work at has a network. We all have our own email address. Mine, for example is lpeacock@elcardiologist.com. The problem is this. I can send out email as much as I want and they go where I need them to go. However, when anyone attempts to send any of us email, to our work, we don't get them. Now we had experianced some MAJOR problems recently in the past to where the actual IT lady had to set everything back up because things just went down. So our email accounts worked without any problems. But now, we can't recieve anything from an...

Help with array formula
I need help with an array formula, I have to columns, I need to find the min, the max and average form the two columns added, but I cant create a new column. E.g.; name s1 s2 John 1 1 Peter 3 5 Frank 6 3 min= 2 ( John) Max= 9 (Frank) average= 6.33 Thanks One way (array entered): =MIN(B2:B4+C2:C4) =MAX(B2:B4+C2:C4) =AVERAGE(B2:B4+C2:C4) In article <1164729405.003619.272780@j72g2000cwa.googlegroups.com>, GastonFranzini@gmail.com wrote: > I need help with an array formula, > I have to columns, I need to find ...

Help On User Form
Hi All, Can any one help me in this .I have made a orderform with some combobox and tickboxes a simple form i want to collect the data at the end of the sheet .Like when i select certain item from drop down box is there away that data can be collected at the end of the sheet.I Many Thanks in Advance Mastee -- mastee ------------------------------------------------------------------------ mastee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23826 View this thread: http://www.excelforum.com/showthread.php?threadid=398070 Good morning Mastee You really ne...

Update Query ctnd:
Hello, I have tried the code from Arvin however I now get 2 paramater prompts. The 1st is for "transactions.Datefield" and the 2nd "Please enter Waiting or Pending" UPDATE transactions SET transactions.Status = "Cleared" WHERE (((transactions.datefield)<Date()-10) And ((transactions.Status)=[Please enter Waiting or Pending])); Can I run this procedure atomatically when the database opens without the prompts & user input Thanks The first prompt implies that datefield isn't the actual name of the field in your transactions table. The second prompt...

Query All records except latest
Access 2003 I need to limit my query to ALL RECORDS EXCEPT the most current record. Any ideas on how to do this -- deb Deb - It will look something like this, assuming no more than one record per date per key field. You can adjust to use date/time if that is needed. It depends on what makes your records unique. Try something like this, substituting your table and fieldnames... Select myKeyfield, field1, field2, field3, myDate FROM myTable Where myDate < (Select max(myDate) from myTable AS T2 WHERE T2.myKeyfield = myTable.myKeyfield) -- Daryl S "d...

MS Query cannot be started because it isn't installed
I'm trying to get External Data from SQL Server using my Excel 2000, but it says MS Query cannot be started because it isn't installed. I've installed the MS Query Add-in severals times and it's still not working. Does MS Query work with Excel 2000? ...

Retrieving records between dates
Here is my table: CREATE TABLE [dbo].[FD__PROGRAM_CLIENT]( [ClientKey] [int] NULL, [PgmKey] [int] NULL, [Date_Admit_Program] [datetime] NULL, [Date_Discharged_Program] [datetime] NULL, CONSTRAINT [PK_SYS_FD__PROGRAM_CLIENT] PRIMARY KEY CLUSTERED SAMPLE DATA: 609 6 2008-05-27 00:00:00.000 2008-06-02 00:00:00.000 459 4 2008-07-30 00:00:00.000 2008-08-12 00:00:00.000 605 6 2008-06-17 00:00:00.000 2008-06-30 00:00:00.000 607 6 2008-04-23 00:00:00.000 NULL 1671 4 2008-07-15 00:00:00.000 2008-07-16 00:00:00.000 1757 6 2008-05-17 00:00:00.000 NULL...

Help needed in print
hi I am displaying lot of labels and progress bars in side form view. I want to print all the label and progress bar as it looks in the form view. thanks in advance with regards Harish Harish, You might be interested in Joe Newcomber's site, here: http://www.flounder.com/mvp_tips.htm#Bitmap%20series where you'll find code for screen copying and bitmap printing. Johan Rosengren Abstrakt Mekanik AB "Harish" <harish.k at sify.com> a �crit dans le message de news:OCcSb3u5DHA.2580@TK2MSFTNGP11.phx.gbl... > hi > > I am displaying lot of labels and progress...

Two Copies of Personal Folders Please Help!
I just transfered all of my email from my old computer to my new one and I just noticed that I have two identical copies of my personal folders in Outlook 2003. If I do something to one of them, it happens to both of them. How can I get rid of one of the copies of my personal folders? Also, I don't like the favorites folder. Can I hide or remove it? Thanks. Shannon you can edit the registry, but it'll be easier to make a new profile. http://www.outlook-tips.net/howto/ghosts.htm No, you can't hide Favorite folders. Try Ctrl+6 to use the old folder list view. -- Diane...

There is a problem with this website's security certificate
Hi All, This morning I woke up to a problem with my SBS 2008 SSL certificate for the remote company website. When we visit the https://remote.company.com/remote page, we get the following error message: There is a problem with this website's security certificate. Blah blah blah... In short, I reissued, rekeyed, revoked, and created a new SSL certificate at Godaddy...but regardless, I still get the same error message. Is this a problem with IIS? Any insight would be greatly appreciated. Thanks. Ringo A bit more info would be good. As... just wondering, but wha...

format date in excel
hi, Can you format a date in a formula to recognise the date only? i have a column with the day number only and need to calculate the figure directly to the right as this day number reaches todays date. example: day cost to date 12 £60.00 if todays date was 11th september, to date would be empty but if todays date was 12th september, to date would have the figure £60.00 in it. thanks in advance, N.S. in the To Date column - assume it is C2 =(if(day(today())<=A2,B2,"") "Nigel" wrote: > hi, > > Can you ...

Help: Reinstall on new PC won't work
Get the dreaded Activation code doesn't work hardware has changed. I emailed the address on wednesday. No response. I am moving to quicken, but I don't want to do it until I have time. Any way i can get around this for now. Did you have original boxed CD, or a downloaded copy of Money? "Frank Spina" wrote: > Get the dreaded Activation code doesn't work hardware has changed. I > emailed the address on wednesday. No response. > > I am moving to quicken, but I don't want to do it until I have time. Any > way i can get around this for now. >...

How to line up two dates?
Before I can carry out other task, I have to line up the row where Date 1 starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 ...