Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Values (A2K SR1)

    I've designed a purchasing database system that, among other things, prints purchase order modifications. For a variety of reasons, a base purchase order may be modified any number of times to increase funding to a particular vendor. Purchase order modifications are denoted by 'Modification No. X', with X starting at 1 and increasing by 1 for each subsequent modification that is issued.

    I have designed a report that serves as the purchase order modification. On this report, I have 4 unbound fields: 1) Original purchase order value; 2) Total value of previous modifications; 3) Value of this modification; and 4) Revised purchase order total. I populate Field 1 with a query that returns the base PO value. Field 4 is supposed to be the sum of Fields 1, 2, and 3. My problem is Field 2, Total value of previous modifications. I have created a query that returns the total value of each of the modifications issued under a particular purchase order and it is easy to populate Field 2 with the sum of all of those values. However, I don't always want to populate that field with the sum of ALL of the modifications that have been issued. If, for example, I want to print out Modification No. 6 to a purchase order with 10 modifications, I want Field 2 to only be populated by the sum of Modification Nos. 1 through 5, NOT Modification Nos. 1 through 10. In this example, how do I select only Modification Nos. 1 through 5 to provide the value I need for Field 2?

    I realize this is difficult to explain, but I hope I've simplified my dilemma enough to be understandable. Thank you in advance for any help/guidance.

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Values (A2K SR1)

    What method of input are you using to determine which Modifications to include in your query and/or calculation? Will the Modifications always be a contiguous range (i.e. 1-5, 2-9, but not 1,3,5, and 6)?

    I would suggest using two combo boxes on a form that populate with the maximum number of Modifications for the selected PO (such as 10). Use one as the low number and the other as the high number.

    Then in your OnOpen event in the Report, fetch the values of the two combo boxes = DSUM("[Value]","tblPO","[ModID]>=[Forms]![frmWhatever]![cboBottom] AND [ModID]<=[Forms]![frmWhatever]![cboTOP]")

    HTH <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Values (A2K SR1)

    I select a particular purchase order modification for printing by entering the primary key for that modification into a parameter query. By the time I'm ready to print a modification, the Modification No. has already been assigned by the user and is part of the record that populates the report. I just need to place the sum of all previous modifications onto the report.

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Values (A2K SR1)

    AHhh - I see....

    In that case, just leave off the "AND..." part of the DSUM function and have it pull the ModID from the report. Instead, it would read:

    DSUM("[Value]","tblPO","[ModID]<[Reports]![ModReport]![ModificationID]")

    This should pick up all previous modification numbers. For example, if your current ModID is 7, this will return a sum for ModIDs 1-6.

    HTH <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Values (A2K SR1)

    Thanks, Mark. I'll give this a try. Will your solution work even though my purchase order modification values aren't in a table, per se, but are returned using a query?

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Values (A2K SR1)

    Sure! The D-Functions treat SELECT queries the same way they do tables--a Recordset is a Recordset is a Recordset...

    Post back if you have any problems.

    Good luck!

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Values (A2K SR1)

    Mark,

    My query, qryPOMODVALUES, returns the value (TotalCost) of each modification (ModificationNumber) issued under a particular purchase order, as well as the Purchase Order number (PONumber). I've checked the query results and they are correct. Here's a sample data set from the query:

    PO J01129: $542,694.00
    Mod 1: $23,400.00
    Mod 2: $123,499.00
    Mod 3: $63,450.00
    Total PO value: $753,043.00

    I place the following code in the Control Source for the Total Value of Previous Mods field in order to retrieve the sum of all modifications issued prior to the latest. Because the query returns the purchase order modifications issued under ALL purchase orders, I had to modify the criteria to identify the specific purchase order number as well as all modification numbers less than the latest one:

    =DSum("[TotalCost]","qryPOMODVALUES","[PONumber] = [Reports]![rptPOMOD]![PONumber] AND [ModificationNumber]<[Reports]![rptPOMOD]![ModificationNumber]")

    However, the returned value just doesn't add up to the actual value. For example, using the sample dataset above, when I generate the report for PO Modification No. 3, the Value of Previous Mods field shows a total of $126,900.00 instead of $146,899.00 as it should. I have no idea where the $126,900.00 is coming from. The qryPOMODVALUES doesn't even return values that add up to that total!

    As you suggested, I tried placing the code in the report's On Open event, but it won't work there because it returns a Null value.

    Also, when your code does return a Null value, such as when I'm printing the report for Purchase Order Modification No. 1 (obviously, there haven't been any previous modifications issued, so the Value of Previous Mods field will be blank), how do I make $0.00 display in the Value of Previous Mods field, which is currently an unbound text box?

    I hope this isn't too confusing. Thanks for your help and guidance.

Posting Permissions

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