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

DateDiff/DateAdd/Date Serial Question for Query ? Access 97

P: n/a
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?

Michael

Nov 12 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
On Tue, 13 Jan 2004 19:25:37 -0600 in comp.databases.ms-access,
"PMBragg" <pm*****@megavision.com> wrote:
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.
Nov 12 '05 #2

P: n/a
Trevor,

This didn't work, I get NO records and I have inventory that is older than 4
years. I also want to check since December. How do I do that?

Michael
"Trevor Best" <bouncer@localhost> wrote in message
news:ag********************************@4ax.com...
On Tue, 13 Jan 2004 19:25:37 -0600 in comp.databases.ms-access,
"PMBragg" <pm*****@megavision.com> wrote:
Thank you in advance. I'm trying to pull all inventory items from Decemberof the previous year back to 4 years for my accountant. I know this can bedone, 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 recordsfrom December of last year, programmatically changing each December to theprevious 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.

Nov 12 '05 #3

P: n/a
On Wed, 14 Jan 2004 10:15:01 -0600 in comp.databases.ms-access,
"PMBragg" <pm*****@megavision.com> wrote:
Trevor,

This didn't work, I get NO records and I have inventory that is older than 4
years. I also want to check since December. How do I do that?


Can you post the SQL of your query?

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #4

P: n/a
field name in query [DateIn].
Format = Short Date mm/dd/yyyy
I have tried:

DateAdd: DateAdd("yyyy",-4,[DateIn]) and get NO
records
Tried:
DateDiff("yyyy",DateAdd("yyyy",-4,[DateIn]),[DateIn]) and NO records.

I have records that are dated 01/05/1998.

What I'm trying to do is pull all records in a query that are newer than 4
years. This is for tax purposes. The inventory that is older than four
years, have been taxed and therefore not taxable! I'm trying to do this in a
Query, using the Criteria in the [DateIn] field, which is a short date
format field; mm/dd/yyyy

HELP

Michael
"Trevor Best" <bouncer@localhost> wrote in message
news:b1********************************@4ax.com...
On Wed, 14 Jan 2004 10:15:01 -0600 in comp.databases.ms-access,
"PMBragg" <pm*****@megavision.com> wrote:
Trevor,

This didn't work, I get NO records and I have inventory that is older than 4years. I also want to check since December. How do I do that?


Can you post the SQL of your query?

--
A)bort, R)etry, I)nfluence with large hammer.

Nov 12 '05 #5

P: n/a
"PMBragg" <pm*****@megavision.com> wrote in
news:kO******************@news.uswest.net:
field name in query [DateIn]. What I'm trying to do is pull all records in a query that are newer than
4 years.


WHERE DateDiff("yyyy",[fldDate],Date())>4;

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #6

P: n/a
One More Time!

I need all inventory from the Month of December, Programmatically for Newer
than 4 years. 12/31/2003

It works with DateDiff("yyyy",[datein],Date()), but I loose all the 2000
inventory, since this is Now January according to the criteria!

WHERE DateDiff("yyyy",[fldDate],Date())>4 gave me an error, but removing the
WHERE gives me everything but the 2000 year inventory, since we're in 2004.
I need 4 years back from December of 2003, and this year it will be 4 years
back from December 2004!

Nov 12 '05 #7

P: n/a
"PMBragg" <pm*****@megavision.com> wrote in
news:Rw*****************@news.uswest.net:
One More Time!

I need all inventory from the Month of December, Programmatically for
Newer than 4 years. 12/31/2003

It works with DateDiff("yyyy",[datein],Date()), but I loose all the 2000
inventory, since this is Now January according to the criteria!

WHERE DateDiff("yyyy",[fldDate],Date())>4 gave me an error, but removing
the WHERE gives me everything but the 2000 year inventory, since we're
in 2004. I need 4 years back from December of 2003, and this year it
will be 4 years back from December 2004!


If you want any date occurring >= 2000-01-01 and < 2004-01-01

you could try

DateIn >= DateSerial(2000,01,01) AND DateIn < DateSerial(2004,01,01)

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #8

P: n/a
Did you try my suggestion by using the Year function by adding the DateIn
field a second time in the query grid and use the Year function around the
DateIn field and also in the criteria (WHERE condition).
i.e. Expr1: Year([DateIn]) and in the criteria paste the following
=Year(Date())-5 And <=Year(Date())-1 This will display all records from 1999 to 2003 since the current year is
2004.

This equates to (watch wrapping)
WHERE ((Year([DateIn])>=Year(Date())-5) AND
(Year([DateIn])<=Year(Date())-1))

Stewart
"PMBragg" <pm*****@megavision.com> wrote in message
news:Rw*****************@news.uswest.net... One More Time!

I need all inventory from the Month of December, Programmatically for Newer than 4 years. 12/31/2003

It works with DateDiff("yyyy",[datein],Date()), but I loose all the 2000
inventory, since this is Now January according to the criteria!

WHERE DateDiff("yyyy",[fldDate],Date())>4 gave me an error, but removing the WHERE gives me everything but the 2000 year inventory, since we're in 2004. I need 4 years back from December of 2003, and this year it will be 4 years back from December 2004!

Nov 12 '05 #9

P: n/a
Lyle,

It works great, but every year I have to go in and change it. I'm trying to
figure out a way to do it programmatically.

