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

[PMBragg]

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

[Trevor Best]

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.

[PMBragg]

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.

[Trevor Best]

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.

[PMBragg]

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.

[Lyle Fairfield]

"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)

[PMBragg]

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!

[Lyle Fairfield]

"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)

[Stewart Allen]

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!

[PMBragg]

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)

[PMBragg]

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!

[Lyle Fairfield]

"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)

[PMBragg]

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)

[PMBragg]

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)

[PMBragg]

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!

[PMBragg]

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

Michael