Calculate date

Hi all,

I have the following MyTable:

ID        SSN     DATE

the  date is in the form of 4/10/1970. Month, Day Year.

My question is it display the  ID  & SSN if date is older than 6
years. Here is my sql but I am not picking up all the records.

SELECT ID, SSNI from MyTable:
where  MyTable.date < Date() - 3900

I came up with 3900 by multiplying 365(days) * 6(years).

thanks in advance for all your input

0
davidstevans
8/27/2007 3:07:28 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
769 Views

Similar Articles

[PageSpeed] 26

365 * 6 = 2190.

Instead, try

SELECT ID, SSNI from MyTable:
where  MyTable.date < DateAdd("yyyy", -6, Date())



-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


<davidstevans@gmail.com> wrote in message 
news:1188227248.285480.271070@19g2000hsx.googlegroups.com...
> Hi all,
>
> I have the following MyTable:
>
> ID        SSN     DATE
>
> the  date is in the form of 4/10/1970. Month, Day Year.
>
> My question is it display the  ID  & SSN if date is older than 6
> years. Here is my sql but I am not picking up all the records.
>
>
> I came up with 3900 by multiplying 365(days) * 6(years).
>
> thanks in advance for all your input
> 


0
Douglas
8/27/2007 3:14:33 PM
On Aug 27, 10:14 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> 365 * 6 = 2190.
>
> Instead, try
>
> SELECT ID, SSNI from MyTable:
> where  MyTable.date < DateAdd("yyyy", -6, Date())
>
> --
> Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
> (no private e-mails, please)
>
> <davidstev...@gmail.com> wrote in message
>
> news:1188227248.285480.271070@19g2000hsx.googlegroups.com...
>
> > Hi all,
>
> > I have the following MyTable:
>
> > ID        SSN     DATE
>
> > the  date is in the form of 4/10/1970. Month, Day Year.
>
> > My question is it display the  ID  & SSN if date is older than 6
> > years. Here is my sql but I am not picking up all the records.
>
> > I came up with 3900 by multiplying 365(days) * 6(years).
>
> > thanks in advance for all your input


Thanks for the help,

One more question if I was asked the question  what is the ID  & SSN
if date is older than current  years. Will the following query work

SELECT ID, SSNI from MyTable where  MyTable.date < DateAdd("yyyy", +1,
Date())

thanks



where  MyTable.date < DateAdd("yyyy", +1, Date())

0
davidstevans
8/27/2007 3:37:45 PM
On Aug 27, 10:37 am, davidstev...@gmail.com wrote:
> On Aug 27, 10:14 am, "Douglas J. Steele"
>
>
>
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> > 365 * 6 = 2190.
>
> > Instead, try
>
> > SELECT ID, SSNI from MyTable:
> > where  MyTable.date < DateAdd("yyyy", -6, Date())
>
> > --
> > Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
> > (no private e-mails, please)
>
> > <davidstev...@gmail.com> wrote in message
>
> >news:1188227248.285480.271070@19g2000hsx.googlegroups.com...
>
> > > Hi all,
>
> > > I have the following MyTable:
>
> > > ID        SSN     DATE
>
> > > the  date is in the form of 4/10/1970. Month, Day Year.
>
> > > My question is it display the  ID  & SSN if date is older than 6
> > > years. Here is my sql but I am not picking up all the records.
>
> > > I came up with 3900 by multiplying 365(days) * 6(years).
>
> > > thanks in advance for all your input
>
> Thanks for the help,
>
> One more question if I was asked the question  what is the ID  & SSN
> if date is older than current  years. Will the following query work
>
> SELECT ID, SSNI from MyTable where  MyTable.date < DateAdd("yyyy", +1,
> Date())
>
> thanks
>
> where  MyTable.date < DateAdd("yyyy", +1, Date())

Sorry in the last query I made a typo question was

ID  & SSN
if date is greater than current  years. Will the following query work
SELECT ID, SSNI from MyTable where  MyTable.date >  DateAdd("yyyy",
+1,
 Date());

thanks

0
davidstevans
8/27/2007 3:40:09 PM
Depends.  Do you want dates that are more than January 1 of the next year 
or do you want dates that are more than one year in from today's date?  Your 
criteria gets records that are after August 27, 2008 (this being August 27, 
2007).

If you want dates that are in 2008 and later then
   Where MyTable.Date >= DateSerial(Year(Date()), 1,1)

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

