DateAdd problem

[Guest]

I am using the following to return dates from January 2004:
month([current innoc]) = Month(DateAdd("m",-12,Date()))
Problem: It also returns dates from January 2005, Where am I going
brain-dead?

 

[Andi Mayer]

I am using the following to return dates from January 2004:
month([current innoc]) = Month(DateAdd("m",-12,Date()))
Problem: It also returns dates from January 2005, Where am I going
brain-dead?

it does what you asked: give me all with month=1

dateserial(year([current innoc]),month([current innoc]),1)=
dateserial(year(date())-1,month(date()),1)

 

[Guest]

Well, I wasn't clear, I was intending to use
month([current currentinnoc]) = Month(DateAdd("m",-12,Date()))

to consistently return records from a year ago in the same month as we
progress through the year

I am using the following to return dates from January 2004:
month([current innoc]) = Month(DateAdd("m",-12,Date()))
Problem: It also returns dates from January 2005, Where am I going
brain-dead?

it does what you asked: give me all with month=1

dateserial(year([current innoc]),month([current innoc]),1)=
dateserial(year(date())-1,month(date()),1)

 

[Andi Mayer]

Well, I wasn't clear, I was intending to use
month([current currentinnoc]) = Month(DateAdd("m",-12,Date()))

to consistently return records from a year ago in the same month as we
progress through the year

then us the example I gave you (you can set also the day)

dateserial(year([current innoc]),month([current innoc]),1)=
dateserial(year(date())-1,month(date()),1)
---

 

[Guest]

Now I get a "data type mismatch" error when opening the report

Well, I wasn't clear, I was intending to use
month([current currentinnoc]) = Month(DateAdd("m",-12,Date()))

to consistently return records from a year ago in the same month as we
progress through the year

then us the example I gave you (you can set also the day)

dateserial(year([current innoc]),month([current innoc]),1)=
dateserial(year(date())-1,month(date()),1)
---

 

[Andi Mayer]

Now I get a "data type mismatch" error when opening the report

look with I wrote

dateserial(year([current innoc]),month([current innoc]),1)=
dateserial(year(date())-1,month(date()),1)

and now it's your Job to set the right field names

and I don't know where you pasted the code (VBA module, Fields,
query,.......)

 

[Guest]

The code is part of an expression I put in the control source of a report as
a filter....the report was correct with the original "dateadd" component
until we began adding dates this month...then the report returned dates from
january 2004 AND 2005. I cut out the "dateadd"component and pasted the
"dateserial" component in it's place. Obviously something incredibly simple
is flying over my head.

Now I get a "data type mismatch" error when opening the report

look with I wrote

dateserial(year([current innoc]),month([current innoc]),1)=
dateserial(year(date())-1,month(date()),1)

and now it's your Job to set the right field names

and I don't know where you pasted the code (VBA module, Fields,
query,.......)

 

[Andi Mayer]

The code is part of an expression I put in the control source of a report as
a filter....the report was correct with the original "dateadd" component
until we began adding dates this month...then the report returned dates from
january 2004 AND 2005. I cut out the "dateadd"component and pasted the
"dateserial" component in it's place. Obviously something incredibly simple
is flying over my head.

without seeing yo whole expression I can't help:
for example:
month(date()) gives you a number
DateSerial (....) gives you a Date

dateserial(...)=Dateserial(....) gives you a boolean (true or false)

your first post:
month([current innoc]) = Month(DateAdd("m",-12,Date()))
your second post:
month([current currentinnoc]) = Month(DateAdd("m",-12,Date()))

notice the field name difference

I have also problems with the dateserial-function in the filter
property so use:

Month([aDat])&Year([aDat]) = Month(Date()) &Year(Date())-1)

but exchange [aDat] with your fieldName

 

[Wayne Morgan]

Jeff, try the Format function instead of the Month function. You have only
passed the Month as the filter, so you will get all records with that month,
regardless of the year. You need to also pass the year information to limit
the records to a month AND year.

Format([current currentinnoc], "mmyyyy") = Format(DateAdd("m",-12,Date()),
"mmyyyy")

The Format above will return 012004 if the current date is in January 2005.

There are other options.

 

[Guest]

Thank you Mr Mayer .... you and the other MVPs continue to demonstrate great
guruism............................ You're appreciated

The code is part of an expression I put in the control source of a report as
a filter....the report was correct with the original "dateadd" component
until we began adding dates this month...then the report returned dates from
january 2004 AND 2005. I cut out the "dateadd"component and pasted the
"dateserial" component in it's place. Obviously something incredibly simple
is flying over my head.

without seeing yo whole expression I can't help:
for example:
month(date()) gives you a number
DateSerial (....) gives you a Date

dateserial(...)=Dateserial(....) gives you a boolean (true or false)

your first post:
month([current innoc]) = Month(DateAdd("m",-12,Date()))
your second post:
month([current currentinnoc]) = Month(DateAdd("m",-12,Date()))

notice the field name difference

I have also problems with the dateserial-function in the filter
property so use:

Month([aDat])&Year([aDat]) = Month(Date()) &Year(Date())-1)

but exchange [aDat] with your fieldName

 

[Guest]

Cool....and yet another way to get it to work right....thanks Wayne

Jeff, try the Format function instead of the Month function. You have only
passed the Month as the filter, so you will get all records with that month,
regardless of the year. You need to also pass the year information to limit
the records to a month AND year.

Format([current currentinnoc], "mmyyyy") = Format(DateAdd("m",-12,Date()),
"mmyyyy")

The Format above will return 012004 if the current date is in January 2005.

There are other options.

--
Wayne Morgan
MS Access MVP

Well, I wasn't clear, I was intending to use
month([current currentinnoc]) = Month(DateAdd("m",-12,Date()))

to consistently return records from a year ago in the same month as we
progress through the year

 

[Andi Mayer]

Thank you Mr Mayer .... you and the other MVPs continue to demonstrate great
guruism............................ You're appreciated

who is Mr Mayer????

in newsgroups you can write Andi and I am not an MVP, I am only a tiny
programmer, who has a lot of time during an application devolopment,
due to a few datachanging Functions which takes ~5 minutes to run

 

[Guest]

You see ,, I am no good for anything...thanks Andi