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

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

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

On Tue, 13 Jan 2004 19:25:37 -0600 in comp.databases.ms-access,
"PMBragg" <pmbragg@megavision.com> wrote:
[color=blue]
>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?[/color]

Cooking the books eh? <g>

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

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

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:agu90016osjv0vgq1cf2o1cf0snv2q22ep@4ax.com...[color=blue]
> On Tue, 13 Jan 2004 19:25:37 -0600 in comp.databases.ms-access,
> "PMBragg" <pmbragg@megavision.com> wrote:
>[color=green]
> >Thank you in advance. I'm trying to pull all inventory items from[/color][/color]
December[color=blue][color=green]
> >of the previous year back to 4 years for my accountant. I know this can[/color][/color]
be[color=blue][color=green]
> >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[/color][/color]
records[color=blue][color=green]
> >from December of last year, programmatically changing each December to[/color][/color]
the[color=blue][color=green]
> >previous 4 years, to allow them to be dropped from inventory for taxes?[/color]
>
> Cooking the books eh? <g>
>
> DateDiff("yyyy",-4,DateIn)
> --
> A)bort, R)etry, I)nfluence with large hammer.[/color]

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

On Wed, 14 Jan 2004 10:15:01 -0600 in comp.databases.ms-access,
"PMBragg" <pmbragg@megavision.com> wrote:
[color=blue]
>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?[/color]

Can you post the SQL of your query?

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

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

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:b16b00dqm6m2sgnlbilku9kvel18gjhi9r@4ax.com...[color=blue]
> On Wed, 14 Jan 2004 10:15:01 -0600 in comp.databases.ms-access,
> "PMBragg" <pmbragg@megavision.com> wrote:
>[color=green]
> >Trevor,
> >
> >This didn't work, I get NO records and I have inventory that is older[/color][/color]
than 4[color=blue][color=green]
> >years. I also want to check since December. How do I do that?[/color]
>
> Can you post the SQL of your query?
>
> --
> A)bort, R)etry, I)nfluence with large hammer.[/color]

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

"PMBragg" <pmbragg@megavision.com> wrote in
news:kOmNb.2439$F83.94653@news.uswest.net:
[color=blue]
> field name in query [DateIn].[/color]
[color=blue]
> What I'm trying to do is pull all records in a query that are newer than
> 4 years.[/color]

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

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

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

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!

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

"PMBragg" <pmbragg@megavision.com> wrote in
news:RwoNb.430$4h7.45600@news.uswest.net:
[color=blue]
> 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![/color]

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)

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

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[color=blue]
>=Year(Date())-5 And <=Year(Date())-1[/color]
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" <pmbragg@megavision.com> wrote in message
news:RwoNb.430$4h7.45600@news.uswest.net...[color=blue]
> One More Time!
>
> I need all inventory from the Month of December, Programmatically for[/color]
Newer[color=blue]
> 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[/color]
the[color=blue]
> WHERE gives me everything but the 2000 year inventory, since we're in[/color]
2004.[color=blue]
> I need 4 years back from December of 2003, and this year it will be 4[/color]
years[color=blue]
> back from December 2004!
>
>
>[/color]

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

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" <MissingAddress@Invalid.Com> wrote in message
news:Xns9470F1C8DB6D9FFDBA@130.133.1.4...[color=blue]
> "PMBragg" <pmbragg@megavision.com> wrote in
> news:RwoNb.430$4h7.45600@news.uswest.net:
>[color=green]
> > 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![/color]
>
> 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)[/color]

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

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" <sagasu@ThisPartNotVailid.wave.co.nz> wrote in message
news:bu5500$qil$1@news.wave.co.nz...[color=blue]
> 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[color=green]
> >=Year(Date())-5 And <=Year(Date())-1[/color]
> 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" <pmbragg@megavision.com> wrote in message
> news:RwoNb.430$4h7.45600@news.uswest.net...[color=green]
> > One More Time!
> >
> > I need all inventory from the Month of December, Programmatically for[/color]
> Newer[color=green]
> > 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[/color]
> the[color=green]
> > WHERE gives me everything but the 2000 year inventory, since we're in[/color]
> 2004.[color=green]
> > I need 4 years back from December of 2003, and this year it will be 4[/color]
> years[color=green]
> > back from December 2004!
> >
> >
> >[/color]
>
>[/color]

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

