Rolling dates

[Guest]

I need to define a query that will provide 12 months worth of data from the
date entered in a form. The form field is [firstdate]. The query would
provide all sales data for 12 months from the date entered (including the
date entered). The criteria listed below doesn't work.

Between (DateAdd("yyyy",-1,[Forms]![formname]![firstdate])) And
([Forms]![formname]![firstdate])

Can someone provide the accuate expression?

 

[Rick B]

what do you mean it "doesn't work"?

 

[Douglas J Steele]

I'm with Rick: what does "doesn't work" mean.

However, try:

Between DateAdd("yyyy",-1,CDate([Forms]![formname]![firstdate])) And
CDate([Forms]![formname]![firstdate])

 

[Guest]

Well, actually I want the full month not just 12 months from the date. If I
enter 12/31/2005, I want all sales for December 2005 to all sales up to
12/31/2005.

what do you mean it "doesn't work"?


--
Rick B

I need to define a query that will provide 12 months worth of data from the
date entered in a form. The form field is [firstdate]. The query would
provide all sales data for 12 months from the date entered (including the
date entered). The criteria listed below doesn't work.

Between (DateAdd("yyyy",-1,[Forms]![formname]![firstdate])) And
([Forms]![formname]![firstdate])

Can someone provide the accuate expression?

 

[Guest]

I mean all sales for December 2004 through December 2005.

Well, actually I want the full month not just 12 months from the date. If I
enter 12/31/2005, I want all sales for December 2005 to all sales up to
12/31/2005.

what do you mean it "doesn't work"?


--
Rick B

I need to define a query that will provide 12 months worth of data from the
date entered in a form. The form field is [firstdate]. The query would
provide all sales data for 12 months from the date entered (including the
date entered). The criteria listed below doesn't work.

Between (DateAdd("yyyy",-1,[Forms]![formname]![firstdate])) And
([Forms]![formname]![firstdate])

Can someone provide the accuate expression?

 

[Van T. Dinh]

Slightly strange naming convention: the way you use it, "FirstDate" should
be named "LastDate".

Do you have non-zero time component in your date Field? If you do, your
criteria translate to midnight of 12/31/2005 and sales on 12/31/2005 (most
likely to be after midnight) will NOT be included.

Try

= DateAdd("d", 1, DateAdd("yyyy",-1,[Forms]![formname]![firstdate]))

And < DateAdd("d", 1, [Forms]![formname]![firstdate])

The above will eliminates sales on 12/31/2004 and includes sales for the
whole day 12/31/2005.

 

[Guest]

Well, how about if I use the form field (it happens to be the first date on
the form and if I add another field, it will just confuse them) and disregard
the actual date - just use the month and year portion of the field for the
query?

Slightly strange naming convention: the way you use it, "FirstDate" should
be named "LastDate".

Do you have non-zero time component in your date Field? If you do, your
criteria translate to midnight of 12/31/2005 and sales on 12/31/2005 (most
likely to be after midnight) will NOT be included.

Try

= DateAdd("d", 1, DateAdd("yyyy",-1,[Forms]![formname]![firstdate]))

And < DateAdd("d", 1, [Forms]![formname]![firstdate])

The above will eliminates sales on 12/31/2004 and includes sales for the
whole day 12/31/2005.

--
HTH
Van T. Dinh
MVP (Access)

Well, actually I want the full month not just 12 months from the date. If
I
enter 12/31/2005, I want all sales for December 2005 to all sales up to
12/31/2005.

 

[Van T. Dinh]

Audrey

The description is still very confusing: it sounds like you want 13 months
and not 12 months' worth of sales. If you can't describe what you want, we
can't provide accurate suggestions.

In addition, you are not answering our questions for additional info., e.d.
does your sale date have non-zero time component?

Post you set-up (details of Table(s), Form, etc...), *exactly* what you
want, provide a sample set of data (relelvant to the boundary conditions)
and the set of results you want related to the sample set of data.

 

[Guest]

After going back to the manager that requested the report, he has decided
that he would like to enter the date range himself (firstdate and
seconddate), which is what we have on several other reports anyway. It did
sound like he wanted 13 months instead of 12. Now he will know exactly what
he's getting. Problem resolved.

Thank you for your patience !