Date calculations

[Stephanie]

Hi. I am trying to fiure out how to perform a date calculation.

I have parameters:

[LoggedDate] Between CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])

I enter [StartDate] as the beginning of the quarter (4/1/2009) an [EndDate]
as the end of the quarter (6/30/2009).

I'd like to have a comparison from the previous quarter (1/1/2009 to
3/31/2009).

Is there a date function that I can use to help me calculate this
information, given that each quarter has a different spin (months with 28,
30, 31 days...)

Thanks,
Stephanie

 

[KARL DEWEY]

'd like to have a comparison from the previous quarter
How do you want to compare the two quarters? Some number total? You
memtion number of days in a month so do you want daily average of a quarter
compared with the second quarter?

By the way you must pull data from both quarters in order to compare them.

 

[Stephanie]

Thanks, Karl.

I want to say this quarter there are this many incidents at this cost. And
last quarter, there were that many incidents at that cost.


So I want to say from 4/1/2009 to 6/30/2009 there were 5 incidents with a
cost of $1m.
Last quarter from 1/1/2009 to 3/31/2009, there were 4 incidents with a cost
of $20,000.

The reason I brought in the number of days in a month, is I think I can't
just say 6/30/2009 minus 3 months since March has 31 days (even if I knew how
to do that!).

Thanks for your help,
Stephanie

How do you want to compare the two quarters? Some number total? You
memtion number of days in a month so do you want daily average of a quarter
compared with the second quarter?

By the way you must pull data from both quarters in order to compare them.

Hi. I am trying to fiure out how to perform a date calculation.

I have parameters:

[LoggedDate] Between CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])

I enter [StartDate] as the beginning of the quarter (4/1/2009) an [EndDate]
as the end of the quarter (6/30/2009).

I'd like to have a comparison from the previous quarter (1/1/2009 to
3/31/2009).

Is there a date function that I can use to help me calculate this
information, given that each quarter has a different spin (months with 28,
30, 31 days...)

Thanks,
Stephanie

 

[KARL DEWEY]

Try this --
SELECT Format([DateField], "q yyyy") AS Quarter, Count([Incidents]) AS
Incident_Count, Sum([Cost]) AS Incident_Cost
FROM YourTable
WHERE [DateField] Between DateAdd("q",-2,(Date()))+1 AND
DateAdd("q",0,(Date()))+1
GROUP BY Format([DateField], "yyyyq");

Thanks, Karl.

I want to say this quarter there are this many incidents at this cost. And
last quarter, there were that many incidents at that cost.


So I want to say from 4/1/2009 to 6/30/2009 there were 5 incidents with a
cost of $1m.
Last quarter from 1/1/2009 to 3/31/2009, there were 4 incidents with a cost
of $20,000.

The reason I brought in the number of days in a month, is I think I can't
just say 6/30/2009 minus 3 months since March has 31 days (even if I knew how
to do that!).

Thanks for your help,
Stephanie

'd like to have a comparison from the previous quarter

How do you want to compare the two quarters? Some number total? You
memtion number of days in a month so do you want daily average of a quarter
compared with the second quarter?

By the way you must pull data from both quarters in order to compare them.

Hi. I am trying to fiure out how to perform a date calculation.

I have parameters:

[LoggedDate] Between CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])

I enter [StartDate] as the beginning of the quarter (4/1/2009) an [EndDate]
as the end of the quarter (6/30/2009).

I'd like to have a comparison from the previous quarter (1/1/2009 to
3/31/2009).

Is there a date function that I can use to help me calculate this
information, given that each quarter has a different spin (months with 28,
30, 31 days...)

Thanks,
Stephanie

 

[Stephanie]

Thanks Karl.
Is there a way to caclulate this without convert to quarter, as that
negatively impacts my query- rather than summary information, it breaks it
out by detail.

But if I could just pass a different data parameter in, something like
[LoggedDate] BETWEEN [StartDate] minus something AND [EndDate] minus
somthing...

I appreciate your help.
Stephanie

Try this --
SELECT Format([DateField], "q yyyy") AS Quarter, Count([Incidents]) AS
Incident_Count, Sum([Cost]) AS Incident_Cost
FROM YourTable
WHERE [DateField] Between DateAdd("q",-2,(Date()))+1 AND
DateAdd("q",0,(Date()))+1
GROUP BY Format([DateField], "yyyyq");