<davidstevans@gmail.com> wrote in message 
news:1188229209.062318.68040@57g2000hsv.googlegroups.com...
> On Aug 27, 10:37 am, davidstev...@gmail.com wrote:
>> On Aug 27, 10:14 am, "Douglas J. Steele"
>>
>>
>>
>> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>> > 365 * 6 = 2190.
>>
>> > Instead, try
>>
>> > SELECT ID, SSNI from MyTable:
>> > where  MyTable.date < DateAdd("yyyy", -6, Date())
>>
>> > --
>> > Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
>> > (no private e-mails, please)
>>
>> > <davidstev...@gmail.com> wrote in message
>>
>> >news:1188227248.285480.271070@19g2000hsx.googlegroups.com...
>>
>> > > Hi all,
>>
>> > > I have the following MyTable:
>>
>> > > ID        SSN     DATE
>>
>> > > the  date is in the form of 4/10/1970. Month, Day Year.
>>
>> > > My question is it display the  ID  & SSN if date is older than 6
>> > > years. Here is my sql but I am not picking up all the records.
>>
>> > > I came up with 3900 by multiplying 365(days) * 6(years).
>>
>> > > thanks in advance for all your input
>>
>> Thanks for the help,
>>
>> One more question if I was asked the question  what is the ID  & SSN
>> if date is older than current  years. Will the following query work
>>
>> SELECT ID, SSNI from MyTable where  MyTable.date < DateAdd("yyyy", +1,
>> Date())
>>
>> thanks
>>
>> where  MyTable.date < DateAdd("yyyy", +1, Date())
>
> Sorry in the last query I made a typo question was
>
> ID  & SSN
> if date is greater than current  years. Will the following query work
> SELECT ID, SSNI from MyTable where  MyTable.date >  DateAdd("yyyy",
> +1,
> Date());
>
> thanks
> 


0
John
8/27/2007 4:26:26 PM
Reply:

Similar Artilces:

Name, Date and Address Formats
Hi, I have a number of different spreadsheets that I need to impirt into a single database. Unfortunately some of the information is in different formats. Would anyone be able to provide me with a solution that can turn: A B Brown ABC Brown *A B C*Brown* Brown, A B Brown, Anthony Bernard Brown Anthony Bernard Mr A B Bernard Into separate fields for title, forename, initials & surname On a similar theme I also need to do much the same thing with addresses: 1 Victoria Road, Victoria, Victoriashire, VC1 1VC etc And lastly (for now anyway) I also have some dates that I need to ...

Short / long date format bug?
The computer is set to use the long date format and everything is OK until I make changes to a chart which causes all dates in all open worksheets to be displayed in the short date format. When excel is closed down and restarted the problem is resolved - but doing this is annoying. I can't see if this a known fault or if it has been introduced through a patch. Does anyone know anything about this strange behaviour please? Thanks. Bill Ridgeway Since posting this I haven't had one response. I can't believe I'm the only user experiencing this problem. Every time I...

Compare this year/qtr/mo to date with prior year/qtr/mo to date
Hi, I'm trying to create a report that compares revenues received this year/qtr/mo/wk with revenues received last year in the comparable year/qtr/mo/wk, and additionally by sales division and sales manager. I have the data stored in a table with fields: SlsDivID, SlsMgrID, DtRecd and AmtRecd. I know I will need to use the date part function, but am not sure how to get the info in the report I need. format should be: SlsDiv - SlsMgr - CY YTD - LY YTD - G/L and % - CY QTD - LY QTD - G/L and % etc etc. I have tried pivot tables (not working) and various group and total functions (a...