"PMBragg" <pmbragg@megavision.com> wrote in news:USwNb.2$nP3.11237
@news.uswest.net:
[color=blue]
> 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.[/color]

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)

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

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" <MissingAddress@Invalid.Com> wrote in message
news:Xns94715E027424FFDBA@130.133.1.4...[color=blue]
> "PMBragg" <pmbragg@megavision.com> wrote in news:USwNb.2$nP3.11237
> @news.uswest.net:
>[color=green]
> > Lyle,
> >
> > It works great, but every year I have to go in and change it. I'm trying[/color][/color]
to[color=blue][color=green]
> > figure out a way to do it programmatically.[/color]
>
> 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)[/color]

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

Nevermind, I figured it out. Thank you again everyone!


"PMBragg" <pmbragg@megavision.com> wrote in message
news:ZHxNb.634$yg2.16594@news.uswest.net...[color=blue]
> 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" <MissingAddress@Invalid.Com> wrote in message
> news:Xns94715E027424FFDBA@130.133.1.4...[color=green]
> > "PMBragg" <pmbragg@megavision.com> wrote in news:USwNb.2$nP3.11237
> > @news.uswest.net:
> >[color=darkred]
> > > Lyle,
> > >
> > > It works great, but every year I have to go in and change it. I'm[/color][/color][/color]
trying[color=blue]
> to[color=green][color=darkred]
> > > figure out a way to do it programmatically.[/color]
> >
> > 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)[/color]
>
>[/color]

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

Nevermind, I figured it out. Thank you again everyone!


"PMBragg" <pmbragg@megavision.com> wrote in message
news:ATwNb.3$nP3.11212@news.uswest.net...[color=blue]
> Stewart,
>
> This works great, now what do I do, to get all the Inventory that's 4[/color]
years[color=blue]
> of older in another query?
>
> Michael
> "Stewart Allen" <sagasu@ThisPartNotVailid.wave.co.nz> wrote in message
> news:bu5500$qil$1@news.wave.co.nz...[color=green]
> > Did you try my suggestion by using the Year function by adding the[/color][/color]
DateIn[color=blue][color=green]
> > field a second time in the query grid and use the Year function around[/color][/color]
the[color=blue][color=green]
> > DateIn field and also in the criteria (WHERE condition).
> > i.e. Expr1: Year([DateIn]) and in the criteria paste the following[color=darkred]
> > >=Year(Date())-5 And <=Year(Date())-1[/color]
> > This will display all records from 1999 to 2003 since the current year[/color][/color]
is[color=blue][color=green]
> > 2004.
> >
> > This equates to (watch wrapping)
> > WHERE ((Year([DateIn])>=Year(Date())-5) AND
> > (Year([DateIn])<=Year(Date())-1))
> >
> > Stewart
> >
> >
> > "PMBragg" <pmbragg@megavision.com> wrote in message
> > news:RwoNb.430$4h7.45600@news.uswest.net...[color=darkred]
> > > One More Time!
> > >
> > > I need all inventory from the Month of December, Programmatically for[/color]
> > Newer[color=darkred]
> > > than 4 years. 12/31/2003
> > >
> > > It works with DateDiff("yyyy",[datein],Date()), but I loose all the[/color][/color][/color]
2000[color=blue][color=green][color=darkred]
> > > inventory, since this is Now January according to the criteria!
> > >
> > > WHERE DateDiff("yyyy",[fldDate],Date())>4 gave me an error, but[/color][/color][/color]
removing[color=blue][color=green]
> > the[color=darkred]
> > > WHERE gives me everything but the 2000 year inventory, since we're in[/color]
> > 2004.[color=darkred]
> > > I need 4 years back from December of 2003, and this year it will be 4[/color]
> > years[color=darkred]
> > > back from December 2004!
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

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

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

Michael