Thanks, Karl.

I want to say this quarter there are this many incidents at this cost. And
last quarter, there were that many incidents at that cost.


So I want to say from 4/1/2009 to 6/30/2009 there were 5 incidents with a
cost of $1m.
Last quarter from 1/1/2009 to 3/31/2009, there were 4 incidents with a cost
of $20,000.

The reason I brought in the number of days in a month, is I think I can't
just say 6/30/2009 minus 3 months since March has 31 days (even if I knew how
to do that!).

Thanks for your help,
Stephanie

'd like to have a comparison from the previous quarter
How do you want to compare the two quarters? Some number total? You
memtion number of days in a month so do you want daily average of a quarter
compared with the second quarter?

By the way you must pull data from both quarters in order to compare them.

:

Hi. I am trying to fiure out how to perform a date calculation.

I have parameters:

[LoggedDate] Between CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])

I enter [StartDate] as the beginning of the quarter (4/1/2009) an [EndDate]
as the end of the quarter (6/30/2009).

I'd like to have a comparison from the previous quarter (1/1/2009 to
3/31/2009).

Is there a date function that I can use to help me calculate this
information, given that each quarter has a different spin (months with 28,
30, 31 days...)

Thanks,
Stephanie

 

[John Spencer MVP]

Perhaps, you can use something like the following to get an offset of one
quarter from the dates you enter.

LoggedDate Between DateAdd('m',-3,[StartDate]) AND DateSerial(Year([End Date],
Month([EndDate])-2,0)



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Karl.
Is there a way to caclulate this without convert to quarter, as that
negatively impacts my query- rather than summary information, it breaks it
out by detail.

But if I could just pass a different data parameter in, something like
[LoggedDate] BETWEEN [StartDate] minus something AND [EndDate] minus
somthing...

I appreciate your help.
Stephanie

Try this --
SELECT Format([DateField], "q yyyy") AS Quarter, Count([Incidents]) AS
Incident_Count, Sum([Cost]) AS Incident_Cost
FROM YourTable
WHERE [DateField] Between DateAdd("q",-2,(Date()))+1 AND
DateAdd("q",0,(Date()))+1
GROUP BY Format([DateField], "yyyyq");

Thanks, Karl.

I want to say this quarter there are this many incidents at this cost. And
last quarter, there were that many incidents at that cost.


So I want to say from 4/1/2009 to 6/30/2009 there were 5 incidents with a
cost of $1m.
Last quarter from 1/1/2009 to 3/31/2009, there were 4 incidents with a cost
of $20,000.

The reason I brought in the number of days in a month, is I think I can't
just say 6/30/2009 minus 3 months since March has 31 days (even if I knew how
to do that!).

Thanks for your help,
Stephanie


:

'd like to have a comparison from the previous quarter
How do you want to compare the two quarters? Some number total? You
memtion number of days in a month so do you want daily average of a quarter
compared with the second quarter?

By the way you must pull data from both quarters in order to compare them.

:

Hi. I am trying to fiure out how to perform a date calculation.

I have parameters:

[LoggedDate] Between CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])

I enter [StartDate] as the beginning of the quarter (4/1/2009) an [EndDate]
as the end of the quarter (6/30/2009).

I'd like to have a comparison from the previous quarter (1/1/2009 to
3/31/2009).

Is there a date function that I can use to help me calculate this
information, given that each quarter has a different spin (months with 28,
30, 31 days...)

Thanks,
Stephanie

 

[KARL DEWEY]

that negatively impacts my query- rather than summary information, it
breaks it out by detail.
I seem to be missing something. The query should give you two records, last
quarter and this quarter. The records would have Quarter/Year, number of
incidents, and cost for the quarter.

What would you want if that is not it?

Thanks Karl.
Is there a way to caclulate this without convert to quarter, as that
negatively impacts my query- rather than summary information, it breaks it
out by detail.

But if I could just pass a different data parameter in, something like
[LoggedDate] BETWEEN [StartDate] minus something AND [EndDate] minus
somthing...

I appreciate your help.
Stephanie

Try this --
SELECT Format([DateField], "q yyyy") AS Quarter, Count([Incidents]) AS
Incident_Count, Sum([Cost]) AS Incident_Cost
FROM YourTable
WHERE [DateField] Between DateAdd("q",-2,(Date()))+1 AND
DateAdd("q",0,(Date()))+1
GROUP BY Format([DateField], "yyyyq");

