DateAdd Funtion Nightmare

[Cortney]

I have a very strange problem. I have a form with a date field. Four
queries run off that date field, they are current month, previous month, 3
month prior and an average of the three. They work great for October through
January. I just put Feb in and the previous month and 3 month prior will not
bring anything up. It brings up current month (Feb) and the average of the
three. it just won't bring up the previous and 3 month prior. Is there any
reason why it would not work for Feb? I am very confused because it works
beautifully for the months before.

The date format is 2/28/2006

My DateAdd funtions are the following:
[Forms]![Main Form]![Text0]
DateAdd("m",-1,[Forms]![Main Form]![Text0])
DateAdd("m",-2,[Forms]![Main Form]![Text0])
Between [Forms]![Main Form]![Text0] And DateAdd("m",-2,[Forms]![Main
Form]![Text0])

Any help is appreciated!!

 

[Douglas J. Steele]

I always put the smaller value first, as in

Between DateAdd("m",-2,[Forms]![Main Form]![Text0]) And [Forms]![Main
Form]![Text0]

However, that wouldn't explain why it used to work.

 

[Jerry Whittle]

Hi,

I tested your functions and all seem fine. So if they are OK, then it may
well be a problem with the rest of the query or the underlying data. To
troubleshoot a problem such as this, I'd do the following:

(1) Try to simplify the query. Maybe even hard code in the dates instead of
using the form reference.

(2) Manually search the tables for the expected records. They might not be
there. Make sure that the date field is formatted to display a 4 character
year. More than once I've seen dates stored with the wrong century!

There is a possibility that you need to wrap your dates in #'s so that it
gets properly evaluated as a date. Something like:

"#" & [Forms]![Main Form]![Text0]) & "#"

 

[Cortney]

Well, I figured out what the problem is, just not why its happening. It
works for January because Jan has 31 days. If I start with any month under
31 days, ex: Sep, I get Sep but not any month with 31 days. That is why when
I put in Feb, I get don't get Jan and Dec. I think it is something to do
with my date format but not sure.

I tried the following and got the results I wanted but I would have to
change the number of days each month. I want them all to work off of one
date field and by month is the best way.
DateAdd("d",-30,[Forms]![Main Form]![Text0])

I also tried hardcoding the dates. Ex:
DateAdd("m",-1,"31-Jan-2006") (this one worked)
DateAdd("m",-1,"28-Feb-2006")(this one did not work)

I have tried changing the format of the dates in the table and the query and
that didn't work either. it also doesn't explain why the average query still
brings in all three months. Its just the queries for the specific months.

Any advice?

Hi,

I tested your functions and all seem fine. So if they are OK, then it may
well be a problem with the rest of the query or the underlying data. To
troubleshoot a problem such as this, I'd do the following:

(1) Try to simplify the query. Maybe even hard code in the dates instead of
using the form reference.

(2) Manually search the tables for the expected records. They might not be
there. Make sure that the date field is formatted to display a 4 character
year. More than once I've seen dates stored with the wrong century!

There is a possibility that you need to wrap your dates in #'s so that it
gets properly evaluated as a date. Something like:

"#" & [Forms]![Main Form]![Text0]) & "#"
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a very strange problem. I have a form with a date field. Four
queries run off that date field, they are current month, previous month, 3
month prior and an average of the three. They work great for October through
January. I just put Feb in and the previous month and 3 month prior will not
bring anything up. It brings up current month (Feb) and the average of the
three. it just won't bring up the previous and 3 month prior. Is there any
reason why it would not work for Feb? I am very confused because it works
beautifully for the months before.

The date format is 2/28/2006

My DateAdd funtions are the following:
[Forms]![Main Form]![Text0]
DateAdd("m",-1,[Forms]![Main Form]![Text0])
DateAdd("m",-2,[Forms]![Main Form]![Text0])
Between [Forms]![Main Form]![Text0] And DateAdd("m",-2,[Forms]![Main
Form]![Text0])

Any help is appreciated!!

 

[KARL DEWEY]

Try this --
Between Format(Date()-Day(Date()),"mm/dd/yyyy") And
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

End of Last Month: Format(Date()-Day(Date()),"mm/dd/yyyy")

Begining of 3 Months ago:
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

Well, I figured out what the problem is, just not why its happening. It
works for January because Jan has 31 days. If I start with any month under
31 days, ex: Sep, I get Sep but not any month with 31 days. That is why when
I put in Feb, I get don't get Jan and Dec. I think it is something to do
with my date format but not sure.

