Expression problem in Report

[John White]

Does anyone know of a source for complex expression writing? I've been able
to get the following to work in a group footer, but it only counts records
(there are 20 different criteria versions, and they all work fine for
COUNTING records. I need the same date ranges in the new expression, but to
ADD VALUES of a field called [RfpDays].

This is the one that works fine for counting:

=Sum(Abs([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

I tried different versions, such as the following but nothing works - they
either come up with negative numbers or err#'s

=Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()))

I'd really appreciate any help. I've been stuck on this way to long!

Thanks in advance,

John

 

[Duane Hookom]

Try
=Sum(Abs([CallType] = "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())) * [RfpDays])

 

[John Spencer]

If the negative number is correct (other than being negative) then you have
forgotten to use the ABS function which will strip off the negative sign.

I usually do Abs(Sum(YourExpression goes here))
=Abs(Sum([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

=Abs(Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date())))

If you want to add RFPDays instead of COUNT the number of entries
=Sum(IIF( [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()), [RfpDays],Null))

That tests the value of CallDate and returns the value of RfpDays if the
criteria is met or returns null if it is not met and then sums returned
values.

Another way to do this
=(Sum([RfpDays] * ([CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date())))) * (-1)

I prefer the first of these two methods.

 

[John White]

Thanks Duane - I didn't need the CallType criteria, since it doesn't apply
to this particular summary. I tried one of John Spencer's and it worked
fine.

Thanks again!

John

Try
=Sum(Abs([CallType] = "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())) * [RfpDays])



--
Duane Hookom
MS Access MVP
--

Does anyone know of a source for complex expression writing? I've been
able to get the following to work in a group footer, but it only counts
records (there are 20 different criteria versions, and they all work fine
for COUNTING records. I need the same date ranges in the new expression,
but to ADD VALUES of a field called [RfpDays].

This is the one that works fine for counting:

=Sum(Abs([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

I tried different versions, such as the following but nothing works -
they either come up with negative numbers or err#'s

=Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()))

I'd really appreciate any help. I've been stuck on this way to long!

Thanks in advance,

John

 

[John White]

Thanks for the quick response, John. Your formula:
=Sum(IIF( [CallDate] Between DateAdd("d",-22,Date()) And

DateAdd("d",-28,Date()), [RfpDays],Null)) worked perfectly.

I hate to bother people before I try everything, but my database is now up
and running for the company... almost all of my time-consuming issues come
from writing expressions. Is there a source anywhere that explains the
logical progression of calclations needed for creating them?

Thanks again for the quick response.

Regards,

John

If the negative number is correct (other than being negative) then you
have forgotten to use the ABS function which will strip off the negative
sign.

I usually do Abs(Sum(YourExpression goes here))
=Abs(Sum([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

=Abs(Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date())))

If you want to add RFPDays instead of COUNT the number of entries
=Sum(IIF( [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()), [RfpDays],Null))

That tests the value of CallDate and returns the value of RfpDays if the
criteria is met or returns null if it is not met and then sums returned
values.

Another way to do this
=(Sum([RfpDays] * ([CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date())))) * (-1)

I prefer the first of these two methods.

Does anyone know of a source for complex expression writing? I've been
able to get the following to work in a group footer, but it only counts
records (there are 20 different criteria versions, and they all work fine
for COUNTING records. I need the same date ranges in the new expression,
but to ADD VALUES of a field called [RfpDays].

This is the one that works fine for counting:

=Sum(Abs([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

I tried different versions, such as the following but nothing works -
they either come up with negative numbers or err#'s

=Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()))

I'd really appreciate any help. I've been stuck on this way to long!

Thanks in advance,

John

 

[Duane Hookom]

If you didn't need CallType, then just remove it:
=Sum(Abs([CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date())) * [RfpDays])
The results should be the same as John's.


--
Duane Hookom
MS Access MVP
--

Thanks Duane - I didn't need the CallType criteria, since it doesn't apply
to this particular summary. I tried one of John Spencer's and it worked
fine.

Thanks again!

John

Try
=Sum(Abs([CallType] = "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())) * [RfpDays])



--
Duane Hookom
MS Access MVP
--

Does anyone know of a source for complex expression writing? I've been
able to get the following to work in a group footer, but it only counts
records (there are 20 different criteria versions, and they all work
fine for COUNTING records. I need the same date ranges in the new
expression, but to ADD VALUES of a field called [RfpDays].

This is the one that works fine for counting:

=Sum(Abs([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

I tried different versions, such as the following but nothing works -
they either come up with negative numbers or err#'s

=Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()))

I'd really appreciate any help. I've been stuck on this way to long!

Thanks in advance,

John

 

[John White]

Thanks again Duane - It works the same just like you said........

Any references for writing these? I've saved all of the expression-related
info on Microsoft's site, but couldn't find a general how-to on the way
expressions are built.

The database is split, and the backend replicated. There are quite a few
unwritten procedures regarding that also.

If you didn't need CallType, then just remove it:
=Sum(Abs([CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date())) * [RfpDays])
The results should be the same as John's.


--
Duane Hookom
MS Access MVP
--

Thanks Duane - I didn't need the CallType criteria, since it doesn't
apply to this particular summary. I tried one of John Spencer's and it
worked fine.

Thanks again!

John

Try
=Sum(Abs([CallType] = "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())) * [RfpDays])



--
Duane Hookom
MS Access MVP
--

Does anyone know of a source for complex expression writing? I've been
able to get the following to work in a group footer, but it only counts
records (there are 20 different criteria versions, and they all work
fine for COUNTING records. I need the same date ranges in the new
expression, but to ADD VALUES of a field called [RfpDays].

This is the one that works fine for counting:

=Sum(Abs([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

I tried different versions, such as the following but nothing works -
they either come up with negative numbers or err#'s

=Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()))

I'd really appreciate any help. I've been stuck on this way to long!

Thanks in advance,

John

 

[John Spencer]

I don't know of any source that explains this. I've picked it up over time.
Basically it is learning to combine various bits and pieces of code into
more complex statements.

Sometimes I have to do one step, get it working, then add the next step.
Repeat until I have the whole thing working.

Thanks for the quick response, John. Your formula:
=Sum(IIF( [CallDate] Between DateAdd("d",-22,Date()) And

DateAdd("d",-28,Date()), [RfpDays],Null)) worked perfectly.

I hate to bother people before I try everything, but my database is now up
and running for the company... almost all of my time-consuming issues come
from writing expressions. Is there a source anywhere that explains the
logical progression of calclations needed for creating them?

Thanks again for the quick response.

Regards,

John

If the negative number is correct (other than being negative) then you
have forgotten to use the ABS function which will strip off the negative
sign.

I usually do Abs(Sum(YourExpression goes here))
=Abs(Sum([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

=Abs(Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date())))

If you want to add RFPDays instead of COUNT the number of entries
=Sum(IIF( [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()), [RfpDays],Null))

That tests the value of CallDate and returns the value of RfpDays if the
criteria is met or returns null if it is not met and then sums returned
values.

Another way to do this
=(Sum([RfpDays] * ([CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date())))) * (-1)

I prefer the first of these two methods.

Does anyone know of a source for complex expression writing? I've been
able to get the following to work in a group footer, but it only counts
records (there are 20 different criteria versions, and they all work
fine for COUNTING records. I need the same date ranges in the new
expression, but to ADD VALUES of a field called [RfpDays].

This is the one that works fine for counting:

=Sum(Abs([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

I tried different versions, such as the following but nothing works -
they either come up with negative numbers or err#'s

=Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()))

I'd really appreciate any help. I've been stuck on this way to long!

Thanks in advance,

John

 

[Duane Hookom]

I agree with John. It takes experience and reading through news groups and
other sources.

One of the keys is understanding what type of expressions can be used in
various arguments of functions and aggregates. Then it's simply a matter of
combining these parts of expressions to get at your final required results.

--
Duane Hookom
MS Access MVP
--

I don't know of any source that explains this. I've picked it up over
time. Basically it is learning to combine various bits and pieces of code
into more complex statements.

Sometimes I have to do one step, get it working, then add the next step.
Repeat until I have the whole thing working.

Thanks for the quick response, John. Your formula:
=Sum(IIF( [CallDate] Between DateAdd("d",-22,Date()) And

DateAdd("d",-28,Date()), [RfpDays],Null)) worked perfectly.

I hate to bother people before I try everything, but my database is now
up and running for the company... almost all of my time-consuming issues
come from writing expressions. Is there a source anywhere that explains
the logical progression of calclations needed for creating them?

Thanks again for the quick response.

Regards,

John

If the negative number is correct (other than being negative) then you
have forgotten to use the ABS function which will strip off the negative
sign.

I usually do Abs(Sum(YourExpression goes here))
=Abs(Sum([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

=Abs(Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date())))

If you want to add RFPDays instead of COUNT the number of entries
=Sum(IIF( [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()), [RfpDays],Null))

That tests the value of CallDate and returns the value of RfpDays if the
criteria is met or returns null if it is not met and then sums returned
values.

Another way to do this
=(Sum([RfpDays] * ([CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date())))) * (-1)

I prefer the first of these two methods.


Does anyone know of a source for complex expression writing? I've been
able to get the following to work in a group footer, but it only counts
records (there are 20 different criteria versions, and they all work
fine for COUNTING records. I need the same date ranges in the new
expression, but to ADD VALUES of a field called [RfpDays].

This is the one that works fine for counting:

=Sum(Abs([CallType] Like "Telephone - Cold Call" And [CallDate] Between
DateAdd("d",-22,Date()) And DateAdd("d",-28,Date())))

I tried different versions, such as the following but nothing works -
they either come up with negative numbers or err#'s

=Sum([RfpDays], [CallDate] Between DateAdd("d",-22,Date()) And
DateAdd("d",-28,Date()))

I'd really appreciate any help. I've been stuck on this way to long!

Thanks in advance,

John