By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

Exclude Records in Query, DateDiff/DateAdd/DateSerial/ ? HELP

P: n/a
ORINGINAL Post
Thank you in advance. I'm trying to pull all inventory items from December
of the previous year back to 4 years for my accountant. I know this can be
done, but I'm drawing a blank. I've tried;

DateDiff("y",-4,DateIn) and get errors

Please any assistance would be greatly appreciated.

Michael

This was my original post. I'm sorry, I meant to ask, how can pull records
from December of last year, programmatically changing each December to the
previous 4 years, to allow them to be dropped from inventory for taxes?


Cooking the books eh? <g>

DateDiff("yyyy",-4,DateIn)
--
A)bort, R)etry, I)nfluence with large hammer.

This gives me NO records and I know there are records older than 4 years.
I've tried:

DateDiff("yyyy",-4, [DateIn]) and get NO records. This should be records
older than 4 years, should it not? Then I tried:
DateDiff("yyyy",-1,[DateIn]) still NO records. Then I tried;
DateDiff("yyyy",0,[DateIn]) Still NO records in my query.

PLEASE help me. I'm trying to take all inventory from December back four
years out for tax purposes. I would like the query to do it programmatically
each December for the previous four year ago inventory!

So 2004, 4 years back would mean all my 1998 inventory would not show on
this Query. In 2005, I would like all my 1999 inventory to do the same
thing. And so on and so on!

DateIn is the field that shows when the inventory was added, using a short
date mm/dd/yyyy!

Thank you again for all your help.

Michael
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You've got the wrong syntax for the DateDiff function it should be
DateDiff("yyyy",[1stDate],[2ndDate])
The syntax you're using at the monent is for the DateAdd function.
If you want to find what the date is 4 years previous use a combination of
the DateDiff and the DateAdd
DateDiff("yyyy",DateAdd("yyyy",-4,[DateIn]),[DateIn]).

An easier way to do this in a query would be to filter by using the Year
function on the DateIn. In the query grid select DateIn again and placed it
inside the Year function i.e. Year([ClaimDate]). In the criteria paste the
following
=Year(Date())-5 And <=Year(Date())-1 This will display all records from 1999 to 2003.

Stewart

P.S. When replying to to a message can you please keep it in the same thread
by clicking reply to group instead of starting a new thread. This will all
related messages together.


This gives me NO records and I know there are records older than 4 years.
I've tried:

DateDiff("yyyy",-4, [DateIn]) and get NO records. This should be records
older than 4 years, should it not? Then I tried:
DateDiff("yyyy",-1,[DateIn]) still NO records. Then I tried;
DateDiff("yyyy",0,[DateIn]) Still NO records in my query.

PLEASE help me. I'm trying to take all inventory from December back four
years out for tax purposes. I would like the query to do it programmatically each December for the previous four year ago inventory!

So 2004, 4 years back would mean all my 1998 inventory would not show on
this Query. In 2005, I would like all my 1999 inventory to do the same
thing. And so on and so on!

DateIn is the field that shows when the inventory was added, using a short
date mm/dd/yyyy!

Thank you again for all your help.

Michael

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.