date serial

[Guest]

I'm trying to write a criteria but failing miserably.

I have a query with this criteria:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This is used for a report bar graph. I need this criteria to be displayed as
the last bar (bar furthest to the right) with the preceding 11 months to its
left.

I've come up with this but it's a mess and not at all returning what I want:
WHERE (((qryCompsFacsMonths.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

Any help is greratly appreciated - thanks!

 

[Michel Walsh]

Have you tried


DateDiff("m", dateField, now ) <= 12



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks, Michael.

That's new to me. I've been monkeying with it to no good end. I'm not so
sure it's what I need.

Here's my current SQL:
SELECT Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00") AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

This works fine (thanks to Duane Hookom's help) but the problem is that this
chart is now part of a multi-chart report that uses the criteria I listed
below:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This criteria will always be a complete month. Is there any way of making
this criteria as the last bar of a bar graph with the preceding 11 months to
its left?

This is way beyond me.

Thanks!

--
www.Marzetti.com

Have you tried


DateDiff("m", dateField, now ) <= 12



Hoping it may help,
Vanderghast, Access MVP

I'm trying to write a criteria but failing miserably.

I have a query with this criteria:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This is used for a report bar graph. I need this criteria to be displayed
as
the last bar (bar furthest to the right) with the preceding 11 months to
its
left.

I've come up with this but it's a mess and not at all returning what I
want:
WHERE (((qryCompsFacsMonths.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

Any help is greratly appreciated - thanks!

 

[Michel Walsh]

Have you tried to replace the WHERE clause with:


WHERE DateDiff("m", DateReceived, now ) <= 12



leaving the other clauses (SELECT, FROM, GROUP BY and ORDER BY) unchanged?


Vanderghast, Access MVP

Thanks, Michael.

That's new to me. I've been monkeying with it to no good end. I'm not so
sure it's what I need.

Here's my current SQL:
SELECT Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00") AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

This works fine (thanks to Duane Hookom's help) but the problem is that
this
chart is now part of a multi-chart report that uses the criteria I listed
below:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This criteria will always be a complete month. Is there any way of making
this criteria as the last bar of a bar graph with the preceding 11 months
to
its left?

This is way beyond me.

Thanks!

--
www.Marzetti.com

Have you tried


DateDiff("m", dateField, now ) <= 12



Hoping it may help,
Vanderghast, Access MVP

I'm trying to write a criteria but failing miserably.

I have a query with this criteria:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This is used for a report bar graph. I need this criteria to be
displayed
as
the last bar (bar furthest to the right) with the preceding 11 months
to
its
left.

I've come up with this but it's a mess and not at all returning what I
want:
WHERE (((qryCompsFacsMonths.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

Any help is greratly appreciated - thanks!

 

[Guest]

Yes. That's what I meant by "monkeying with it". This is what I tried:
SELECT Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00") AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE ((DateDiff("m","DateReceived",Now())<=12))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

This returns Error 3071: "The expression is typed incorrectly, or it is too
complex to be evaluated." I can't see at all what's wrong with it however
DateDiff is new to me.

Also, I don't see how this addresses the criteria that I mentioned below:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

Thanks for your time!

--
www.Marzetti.com

Have you tried to replace the WHERE clause with:


WHERE DateDiff("m", DateReceived, now ) <= 12



leaving the other clauses (SELECT, FROM, GROUP BY and ORDER BY) unchanged?


Vanderghast, Access MVP

Thanks, Michael.

That's new to me. I've been monkeying with it to no good end. I'm not so
sure it's what I need.

Here's my current SQL:
SELECT Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00") AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

This works fine (thanks to Duane Hookom's help) but the problem is that
this
chart is now part of a multi-chart report that uses the criteria I listed
below:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This criteria will always be a complete month. Is there any way of making
this criteria as the last bar of a bar graph with the preceding 11 months
to
its left?

This is way beyond me.

Thanks!

--
www.Marzetti.com

Have you tried


DateDiff("m", dateField, now ) <= 12



Hoping it may help,
Vanderghast, Access MVP



I'm trying to write a criteria but failing miserably.

I have a query with this criteria:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This is used for a report bar graph. I need this criteria to be
displayed
as
the last bar (bar furthest to the right) with the preceding 11 months
to
its
left.

I've come up with this but it's a mess and not at all returning what I
want:
WHERE (((qryCompsFacsMonths.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

Any help is greratly appreciated - thanks!

 

[Michel Walsh]

The syntax is wrong on the second argument, It should be the field name, not
a string:


WHERE ((DateDiff("m",DateReceived,Now())<=12))


not

WHERE ((DateDiff("m","DateReceived",Now())<=12))


With that criteria, you only keep data that is between Now() and Now() -
12 months (boundaries)

Change Now() by a date in a (past) month to get another month limit than the
actual month. But in that case, you may try:


WHERE ((DateDiff("m", DateReceived, SomeDate) BETWEEN 0 AND 12))


since, in that case, it sounds like you don't want data in the month(s)
following the month into which SomeDate belongs to.




Vanderghast, Access MVP

Yes. That's what I meant by "monkeying with it". This is what I tried:
SELECT Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00") AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE ((DateDiff("m","DateReceived",Now())<=12))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

This returns Error 3071: "The expression is typed incorrectly, or it is
too
complex to be evaluated." I can't see at all what's wrong with it however
DateDiff is new to me.

Also, I don't see how this addresses the criteria that I mentioned below:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

Thanks for your time!

--
www.Marzetti.com

Have you tried to replace the WHERE clause with:


WHERE DateDiff("m", DateReceived, now ) <= 12



leaving the other clauses (SELECT, FROM, GROUP BY and ORDER BY)
unchanged?


Vanderghast, Access MVP

Thanks, Michael.

That's new to me. I've been monkeying with it to no good end. I'm not
so
sure it's what I need.

Here's my current SQL:
SELECT Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00") AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

This works fine (thanks to Duane Hookom's help) but the problem is that
this
chart is now part of a multi-chart report that uses the criteria I
listed
below:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This criteria will always be a complete month. Is there any way of
making
this criteria as the last bar of a bar graph with the preceding 11
months
to
its left?

This is way beyond me.

Thanks!

--
www.Marzetti.com


:

Have you tried


DateDiff("m", dateField, now ) <= 12



Hoping it may help,
Vanderghast, Access MVP



I'm trying to write a criteria but failing miserably.

I have a query with this criteria:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This is used for a report bar graph. I need this criteria to be
displayed
as
the last bar (bar furthest to the right) with the preceding 11
months
to
its
left.

I've come up with this but it's a mess and not at all returning what
I
want:
WHERE (((qryCompsFacsMonths.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]))
GROUP BY Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00"),
(Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

Any help is greratly appreciated - thanks!

 

[Guest]

Thanks, Michel (I noticed I mistakenly typed Michael before!)

The syntax is wrong on the second argument, It should be the field name, not
a string:

Gotcha! That fixed it up.

Change Now() by a date in a (past) month to get another month limit than the
actual month. But in that case, you may try:

That makes sense but I was trying to automate this more. Users won't be able
to do this. The report is run from a form where dates are entered in the
fields:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This brings me back to my question regarding if there's any way of making
this criteria as the last bar of a bar graph with the preceding 11 months to
its left?

Maybe you've explained that and I've misunderstood?

 

[Michel Walsh]

From what I understand, it would be to write:

WHERE ((DateDiff("m", DateReceived,
Forms!frmComplaintQueriesReports!EndingDate) BETWEEN 0 AND 12))



Vanderghast, Access MVP

Thanks, Michel (I noticed I mistakenly typed Michael before!)

The syntax is wrong on the second argument, It should be the field name,
not
a string:

Gotcha! That fixed it up.

Change Now() by a date in a (past) month to get another month limit than
the
actual month. But in that case, you may try:

That makes sense but I was trying to automate this more. Users won't be
able
to do this. The report is run from a form where dates are entered in the
fields:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This brings me back to my question regarding if there's any way of making
this criteria as the last bar of a bar graph with the preceding 11 months
to
its left?

Maybe you've explained that and I've misunderstood?

 

[Guest]

Thanks, Michel!

That works. Good grief - this is TOUGH stuff for me. Every time I feel I
understand Access I'm quickly reminded of how much I've yet to understand.

Thanks again! You're a life saver!

--
www.Marzetti.com

From what I understand, it would be to write:

WHERE ((DateDiff("m", DateReceived,
Forms!frmComplaintQueriesReports!EndingDate) BETWEEN 0 AND 12))



Vanderghast, Access MVP

Thanks, Michel (I noticed I mistakenly typed Michael before!)

The syntax is wrong on the second argument, It should be the field name,
not
a string:

Gotcha! That fixed it up.

Change Now() by a date in a (past) month to get another month limit than
the
actual month. But in that case, you may try:

That makes sense but I was trying to automate this more. Users won't be
able
to do this. The report is run from a form where dates are entered in the
fields:
Between [Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]

This brings me back to my question regarding if there's any way of making
this criteria as the last bar of a bar graph with the preceding 11 months
to
its left?

Maybe you've explained that and I've misunderstood?