Interesting query problem

[DeanL]

Hi all,

I have a problem that I'm a little stumped by and need some help if
possible. I need to generate a report in Access 97 from 2 tables (easy
so far) but it requires a calculated date based on several factors
including whether a date is present in one of three fields.

Basically, I have 3 date fields (Award Date, RTL Date and Target RTL
Date) each of which may or may not contain a date. I need to use the
Award Date if present, if not then the RTL Date and if that's missing
then the Target RTL Date and then count X number of days forward from
that date based on a Cost field (below A then count 6 weeks forward,
below C but above C then count 8 weeks forward, etc.). If the Award
Date is present then that would be used otherwise the RTL Date and
Target RTL Date would be used to calculate an Estimated Award Date
based on other criteria.

There are a number of instances where there may be no date at all, in
which case I just want to show a basic message.

Any help would be very much appreciated.

Many thanks, Dean...

[ADezii]

Hi all,

I have a problem that I'm a little stumped by and need some help if
possible. I need to generate a report in Access 97 from 2 tables (easy
so far) but it requires a calculated date based on several factors
including whether a date is present in one of three fields.

Basically, I have 3 date fields (Award Date, RTL Date and Target RTL
Date) each of which may or may not contain a date. I need to use the
Award Date if present, if not then the RTL Date and if that's missing
then the Target RTL Date and then count X number of days forward from
that date based on a Cost field (below A then count 6 weeks forward,
below C but above C then count 8 weeks forward, etc.). If the Award
Date is present then that would be used otherwise the RTL Date and
Target RTL Date would be used to calculate an Estimated Award Date
based on other criteria.

There are a number of instances where there may be no date at all, in
which case I just want to show a basic message.

Any help would be very much appreciated.

Many thanks, Dean...

The value of your Calculated Field will be the return value of a Public Function. The logic goes something like this, in the Calculated Field within the
QBE Grid:
CalcField:fAnyName([Award Date], [RTL Date], [Target RTL Date], _
[Cost])

The actual function would be something like this:
Public Function fAnyName(AwardDate As Date, RTLDate As Date, TargetRTLDate As Date, MyCost As Currency)

If IsNull(AwardDate) AND IsNull(RTLDate) AND IsNull(TargetRTLDate) Then
Msgbox "Some Message"
fAnyName = NULL
Exit Function
End If

If Not IsNull(AwardDate) Then
'use Awarddate in calculations
fAnyName = <RetVal>
Else 'AwardDate is Null
If Not IsNull(RTLDate) Then
'use RTLDate in calculations
fAnyName = <RetVal>
Else
If Not IsNull(TargetRTLDate) Then
'use TargetRTLDate in calculations along with the passed Cost
'Argument
fAnyName = <RetVal>
Else
fAnyName = <RetVal>
End If
End If
End If

Hopes this helps and does not confuse!!!

[Bob Quintal]

"DeanL" <de*************@yahoo.com> wrote in
news:11**********************@v35g2000cwv.googlegr oups.com:

Hi all,

I have a problem that I'm a little stumped by and need some
help if possible. I need to generate a report in Access 97
from 2 tables (easy so far) but it requires a calculated date
based on several factors including whether a date is present
in one of three fields.

Basically, I have 3 date fields (Award Date, RTL Date and
Target RTL Date) each of which may or may not contain a date.
I need to use the Award Date if present, if not then the RTL
Date and if that's missing then the Target RTL Date and then
count X number of days forward from that date based on a Cost
field (below A then count 6 weeks forward, below C but above C
then count 8 weeks forward, etc.). If the Award Date is
present then that would be used otherwise the RTL Date and
Target RTL Date would be used to calculate an Estimated Award
Date based on other criteria.

There are a number of instances where there may be no date at
all, in which case I just want to show a basic message.

Any help would be very much appreciated.

Many thanks, Dean...

This sounds like you need a user defined function more than a
query, because the logic just gets too convoluted. You can then
call the udf in the query, or in the report itself.
Off the top of my head, this should do what you want.
public function CalcDate( dtAward, DtRTL, DtEstm, AmtCost) as
variant.

If not IsNull(dtAward) then
Calcdate = dtAward
ElseIf not IsNull(dtRTL) then
Calcdate = dtRTL
ElseIf not IsNull(dtEstm) then
If Cost < A then
Calcdate = DateAdd("ww",6, dtEstm)
ElseIf Cost < B then
Calcdate = DateAdd("ww",7, dtEstm)
Else
Calcdate = DateAdd("ww",8, dtEstm)
Else
Calcdate = "No Date Available"
end function

--
Bob Quintal

PA is y I've altered my email address.