I tried the following and got the results I wanted but I would have to
change the number of days each month. I want them all to work off of one
date field and by month is the best way.
DateAdd("d",-30,[Forms]![Main Form]![Text0])

I also tried hardcoding the dates. Ex:
DateAdd("m",-1,"31-Jan-2006") (this one worked)
DateAdd("m",-1,"28-Feb-2006")(this one did not work)

I have tried changing the format of the dates in the table and the query and
that didn't work either. it also doesn't explain why the average query still
brings in all three months. Its just the queries for the specific months.

Any advice?

Hi,

I tested your functions and all seem fine. So if they are OK, then it may
well be a problem with the rest of the query or the underlying data. To
troubleshoot a problem such as this, I'd do the following:

(1) Try to simplify the query. Maybe even hard code in the dates instead of
using the form reference.

(2) Manually search the tables for the expected records. They might not be
there. Make sure that the date field is formatted to display a 4 character
year. More than once I've seen dates stored with the wrong century!

There is a possibility that you need to wrap your dates in #'s so that it
gets properly evaluated as a date. Something like:

"#" & [Forms]![Main Form]![Text0]) & "#"
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a very strange problem. I have a form with a date field. Four
queries run off that date field, they are current month, previous month, 3
month prior and an average of the three. They work great for October through
January. I just put Feb in and the previous month and 3 month prior will not
bring anything up. It brings up current month (Feb) and the average of the
three. it just won't bring up the previous and 3 month prior. Is there any
reason why it would not work for Feb? I am very confused because it works
beautifully for the months before.

The date format is 2/28/2006

My DateAdd funtions are the following:
[Forms]![Main Form]![Text0]
DateAdd("m",-1,[Forms]![Main Form]![Text0])
DateAdd("m",-2,[Forms]![Main Form]![Text0])
Between [Forms]![Main Form]![Text0] And DateAdd("m",-2,[Forms]![Main
Form]![Text0])

Any help is appreciated!!

 

[Cortney]

I am sorry for not responding sooner. I was out of town for a while.
I am sorry to be a bit slow but where does my Form criteria fit into this?
I couldn't get it to work.

Try this --
Between Format(Date()-Day(Date()),"mm/dd/yyyy") And
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

End of Last Month: Format(Date()-Day(Date()),"mm/dd/yyyy")

Begining of 3 Months ago:
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

Well, I figured out what the problem is, just not why its happening. It
works for January because Jan has 31 days. If I start with any month under
31 days, ex: Sep, I get Sep but not any month with 31 days. That is why when
I put in Feb, I get don't get Jan and Dec. I think it is something to do
with my date format but not sure.

I tried the following and got the results I wanted but I would have to
change the number of days each month. I want them all to work off of one
date field and by month is the best way.
DateAdd("d",-30,[Forms]![Main Form]![Text0])

I also tried hardcoding the dates. Ex:
DateAdd("m",-1,"31-Jan-2006") (this one worked)
DateAdd("m",-1,"28-Feb-2006")(this one did not work)

I have tried changing the format of the dates in the table and the query and
that didn't work either. it also doesn't explain why the average query still
brings in all three months. Its just the queries for the specific months.

Any advice?

Hi,

I tested your functions and all seem fine. So if they are OK, then it may
well be a problem with the rest of the query or the underlying data. To
troubleshoot a problem such as this, I'd do the following:

(1) Try to simplify the query. Maybe even hard code in the dates instead of
using the form reference.

(2) Manually search the tables for the expected records. They might not be
there. Make sure that the date field is formatted to display a 4 character
year. More than once I've seen dates stored with the wrong century!

There is a possibility that you need to wrap your dates in #'s so that it
gets properly evaluated as a date. Something like:

"#" & [Forms]![Main Form]![Text0]) & "#"
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a very strange problem. I have a form with a date field. Four
queries run off that date field, they are current month, previous month, 3
month prior and an average of the three. They work great for October through
January. I just put Feb in and the previous month and 3 month prior will not
bring anything up. It brings up current month (Feb) and the average of the
three. it just won't bring up the previous and 3 month prior. Is there any
reason why it would not work for Feb? I am very confused because it works
beautifully for the months before.

The date format is 2/28/2006

My DateAdd funtions are the following:
[Forms]![Main Form]![Text0]
DateAdd("m",-1,[Forms]![Main Form]![Text0])
DateAdd("m",-2,[Forms]![Main Form]![Text0])
Between [Forms]![Main Form]![Text0] And DateAdd("m",-2,[Forms]![Main
Form]![Text0])

Any help is appreciated!!

 

[KARL DEWEY]