Can Excel express a date with ordinals?
Wondering if excel can express mmm dd dates including their ordinal? Examples are: May 4th, October 1st, June 3rd. Thanks! Wendy Wendy, For a date in A1: =TEXT(A1,"mmmm ") & IF(DAY(A1)>0,DAY(A1)&IF(AND(DAY(A1)>10,DAY(A1)<20),"th",CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th")),"") HTH, Bernie MS Excel MVP "Wendy D" <WendyD@discussions.microsoft.com> wrote in message news:054E5E93-3B1B-4311-AB49...

Excell & MailMerge dates
I have an excel file with dates broken down with a cell for the number in the month, a cell for the month, and a cell for the year. I used the "MMMM" for the month. In excell the cell reads "July", but when I use the cell as a data cell for mail merge in WORD I don't get the month I get the date number. Is there a way to get WORD to read the MONTH? Thanks From a previous post: You could use some helper cells and create strings for each field--then use those helper values: =text(a1,"dd-mmm-yyyy") =text(b1,"0.0") =text(c1,"#,##0.00&q...

Date Format turn to Year #2
From: learning_codes@hotmail.com - view profile Date: Thurs, Nov 16 2006 9:24 pm Email: "learning_co...@hotmail.com" <learning_co...@hotmail.com> Groups: microsoft.public.excel Not yet ratedRating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Remove | Report Abuse | Find messages by this author Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =3DYear(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated...

Input Mask for Dates
Can I apply an input mask or custom format to cells so that users do not have to type the slashes for date entry? Thanks! Hi have a look at http://www.cpearson.com/excel/DateTimeEntry.htm This requires VBA as this can't be done with formating only. -- Regards Frank Kabel Frankfurt, Germany Allison wrote: > Can I apply an input mask or custom format to cells so > that users do not have to type the slashes for date > entry? Thanks! You can use some VBA code that will interpret a date that is entered with out "-" or "/". See Chip Pearson's site: ...

USA to UK Dates
I have a list of data extracted from elsewhere and the dates are in USA format i.e. mm/dd/yyyy Can excel swap this around to UK format dd/mm/yyyy ? thanks in advance -- Vass ................................................ Now: YZF-R1, CBR1100xx-x Then: A100, MBX80, XL125, CB400, FZR600, CBR600Fv Hi, Format the dates to dd/mm/yyyy Highlight the dates Format > Cells > Number tab > Date > choose the one you need If it is not in there go to Custom and put dd/mm/yyyy in the space provided under Type. HTH. -- greg7468 -------------------------------------------------...

Use combo to print report between two sets of dates
I am trying to print a report that shows timesheet entries for two sets of dates. The report also prints out based on the data entry person selected. The user selects their name and then uses another combo box to select pre entered dates. My code is as follows: Private Sub cboDates_Click() Dim stDocName As String Dim FromDate As Date 'Name of criteria start timesheets date field. Dim ToDate As Date 'Name of criteria end timesheets date field. Dim FromOTDate As Date 'Name of criteria start OT date field. Dim ToOTDate As Date 'Name of criter...

Service Appointment, Activity History, and "Due Date"
When I create a service appointment in CRM, and I check the "All Day Event" check box, the schedule start and end times disappear, and the dates sync. This seems logical. However, when I look in the activity tab or the history tab the "Due Date" displays the next day. For example, I create a service appointment where the scheduled start date is 2/10/2007, and I select the "All Day Event" checkbox. The scheduled end date is set to 2/10/2007, the duration is set to 1 day, and the time fields disappear. If I view this service appointment in the activities ...

Ability to create formula to calculate benefit or deduction code
Would like to use a benefit or deduction based on a formula to calculate the amount instead of choosing only a flat amount or % as it is currently set up in GP version 9. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Commun...

Factors governing Inventory Cost Calculation at dealership
Homework? -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Shankar Raman" <Shankar Raman@discussions.microsoft.com> wrote in message news:E05B70B8-4AC0-415D-82DE-CDFD450832BF@microsoft.com... > ...

Calculating Percentages #3
I am doing a simple percentage calculation, however the two percentages are not adding up to 100%, is there a reason for this? Thanks for any help with this matter. Could you provide an example? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200507/1 What are your input values? "tifosi3" wrote: > I am doing a simple percentage calculation, however the two percentages are > not adding up to 100%, is there a reason for this? Thanks for any help with > this matter. > We'd need to have the exact numbers you're worki...

chart with dates on the x-axe
how can you change the x-axe into a text x-as when the data are dates ? on specific dates a want to show a specific value. When I want to create e.g. a line-type chart, the values are ok, but the x-axe with the dates becomes a time-ax in stead of showing the real dates from the data. Is there a way to change the dates into text, without making it explicit text (with a ') because I want to use them also for calculating things. ??? -- chriske ------------------------------------------------------------------------ chriske's Profile: http://www.excelforum.com/member.php?action=getinf...

Aggregate calculations on calculated query fields?
I made a query that uses fields from multiple tables to calculate values for daily metrics. I need to present monthly totals of the data as sums and/or means. The tables are linked by the Date (mm/dd/yyyy) field. I enter ‘between 03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields. When the query is executed I receive an error that I ‘tried to execute a query that does not include the specified expression *** as part of an aggregate function. *** is the calculated field in the...

Date Entry in Excel 2000
Hi All, We recently upgraded to 2003 and one of our users says she was able to enter a date such as 10/24/04 in Excel 2000 as 102404 when she had the cell formatted as a date. I haven't been able to reproduce it on a 2000 machine nor does it make sense to me that it would work given that a date in Excel has a numeric value which is unrelated to the digits in the date. Is she mistaken or is there a setting I don't know about which allows this? Thanks Erin She's most probably mistaken. That behavior isn't native to XL. It's possible to do this with event macros...

checkbox/calculations
I have a form with a subform. In the subform I have all the past service dates. I have a calculation where I take the most recent service date and multiply by the service interval on the main form to give me the next service date. I would like to add a checkbox in the subform or next to the nextservice date so it can take that date and put it into the servicelog and give me the next date automatically. Is there a way to do this. and if so what is the easiest way to go about it. I basically want it to automatically update without me having to move the date manually from the next service ...

How do get the years and days Between two dates
if you had 5/1/2006 and 2/28/2002 how would you get 4.17? If 5/1/2006 is in A1 and 2/28/2002 is in A2, then maybe: =(a1-a2)/365 or =(a1-a2)/365.25 But if you're trying to find differences between dates, you may want to take a look at =datedif(). You can find lots of info at Chip Pearson's site: http://www.cpearson.com/excel/datedif.htm (=datedif() was only documented in xl2k's help.) jhon doe wrote: > > if you had 5/1/2006 and 2/28/2002 how would you get 4.17? -- Dave Peterson ...

How to stop calculating CPP when employee turns to 65 years of age
Hi; I am using Canadian Payroll in GP 10, one of my employee who turned to 65 years of old, subject to Canada regulation, he does not need to contribute CPP. How can I configure Canadian Payroll to calculate CPP under this employee, today; I have to remove the calculated amount manuall when I ran pay roll each time. The date of birth of the employee has been entered in system already. -- Kane I assume the Canadian Payroll is similar to that of the US in the sense that you have benefits and deductions. Canada Pension Plan (CPP) is a deduction that is (must be) associated to an empl...

dates as minimum axis values
Excel versions prior to Excel 2007 allowed dates to be entered as minimum axis values for XY charts on the axis format scale menu. In Excel 2007 when I attempt to enter a date as a minimum value, i.e. attempting to enter 1-1-2007 with the Fixed radio button selected in Axis Options, the automatic value pops back up unchanged. I can successfuly enter a numeric value, but not a date. This requirement adds several extra steps - calculating a date value and typing the resulting number, then deleting the calculation - to what used to be a simple process. If anyone knows that the same cap...

Automaticaly put in the / in a date field
I want my users to input a date in the format dd/mm/yy I know some of them will mis out the separators and enter for example 140301 for the 14th March 2001, but Excel changes the date to something totally different. How can I create a mask (like in Access) so the separators are already displayed in the cell? For example the cell would look like / / and when the date is input it automatically jumps over the separator. You could use a worksheet event that changes the entry to a date. Chip Pearson has some code at: http://www.cpearson.com/excel/DateTimeEntry.htm Keith wrote: > ...

Extraction of Dates
Hello...I would appreciate any help. I have database that I am creating and one of the tables contains SessionId (unique), StartDate, and an EndDate. This data comes from a data logger. There is also another table connected to this table that records a sound code at night, and a date if sounds were heard (within those session dates). However...if there was no sound...then that date does not get input. What I would like to see is an output of all the dates (from the StartDate to the EndDate for each session), whether or not there was a sound or not. How do I generate a popul...

how do i create reminders in outlook from a date cell in excel?
I need to know how to create reminders in outlook from a date cell in excel? hi "Philhegele" Try posting in an Outllook forum. Maybe they will be more helpfull. Greetings from France Dav "Philhegele" <Philhegele@discussions.microsoft.com> a écrit dans le message de news:D31EF446-D6BA-45E7-A766-EC3400CF3B26@microsoft.com... >I need to know how to create reminders in outlook from a date cell in >excel? ...

How to add the date after the "A1"
Hi, I type "The information gathered as of March 9, 2008" on cell "A1" Is there a way to make a date default like "The information gathered as of " & Date (default). Your help would be much apprecated. ="The informations gathered as of "&TEXT(TODAY(),"mmmm d, yyyy") -- Kind regards, Niek Otten Microsoft MVP - Excel <learning_codes@hotmail.com> wrote in message news:c11a1bfc-65ba-4cf3-bdef-c28bdcc19c1d@8g2000hse.googlegroups.com... | Hi, | | I type "The information gathered as of March 9, 2008" on cell "A1&q...

sort by date #2
I am trying to sort by date where formatting is 'yyyymmdd' and the months are December, January, February, and March. The months need to show in the above order. Thanks for helping with this.. Create a helper column with a ranking as you see fit such as =IF(MONTH(G1)+1=13,1,MONTH(G1)+1) and sort by the helper column. -- HTH Bob Phillips "Red" <Red@discussions.microsoft.com> wrote in message news:DFDAB427-51F1-4B73-9591-09DDEE8B2E43@microsoft.com... > I am trying to sort by date where formatting is 'yyyymmdd' > and the months are December, January,...