Thanks, Karl.

I want to say this quarter there are this many incidents at this cost. And
last quarter, there were that many incidents at that cost.


So I want to say from 4/1/2009 to 6/30/2009 there were 5 incidents with a
cost of $1m.
Last quarter from 1/1/2009 to 3/31/2009, there were 4 incidents with a cost
of $20,000.

The reason I brought in the number of days in a month, is I think I can't
just say 6/30/2009 minus 3 months since March has 31 days (even if I knew how
to do that!).

Thanks for your help,
Stephanie


:

'd like to have a comparison from the previous quarter
How do you want to compare the two quarters? Some number total? You
memtion number of days in a month so do you want daily average of a quarter
compared with the second quarter?

By the way you must pull data from both quarters in order to compare them.

:

Hi. I am trying to fiure out how to perform a date calculation.

I have parameters:

[LoggedDate] Between CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])

I enter [StartDate] as the beginning of the quarter (4/1/2009) an [EndDate]
as the end of the quarter (6/30/2009).

I'd like to have a comparison from the previous quarter (1/1/2009 to
3/31/2009).

Is there a date function that I can use to help me calculate this
information, given that each quarter has a different spin (months with 28,
30, 31 days...)

Thanks,
Stephanie

 

[Stephanie]

John,

Thnaks- this type of paramater is what I'm looking for.

Howerver when I use

[LoggedDate] Between DateAdd('m',-3,[StartDate]) AND DateSerial(Year([End
Date], Month([EndDate])-2,0)

I receive an error: "the expression you entered has a function containing
the wrong number of arguments".

Is there a way to rephrase for the query?

Thanks,
Stephanie

Perhaps, you can use something like the following to get an offset of one
quarter from the dates you enter.

LoggedDate Between DateAdd('m',-3,[StartDate]) AND DateSerial(Year([End Date],
Month([EndDate])-2,0)



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Karl.
Is there a way to caclulate this without convert to quarter, as that
negatively impacts my query- rather than summary information, it breaks it
out by detail.

But if I could just pass a different data parameter in, something like
[LoggedDate] BETWEEN [StartDate] minus something AND [EndDate] minus
somthing...

I appreciate your help.
Stephanie

Try this --
SELECT Format([DateField], "q yyyy") AS Quarter, Count([Incidents]) AS
Incident_Count, Sum([Cost]) AS Incident_Cost
FROM YourTable
WHERE [DateField] Between DateAdd("q",-2,(Date()))+1 AND
DateAdd("q",0,(Date()))+1
GROUP BY Format([DateField], "yyyyq");

:

Thanks, Karl.

I want to say this quarter there are this many incidents at this cost. And
last quarter, there were that many incidents at that cost.


So I want to say from 4/1/2009 to 6/30/2009 there were 5 incidents with a
cost of $1m.
Last quarter from 1/1/2009 to 3/31/2009, there were 4 incidents with a cost
of $20,000.

The reason I brought in the number of days in a month, is I think I can't
just say 6/30/2009 minus 3 months since March has 31 days (even if I knew how
to do that!).

Thanks for your help,
Stephanie


:

'd like to have a comparison from the previous quarter
How do you want to compare the two quarters? Some number total? You
memtion number of days in a month so do you want daily average of a quarter
compared with the second quarter?

By the way you must pull data from both quarters in order to compare them.

:

Hi. I am trying to fiure out how to perform a date calculation.

I have parameters:

[LoggedDate] Between CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])

I enter [StartDate] as the beginning of the quarter (4/1/2009) an [EndDate]
as the end of the quarter (6/30/2009).

I'd like to have a comparison from the previous quarter (1/1/2009 to
3/31/2009).

Is there a date function that I can use to help me calculate this
information, given that each quarter has a different spin (months with 28,
30, 31 days...)

Thanks,
Stephanie

 

[John Spencer MVP]

Check out your parentheses. What you posted is missing a closing parenthesis
after End Date in the DateSerial function. I missed that when I entered the
sample string.