What I gave you is criteria for the query. You will not have to enter
anything.

The first one uses the current date and 3 months prior as criteria.

The last two formulate criteria for 'Last Month' and for '3 Prior Months' so
you do not need to enter each month.

Between Format(Date()-Day(Date()),"mm/dd/yyyy") and
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

I am sorry for not responding sooner. I was out of town for a while.
I am sorry to be a bit slow but where does my Form criteria fit into this?
I couldn't get it to work.

Try this --
Between Format(Date()-Day(Date()),"mm/dd/yyyy") And
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

End of Last Month: Format(Date()-Day(Date()),"mm/dd/yyyy")

Begining of 3 Months ago:
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

Well, I figured out what the problem is, just not why its happening. It
works for January because Jan has 31 days. If I start with any month under
31 days, ex: Sep, I get Sep but not any month with 31 days. That is why when
I put in Feb, I get don't get Jan and Dec. I think it is something to do
with my date format but not sure.

I tried the following and got the results I wanted but I would have to
change the number of days each month. I want them all to work off of one
date field and by month is the best way.
DateAdd("d",-30,[Forms]![Main Form]![Text0])

I also tried hardcoding the dates. Ex:
DateAdd("m",-1,"31-Jan-2006") (this one worked)
DateAdd("m",-1,"28-Feb-2006")(this one did not work)

I have tried changing the format of the dates in the table and the query and
that didn't work either. it also doesn't explain why the average query still
brings in all three months. Its just the queries for the specific months.

Any advice?

:

Hi,

I tested your functions and all seem fine. So if they are OK, then it may
well be a problem with the rest of the query or the underlying data. To
troubleshoot a problem such as this, I'd do the following:

(1) Try to simplify the query. Maybe even hard code in the dates instead of
using the form reference.

(2) Manually search the tables for the expected records. They might not be
there. Make sure that the date field is formatted to display a 4 character
year. More than once I've seen dates stored with the wrong century!

There is a possibility that you need to wrap your dates in #'s so that it
gets properly evaluated as a date. Something like:

"#" & [Forms]![Main Form]![Text0]) & "#"
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a very strange problem. I have a form with a date field. Four
queries run off that date field, they are current month, previous month, 3
month prior and an average of the three. They work great for October through
January. I just put Feb in and the previous month and 3 month prior will not
bring anything up. It brings up current month (Feb) and the average of the
three. it just won't bring up the previous and 3 month prior. Is there any
reason why it would not work for Feb? I am very confused because it works
beautifully for the months before.

The date format is 2/28/2006

My DateAdd funtions are the following:
[Forms]![Main Form]![Text0]
DateAdd("m",-1,[Forms]![Main Form]![Text0])
DateAdd("m",-2,[Forms]![Main Form]![Text0])
Between [Forms]![Main Form]![Text0] And DateAdd("m",-2,[Forms]![Main
Form]![Text0])

Any help is appreciated!!

 

[Cortney]

I need it to run off of a form where the user enters the date (always the
last day of the month 1/31/2006). The user can look up previous months if
they need to. Everything works great except if there are less than 31 days
in a month. For example, if the user enters 1/31/2006, the next form will
bring in Jan, Dec and Nov and then an average of the three. If I enter
2/28/2006, I am only getting Feb and the average. Jan and Dec won't come up
because they have 31 days. I am not sure how to get around it. If I just
put in Dec and Jan manually, the user will not be able to bring up a prior
period. Does this make sense?

What I gave you is criteria for the query. You will not have to enter
anything.

The first one uses the current date and 3 months prior as criteria.

The last two formulate criteria for 'Last Month' and for '3 Prior Months' so
you do not need to enter each month.

Between Format(Date()-Day(Date()),"mm/dd/yyyy") and
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

I am sorry for not responding sooner. I was out of town for a while.
I am sorry to be a bit slow but where does my Form criteria fit into this?
I couldn't get it to work.

Try this --
Between Format(Date()-Day(Date()),"mm/dd/yyyy") And
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

End of Last Month: Format(Date()-Day(Date()),"mm/dd/yyyy")

Begining of 3 Months ago:
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

:

Well, I figured out what the problem is, just not why its happening. It
works for January because Jan has 31 days. If I start with any month under
31 days, ex: Sep, I get Sep but not any month with 31 days. That is why when
I put in Feb, I get don't get Jan and Dec. I think it is something to do
with my date format but not sure.

I tried the following and got the results I wanted but I would have to
change the number of days each month. I want them all to work off of one
date field and by month is the best way.
DateAdd("d",-30,[Forms]![Main Form]![Text0])

