By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 424,847 Members | 3,102 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

# Interesting query problem

 P: n/a 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... May 30 '06 #1
Share this Question
2 Replies

 Expert 5K+ P: 8,616 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 = Else 'AwardDate is Null If Not IsNull(RTLDate) Then 'use RTLDate in calculations fAnyName = Else If Not IsNull(TargetRTLDate) Then 'use TargetRTLDate in calculations along with the passed Cost 'Argument fAnyName = Else fAnyName = End If End If End If Hopes this helps and does not confuse!!! May 31 '06 #2

 P: n/a "DeanL" 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. May 31 '06 #3

### This discussion thread is closed

Replies have been disabled for this discussion. 