[LoggedDate] Between DateAdd('m',-3,[StartDate])
AND DateSerial(Year([End Date]), Month([EndDate])-2,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John,

Thnaks- this type of paramater is what I'm looking for.

Howerver when I use

[LoggedDate] Between DateAdd('m',-3,[StartDate]) AND DateSerial(Year([End
Date], Month([EndDate])-2,0)

I receive an error: "the expression you entered has a function containing
the wrong number of arguments".

Is there a way to rephrase for the query?

Thanks,
Stephanie

Perhaps, you can use something like the following to get an offset of one
quarter from the dates you enter.

LoggedDate Between DateAdd('m',-3,[StartDate]) AND DateSerial(Year([End Date],
Month([EndDate])-2,0)



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Karl.
Is there a way to caclulate this without convert to quarter, as that
negatively impacts my query- rather than summary information, it breaks it
out by detail.

But if I could just pass a different data parameter in, something like
[LoggedDate] BETWEEN [StartDate] minus something AND [EndDate] minus
somthing...

I appreciate your help.
Stephanie

:

Try this --
SELECT Format([DateField], "q yyyy") AS Quarter, Count([Incidents]) AS
Incident_Count, Sum([Cost]) AS Incident_Cost
FROM YourTable
WHERE [DateField] Between DateAdd("q",-2,(Date()))+1 AND
DateAdd("q",0,(Date()))+1
GROUP BY Format([DateField], "yyyyq");

:

Thanks, Karl.

I want to say this quarter there are this many incidents at this cost. And
last quarter, there were that many incidents at that cost.


So I want to say from 4/1/2009 to 6/30/2009 there were 5 incidents with a
cost of $1m.
Last quarter from 1/1/2009 to 3/31/2009, there were 4 incidents with a cost
of $20,000.

The reason I brought in the number of days in a month, is I think I can't
just say 6/30/2009 minus 3 months since March has 31 days (even if I knew how
to do that!).

Thanks for your help,
Stephanie


:

'd like to have a comparison from the previous quarter
How do you want to compare the two quarters? Some number total? You
memtion number of days in a month so do you want daily average of a quarter
compared with the second quarter?

By the way you must pull data from both quarters in order to compare them.

:

Hi. I am trying to fiure out how to perform a date calculation.

I have parameters:

[LoggedDate] Between CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])

I enter [StartDate] as the beginning of the quarter (4/1/2009) an [EndDate]
as the end of the quarter (6/30/2009).

I'd like to have a comparison from the previous quarter (1/1/2009 to
3/31/2009).

Is there a date function that I can use to help me calculate this
information, given that each quarter has a different spin (months with 28,
30, 31 days...)

Thanks,
Stephanie

 

[John W. Vinson]

I receive an error: "the expression you entered has a function containing
the wrong number of arguments".

Is there a way to rephrase for the query?

John accidentally left out a close parenthesis in the Year() function: instead
of