I also tried hardcoding the dates. Ex:
DateAdd("m",-1,"31-Jan-2006") (this one worked)
DateAdd("m",-1,"28-Feb-2006")(this one did not work)

I have tried changing the format of the dates in the table and the query and
that didn't work either. it also doesn't explain why the average query still
brings in all three months. Its just the queries for the specific months.

Any advice?

:

Hi,

I tested your functions and all seem fine. So if they are OK, then it may
well be a problem with the rest of the query or the underlying data. To
troubleshoot a problem such as this, I'd do the following:

(1) Try to simplify the query. Maybe even hard code in the dates instead of
using the form reference.

(2) Manually search the tables for the expected records. They might not be
there. Make sure that the date field is formatted to display a 4 character
year. More than once I've seen dates stored with the wrong century!

There is a possibility that you need to wrap your dates in #'s so that it
gets properly evaluated as a date. Something like:

"#" & [Forms]![Main Form]![Text0]) & "#"
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a very strange problem. I have a form with a date field. Four
queries run off that date field, they are current month, previous month, 3
month prior and an average of the three. They work great for October through
January. I just put Feb in and the previous month and 3 month prior will not
bring anything up. It brings up current month (Feb) and the average of the
three. it just won't bring up the previous and 3 month prior. Is there any
reason why it would not work for Feb? I am very confused because it works
beautifully for the months before.

The date format is 2/28/2006

My DateAdd funtions are the following:
[Forms]![Main Form]![Text0]
DateAdd("m",-1,[Forms]![Main Form]![Text0])
DateAdd("m",-2,[Forms]![Main Form]![Text0])
Between [Forms]![Main Form]![Text0] And DateAdd("m",-2,[Forms]![Main
Form]![Text0])

Any help is appreciated!!

 

[KARL DEWEY]

Between DateAdd("m",-2,CVDate([Enter last date of last
month])-Day(CVDate([Enter last date of last month]))+1) And [Enter last date
of last month]

I need it to run off of a form where the user enters the date (always the
last day of the month 1/31/2006). The user can look up previous months if
they need to. Everything works great except if there are less than 31 days
in a month. For example, if the user enters 1/31/2006, the next form will
bring in Jan, Dec and Nov and then an average of the three. If I enter
2/28/2006, I am only getting Feb and the average. Jan and Dec won't come up
because they have 31 days. I am not sure how to get around it. If I just
put in Dec and Jan manually, the user will not be able to bring up a prior
period. Does this make sense?

What I gave you is criteria for the query. You will not have to enter
anything.

The first one uses the current date and 3 months prior as criteria.

The last two formulate criteria for 'Last Month' and for '3 Prior Months' so
you do not need to enter each month.

Between Format(Date()-Day(Date()),"mm/dd/yyyy") and
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

I am sorry for not responding sooner. I was out of town for a while.
I am sorry to be a bit slow but where does my Form criteria fit into this?
I couldn't get it to work.

:

Try this --
Between Format(Date()-Day(Date()),"mm/dd/yyyy") And
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

End of Last Month: Format(Date()-Day(Date()),"mm/dd/yyyy")

Begining of 3 Months ago:
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

:

Well, I figured out what the problem is, just not why its happening. It
works for January because Jan has 31 days. If I start with any month under
31 days, ex: Sep, I get Sep but not any month with 31 days. That is why when
I put in Feb, I get don't get Jan and Dec. I think it is something to do
with my date format but not sure.

I tried the following and got the results I wanted but I would have to
change the number of days each month. I want them all to work off of one
date field and by month is the best way.
DateAdd("d",-30,[Forms]![Main Form]![Text0])

I also tried hardcoding the dates. Ex:
DateAdd("m",-1,"31-Jan-2006") (this one worked)
DateAdd("m",-1,"28-Feb-2006")(this one did not work)

I have tried changing the format of the dates in the table and the query and
that didn't work either. it also doesn't explain why the average query still
brings in all three months. Its just the queries for the specific months.

Any advice?

:

Hi,

I tested your functions and all seem fine. So if they are OK, then it may
well be a problem with the rest of the query or the underlying data. To
troubleshoot a problem such as this, I'd do the following:

(1) Try to simplify the query. Maybe even hard code in the dates instead of
using the form reference.

(2) Manually search the tables for the expected records. They might not be
there. Make sure that the date field is formatted to display a 4 character
year. More than once I've seen dates stored with the wrong century!

There is a possibility that you need to wrap your dates in #'s so that it
gets properly evaluated as a date. Something like:

"#" & [Forms]![Main Form]![Text0]) & "#"
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a very strange problem. I have a form with a date field. Four
queries run off that date field, they are current month, previous month, 3
month prior and an average of the three. They work great for October through
January. I just put Feb in and the previous month and 3 month prior will not
bring anything up. It brings up current month (Feb) and the average of the
three. it just won't bring up the previous and 3 month prior. Is there any
reason why it would not work for Feb? I am very confused because it works
beautifully for the months before.

The date format is 2/28/2006

My DateAdd funtions are the following:
[Forms]![Main Form]![Text0]
DateAdd("m",-1,[Forms]![Main Form]![Text0])
DateAdd("m",-2,[Forms]![Main Form]![Text0])
Between [Forms]![Main Form]![Text0] And DateAdd("m",-2,[Forms]![Main
Form]![Text0])

Any help is appreciated!!

 

[Cortney]

Thanks for your help Karl. Much appreciated!!

Between DateAdd("m",-2,CVDate([Enter last date of last
month])-Day(CVDate([Enter last date of last month]))+1) And [Enter last date
of last month]

I need it to run off of a form where the user enters the date (always the
last day of the month 1/31/2006). The user can look up previous months if
they need to. Everything works great except if there are less than 31 days
in a month. For example, if the user enters 1/31/2006, the next form will
bring in Jan, Dec and Nov and then an average of the three. If I enter
2/28/2006, I am only getting Feb and the average. Jan and Dec won't come up
because they have 31 days. I am not sure how to get around it. If I just
put in Dec and Jan manually, the user will not be able to bring up a prior
period. Does this make sense?

What I gave you is criteria for the query. You will not have to enter
anything.

The first one uses the current date and 3 months prior as criteria.

The last two formulate criteria for 'Last Month' and for '3 Prior Months' so
you do not need to enter each month.

Between Format(Date()-Day(Date()),"mm/dd/yyyy") and
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1


:

I am sorry for not responding sooner. I was out of town for a while.
I am sorry to be a bit slow but where does my Form criteria fit into this?
I couldn't get it to work.

:

Try this --
Between Format(Date()-Day(Date()),"mm/dd/yyyy") And
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

End of Last Month: Format(Date()-Day(Date()),"mm/dd/yyyy")

Begining of 3 Months ago:
DateAdd("m",-3,Format(Date()-Day(Date()),"mm/dd/yyyy"))+1

:

Well, I figured out what the problem is, just not why its happening. It
works for January because Jan has 31 days. If I start with any month under
31 days, ex: Sep, I get Sep but not any month with 31 days. That is why when
I put in Feb, I get don't get Jan and Dec. I think it is something to do
with my date format but not sure.

I tried the following and got the results I wanted but I would have to
change the number of days each month. I want them all to work off of one
date field and by month is the best way.
DateAdd("d",-30,[Forms]![Main Form]![Text0])

I also tried hardcoding the dates. Ex:
DateAdd("m",-1,"31-Jan-2006") (this one worked)
DateAdd("m",-1,"28-Feb-2006")(this one did not work)

I have tried changing the format of the dates in the table and the query and
that didn't work either. it also doesn't explain why the average query still
brings in all three months. Its just the queries for the specific months.

Any advice?

:

Hi,

I tested your functions and all seem fine. So if they are OK, then it may
well be a problem with the rest of the query or the underlying data. To
troubleshoot a problem such as this, I'd do the following:

(1) Try to simplify the query. Maybe even hard code in the dates instead of
using the form reference.

(2) Manually search the tables for the expected records. They might not be
there. Make sure that the date field is formatted to display a 4 character
year. More than once I've seen dates stored with the wrong century!

There is a possibility that you need to wrap your dates in #'s so that it
gets properly evaluated as a date. Something like:

"#" & [Forms]![Main Form]![Text0]) & "#"
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a very strange problem. I have a form with a date field. Four
queries run off that date field, they are current month, previous month, 3
month prior and an average of the three. They work great for October through
January. I just put Feb in and the previous month and 3 month prior will not
bring anything up. It brings up current month (Feb) and the average of the
three. it just won't bring up the previous and 3 month prior. Is there any
reason why it would not work for Feb? I am very confused because it works
beautifully for the months before.

The date format is 2/28/2006

My DateAdd funtions are the following:
[Forms]![Main Form]![Text0]
DateAdd("m",-1,[Forms]![Main Form]![Text0])
DateAdd("m",-2,[Forms]![Main Form]![Text0])
Between [Forms]![Main Form]![Text0] And DateAdd("m",-2,[Forms]![Main
Form]![Text0])

Any help is appreciated!!