Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    267

    Unanswered: Another Query/Report Issue

    For those who have seen my past few posts, you have noticed that i have had some really wierd problems with Access. Well I have a new One that has me baffled.

    I have a query, that is based on another query (I have both queries listed and have subsituted a lot of the display fields with a * to shorten the queries). The queries work exactly right in query preview. However when I tie quryRptMSSec00127Two to a report the LastTreatSubCategory Field displays as a Null Value (have test this) instead of displaying the actual value coming back in the select Statement that it is based on.

    Anybody had a similar problem? Any ideas on how to overcome this without changing QueryTwo in to a Maketable Query and basing the report on this temp table?

    quryRptMSSec00127One
    Code:
    SELECT tblOrangeProject.*, [Enter the Beginning Year of you search range (2004)] AS BeginYear, [Enter the Ending Year of you search range (2004)] AS EndYear, IIf([BeginYear]=[EndYear],[BeginYear],"") AS OneYearMarker
    FROM (tblSection AS tblSection_1 RIGHT JOIN tblOrangeProject ON tblSection_1.SORANGEPROG = tblOrangeProject.OPOrangeNUM) LEFT JOIN tblSection ON tblOrangeProject.OPOrangeNUM = tblSection.SPLANMAJCONTTYPE
    WHERE (((tblOrangeProject.OPPROJTREATTYPE)<>"None") AND ((tblOrangeProject.OPYEAR) Between [Enter the Beginning Year of you search range (2004)] And [Enter the Ending Year of you search range (2004)]));

    quryRptMSSec00127Two
    Code:
    SELECT quryRptMSSec00127One.*, (Select Top 1 CPTREATSubStrategy From tblConstPaveHist Where tblConstPaveHist.CPRouteNum = quryRptMSSec00127One.Route ANd tblConstPaveHist.CPBegPt = quryRptMSSec00127One.BegPt And tblConstPaveHist.CPEndPt=quryRptMSSec00127One.EndPt And tblConstPaveHist.CPSubId = quryRptMSSec00127One.DirID And tblConstPaveHist.CPSecType = quryRptMSSec00127One.SecType Order By tblConstPaveHist.CPYear Desc) AS LastTreatSubCategory
    FROM tblTreatTypeSubCost RIGHT JOIN quryRptMSSec00127One ON (tblTreatTypeSubCost.CTCOSTTYPE = quryRptMSSec00127One.CostType) AND (tblTreatTypeSubCost.TTSTREATSUBNUM = quryRptMSSec00127One.OPPROJSUBTREATTYPE)
    WHERE (((quryRptMSSec00127One.Route) Is Not Null))
    ORDER BY quryRptMSSec00127One.GROUPINGSRank, quryRptMSSec00127One.OPOrangeNUM, quryRptMSSec00127One.Route, quryRptMSSec00127One.BegPt;

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    bump thread

  3. #3
    Join Date
    Nov 2003
    Posts
    267
    Turned the final query into a make table query and based report on table

    S-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •