Date calculations


S

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
 
Ad

Advertisements

K

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.
 
S

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


KARL DEWEY said:
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 said:
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
 
K

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");

Stephanie said:
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


KARL DEWEY said:
'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 said:
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
 
S

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

KARL DEWEY said:
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");

Stephanie said:
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


KARL DEWEY said:
'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
 
J

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

KARL DEWEY said:
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");

Stephanie said:
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
 
Ad

Advertisements

K

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?

Stephanie said:
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

KARL DEWEY said:
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");

Stephanie said:
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
 
S

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

John Spencer MVP said:
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

KARL DEWEY said:
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
 
J

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

John Spencer MVP said:
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
 
J

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.
 
S

Stephanie

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

John Spencer MVP said:
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

John Spencer MVP said:
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
 
Ad

Advertisements

S

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

KARL DEWEY said:
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?

Stephanie said:
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

KARL DEWEY said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top