Michael

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
"PMBragg" <pm*****@megavision.com> wrote in
news:Rw*****************@news.uswest.net:
One More Time!

I need all inventory from the Month of December, Programmatically for
Newer than 4 years. 12/31/2003

It works with DateDiff("yyyy",[datein],Date()), but I loose all the 2000
inventory, since this is Now January according to the criteria!

WHERE DateDiff("yyyy",[fldDate],Date())>4 gave me an error, but removing
the WHERE gives me everything but the 2000 year inventory, since we're
in 2004. I need 4 years back from December of 2003, and this year it
will be 4 years back from December 2004!


If you want any date occurring >= 2000-01-01 and < 2004-01-01

you could try

DateIn >= DateSerial(2000,01,01) AND DateIn < DateSerial(2004,01,01)

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Nov 12 '05 #10

P: n/a
Stewart,

This works great, now what do I do, to get all the Inventory that's 4 years
of older in another query?

Michael
"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bu**********@news.wave.co.nz...
Did you try my suggestion by using the Year function by adding the DateIn
field a second time in the query grid and use the Year function around the
DateIn field and also in the criteria (WHERE condition).
i.e. Expr1: Year([DateIn]) and in the criteria paste the following
=Year(Date())-5 And <=Year(Date())-1

This will display all records from 1999 to 2003 since the current year is
2004.

This equates to (watch wrapping)
WHERE ((Year([DateIn])>=Year(Date())-5) AND
(Year([DateIn])<=Year(Date())-1))

Stewart
"PMBragg" <pm*****@megavision.com> wrote in message
news:Rw*****************@news.uswest.net...
One More Time!

I need all inventory from the Month of December, Programmatically for

Newer
than 4 years. 12/31/2003

It works with DateDiff("yyyy",[datein],Date()), but I loose all the 2000
inventory, since this is Now January according to the criteria!

WHERE DateDiff("yyyy",[fldDate],Date())>4 gave me an error, but removing

the
WHERE gives me everything but the 2000 year inventory, since we're in

2004.
I need 4 years back from December of 2003, and this year it will be 4

years
back from December 2004!


Nov 12 '05 #11

P: n/a
"PMBragg" <pm*****@megavision.com> wrote in news:USwNb.2$nP3.11237
@news.uswest.net:
Lyle,

It works great, but every year I have to go in and change it. I'm trying to
figure out a way to do it programmatically.


DateIn >= DateSerial(Year(Date())-4,01,01)
AND
DateIn < DateSerial(Year(Date()),01,01)

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #12

P: n/a
That's awesom Lyle. Now how do I get my current Inventory in a like Query,
but all items programmatically back 4 years....lol

Michael

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn******************@130.133.1.4...
"PMBragg" <pm*****@megavision.com> wrote in news:USwNb.2$nP3.11237
@news.uswest.net:
Lyle,

It works great, but every year I have to go in and change it. I'm trying to figure out a way to do it programmatically.


DateIn >= DateSerial(Year(Date())-4,01,01)
AND
DateIn < DateSerial(Year(Date()),01,01)

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Nov 12 '05 #13

P: n/a
Nevermind, I figured it out. Thank you again everyone!
"PMBragg" <pm*****@megavision.com> wrote in message
news:ZH*****************@news.uswest.net...
That's awesom Lyle. Now how do I get my current Inventory in a like Query,
but all items programmatically back 4 years....lol

Michael

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn******************@130.133.1.4...
"PMBragg" <pm*****@megavision.com> wrote in news:USwNb.2$nP3.11237
@news.uswest.net:
Lyle,

It works great, but every year I have to go in and change it. I'm
trying
to figure out a way to do it programmatically.


DateIn >= DateSerial(Year(Date())-4,01,01)
AND
DateIn < DateSerial(Year(Date()),01,01)

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)


Nov 12 '05 #14

P: n/a
Nevermind, I figured it out. Thank you again everyone!
"PMBragg" <pm*****@megavision.com> wrote in message
news:AT***************@news.uswest.net...
Stewart,

This works great, now what do I do, to get all the Inventory that's 4 years of older in another query?

Michael
"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bu**********@news.wave.co.nz...
Did you try my suggestion by using the Year function by adding the DateIn field a second time in the query grid and use the Year function around the DateIn field and also in the criteria (WHERE condition).
i.e. Expr1: Year([DateIn]) and in the criteria paste the following
=Year(Date())-5 And <=Year(Date())-1

This will display all records from 1999 to 2003 since the current year is 2004.

This equates to (watch wrapping)
WHERE ((Year([DateIn])>=Year(Date())-5) AND
(Year([DateIn])<=Year(Date())-1))

Stewart
"PMBragg" <pm*****@megavision.com> wrote in message
news:Rw*****************@news.uswest.net...
One More Time!

I need all inventory from the Month of December, Programmatically for

Newer
than 4 years. 12/31/2003

It works with DateDiff("yyyy",[datein],Date()), but I loose all the 2000 inventory, since this is Now January according to the criteria!

WHERE DateDiff("yyyy",[fldDate],Date())>4 gave me an error, but
removing the
WHERE gives me everything but the 2000 year inventory, since we're in

2004.
I need 4 years back from December of 2003, and this year it will be 4

years
back from December 2004!



Nov 12 '05 #15

P: n/a
Thanks everyone. Now is there a way in Access to isolate, in a Query, a
specific year programmatically?

Michael
Nov 12 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.