[LoggedDate] Between DateAdd('m',-3,[StartDate]) AND DateSerial(Year([End
Date], Month([EndDate])-2,0)


try

[LoggedDate] Between DateAdd('m',-3,[StartDate]) AND DateSerial(Year([End
Date]), Month([EndDate])-2,0)

It was a bit ambiguous what you wanted - John's answer will get the records
starting three months ago today and ending on the last day of that same month,
i.e. from April 9 through April 30. If that's not what you want, please
explain what you do.

 

[Stephanie]

Thank you, Sir! That worked well. Thanks for the lesson.

Check out your parentheses. What you posted is missing a closing parenthesis
after End Date in the DateSerial function. I missed that when I entered the
sample string.

[LoggedDate] Between DateAdd('m',-3,[StartDate])
AND DateSerial(Year([End Date]), Month([EndDate])-2,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John,

Thnaks- this type of paramater is what I'm looking for.

Howerver when I use

[LoggedDate] Between DateAdd('m',-3,[StartDate]) AND DateSerial(Year([End
Date], Month([EndDate])-2,0)

I receive an error: "the expression you entered has a function containing
the wrong number of arguments".

Is there a way to rephrase for the query?

Thanks,
Stephanie

Perhaps, you can use something like the following to get an offset of one
quarter from the dates you enter.

LoggedDate Between DateAdd('m',-3,[StartDate]) AND DateSerial(Year([End Date],
Month([EndDate])-2,0)



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Stephanie wrote:
Thanks Karl.
Is there a way to caclulate this without convert to quarter, as that
negatively impacts my query- rather than summary information, it breaks it
out by detail.

But if I could just pass a different data parameter in, something like
[LoggedDate] BETWEEN [StartDate] minus something AND [EndDate] minus
somthing...

I appreciate your help.
Stephanie

:

Try this --
SELECT Format([DateField], "q yyyy") AS Quarter, Count([Incidents]) AS
Incident_Count, Sum([Cost]) AS Incident_Cost
FROM YourTable
WHERE [DateField] Between DateAdd("q",-2,(Date()))+1 AND
DateAdd("q",0,(Date()))+1
GROUP BY Format([DateField], "yyyyq");

:

Thanks, Karl.

I want to say this quarter there are this many incidents at this cost. And
last quarter, there were that many incidents at that cost.


So I want to say from 4/1/2009 to 6/30/2009 there were 5 incidents with a
cost of $1m.
Last quarter from 1/1/2009 to 3/31/2009, there were 4 incidents with a cost
of $20,000.

The reason I brought in the number of days in a month, is I think I can't
just say 6/30/2009 minus 3 months since March has 31 days (even if I knew how
to do that!).

Thanks for your help,
Stephanie


:

'd like to have a comparison from the previous quarter
How do you want to compare the two quarters? Some number total? You
memtion number of days in a month so do you want daily average of a quarter
compared with the second quarter?

By the way you must pull data from both quarters in order to compare them.

:

Hi. I am trying to fiure out how to perform a date calculation.

I have parameters:

[LoggedDate] Between CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])

I enter [StartDate] as the beginning of the quarter (4/1/2009) an [EndDate]
as the end of the quarter (6/30/2009).

I'd like to have a comparison from the previous quarter (1/1/2009 to
3/31/2009).

Is there a date function that I can use to help me calculate this
information, given that each quarter has a different spin (months with 28,
30, 31 days...)

Thanks,
Stephanie

 

[Stephanie]

Karl,
Thanks for the help. Sorry I didn't explain the issue as well as I could
have. While I think your solution would have worked, I was trying to keep
everything at the summary level, and breaking it down to quarters was a extra
step.

The discussion group is lucky to haveyou answering questions!

Cheers,
Stephanie

breaks it out by detail.
I seem to be missing something. The query should give you two records, last
quarter and this quarter. The records would have Quarter/Year, number of
incidents, and cost for the quarter.

What would you want if that is not it?

Thanks Karl.
Is there a way to caclulate this without convert to quarter, as that
negatively impacts my query- rather than summary information, it breaks it
out by detail.

But if I could just pass a different data parameter in, something like
[LoggedDate] BETWEEN [StartDate] minus something AND [EndDate] minus
somthing...

I appreciate your help.
Stephanie

Try this --
SELECT Format([DateField], "q yyyy") AS Quarter, Count([Incidents]) AS
Incident_Count, Sum([Cost]) AS Incident_Cost
FROM YourTable
WHERE [DateField] Between DateAdd("q",-2,(Date()))+1 AND
DateAdd("q",0,(Date()))+1
GROUP BY Format([DateField], "yyyyq");

:

Thanks, Karl.

I want to say this quarter there are this many incidents at this cost. And
last quarter, there were that many incidents at that cost.


So I want to say from 4/1/2009 to 6/30/2009 there were 5 incidents with a
cost of $1m.
Last quarter from 1/1/2009 to 3/31/2009, there were 4 incidents with a cost
of $20,000.

The reason I brought in the number of days in a month, is I think I can't
just say 6/30/2009 minus 3 months since March has 31 days (even if I knew how
to do that!).

Thanks for your help,
Stephanie


:

'd like to have a comparison from the previous quarter
How do you want to compare the two quarters? Some number total? You
memtion number of days in a month so do you want daily average of a quarter
compared with the second quarter?

By the way you must pull data from both quarters in order to compare them.

:

Hi. I am trying to fiure out how to perform a date calculation.

I have parameters:

[LoggedDate] Between CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])

I enter [StartDate] as the beginning of the quarter (4/1/2009) an [EndDate]
as the end of the quarter (6/30/2009).

I'd like to have a comparison from the previous quarter (1/1/2009 to
3/31/2009).

Is there a date function that I can use to help me calculate this
information, given that each quarter has a different spin (months with 28,
30, 31 days...)

Thanks,
Stephanie