Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 44

IIF function checking Text problem

  1. #16
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,598
    Quote Originally Posted by June7 View Post
    It calculates based on last record loaded. Try moving into Detail section.
    Ah good point, I missed that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21

    Exclamation

    Quote Originally Posted by June7 View Post
    This is not an aggregate calc and really doesn't belong in Report footer. It calculates based on last record loaded. Try moving into Detail section.

    However, the other textbox is apparently showing value from first record loaded.

    I have tested with my report and observe these behaviors.

    Editing report RecordSource query should be just as simple as calc in textbox.


    In my detail, the first line has species listed and the other lines do not. In the summary, I need to calculate totals differently if the species = beef....but I can't seem to get it to recognize the species in the summary, even though I display it right above my IIf statement and it displays the correct species. I think you might be correct that the IIF statement is looking at the last detail record which has no species listed and the textbox is showing the first record loaded which shows the species. I don't know how to move it into the detail when I am needing to calculate summaries of all the detail lines. Also, can you give me an example of how to do the last thing you said..."Editing report RecordSource query should be just as simple as calc in textbox". Thank you!

  3. #18
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,239
    Both are basic Access functionality.

    You can click, grab, drag textbox to Detail section or delete and create a new one.

    As for calculating fields in query, review https://www.fontstuff.com/access/acctut02.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #19
    rapsr59 is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2014
    Location
    El Paso, TX
    Posts
    7
    I created a small table named Species and added the a Text field "Species". I then entered "Beef", "Pork", "Chicken" And "Cat" in the Species field.

    I then created a query Called Check Result. I added the Species field in the first field of the query.
    To the right of the Species field, I then typed CheckResult:IIf([Species]="Beef","True","False")

    The Is The SQL View of the query.

    SELECT [Species], IIf([Species]="Beef","True","False") AS CheckResult
    FROM Species;

    When I ran the query It Returned True in the CheckResult field for Beef and False for the "Pork", "Chicken" And "Cat" data.

    Hope this helps you solve your problem.

    Best Wishes,


    Richard
    Last edited by rapsr59; 10-22-2019 at 10:54 PM. Reason: Forgot the SQL View

  5. #20
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    [QUOTE=June7;441320]This is not an aggregate calc and really doesn't belong in Report footer. It calculates based on last record loaded. Try moving into Detail section.

    However, the other textbox is apparently showing value from first record loaded.

    I have tested with my report and observe these behaviors.

    Editing report RecordSource query should be just as simple as calc in textbox.[/QUOTE}

    In my detail, the first line has the species in it and the other lines do not so I think you are right in saying that the footer calculates on the last record loaded, which does not have species in it and that the text box in the footer is showing the first record loaded, which does have the species. I don't know how to move what I am trying to do into the detail because I am summarizing amounts on all of the detail but need to do a different calculation if the species = beef.

    Also can you explain or show an example of "Editing report RecordSource query should be as simple as calc in textbox". I don't know what you mean.

    Thank you!

  6. #21
    rapsr59 is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2014
    Location
    El Paso, TX
    Posts
    7
    I created a small table named Species that contains one Field. Species that is a Text Field.
    I entered "Beef", "Pork", "Chicken" And "Cat" in the Species field. I Then ran the query Called "CheckResult"

    The Below Is The SQL View if the query.

    SELECT [Species], IIf([Species]="Beef","True","False") AS CheckResult
    FROM Species;

    The results was True for the Beef and False for the "Pork", "Chicken" And "Cat" data.

    Hope this help you resolve you problem.

    Best Wishes,


    Richard

  7. #22
    Join Date
    Apr 2017
    Posts
    984
    Quote Originally Posted by rapsr59 View Post
    SELECT [Species], IIf([Species]="Beef","True","False") AS CheckResult FROM Species;

    The results was True for the Beef and False for the "Pork", "Chicken" And "Cat" data.
    Results will be "True" or "False", not True or False!
    The real vales for True and False will be -1 and 0.

  8. #23
    rapsr59 is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2014
    Location
    El Paso, TX
    Posts
    7
    RockPicker

    I Changed the query to True and False as you will note below.

    SELECT [Species].[Species], IIf([Species]="Beef",True,False) AS CheckResult
    FROM Species;

    The result was that showed -1 for the Beef, which is True, and 0 which is False for the Pork. Chicken and Cat.

    Note:
    Microsoft Access uses the values of -1 For True and 0 for False .
    Microsoft Access also uses the values of Yes for True and No for False.
    Microsoft Access also uses the values of On for True and Off For False.

    I hope this helps to clarify the use of a Yes/No Data Type in an Access Table.

    Good Luck


    Richard

  9. #24
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    I know that this should be an easy fix but....this is a join query where I am joining unlike records together to get a total amount so the detail looks somewhat like:


    MemberID Species Weight GrossAmt FloorPrice TotalDue.......................................(Bl ockSaleDetail - There is one of these records per member)
    MemberID Null.......Null.....Null.........Null.........Tota lDue.......................................(AddOnD etail - There are zero to multiple records of this type per member)
    MemberID Null.......Null.....Null.........Null.........Tota lDue
    etc.

    The Summary is
    MemberID SumTotalDue



    When the species is "beef", I need to edit the SumTotalDue. Even though i display the species in the footer and it says "beef", I believe it is really set to "Null" in the IIf statement because it says "False". I believe that June7 had it right saying that the textbox showing the species is looking at the first record in the detail but the IIf statement is looking at the last record in the detail that has a Null.
    I need the detail to show in the order that is shown. And I don't know how to get the species from the detail to use it to make calculations in the footer.

    Hope that makes sense and that someone can help.

  10. #25
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,445
    In Post #14, Paul asked about you posting the dB.

    It would really be a help if you could attach the dB here....

    I don't know about others, but I an still having a hard time visualizing your dB structure and report.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  11. #26
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,239
    If you want to calculate a total based on species, why not use report Sorting & Grouping with aggregate calcs?

    Otherwise, do conditional aggregate calc in subreport footer section, like:

    =Sum(IIf(Species="Beef", [fieldname], Null))


    Really, time to provide db for analysis because I am ready to give up.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #27
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by June7 View Post
    If you want to calculate a total based on species, why not use report Sorting & Grouping with aggregate calcs?

    Otherwise, do conditional aggregate calc in subreport footer section, like:

    =Sum(IIf(Species="Beef", [fieldname], Null))


    Really, time to provide db for analysis because I am ready to give up.

    How do I provide the db for analysis? I was going to copy the Join Query...which I could do, and then a copy of the report...which I couldn't seem to do...sigh

    SELECT tblDonationDetail.MemberID,
    tblDonationDetail.BuyerID,
    tblBuyers.BuyerLastName, tblBuyers.BuyerFirstName, tblBuyers.BuyerAddress, tblBuyers.BuyerCity, tblBuyers.BuyerStateOrProvince, tblBuyers.BuyerPostalCode, tblBuyers.BuyerPhoneNumber,
    tblDonationDetail.BidAmount,
    [BidAmount]*CInt([Weight]) AS GrossAmt,
    [FloorPrice]*CInt([Weight]) AS FloorPriceAmt,
    IIf(([Disposition]="Resale" And [Species]="Beef") Or ([Disposition]="Resale" And [Species]="Swine") Or [Disposition]="Home/No Resale",[GrossAmt]-[FloorPriceAmt],[GrossAmt]) AS TotalDue,
    tblDonationDetail.Disposition,
    tblDonationDetail.CustomSlaughter,
    tblFinalSaleSheet.ID,
    tblFinalSaleSheet.Species,
    tblFinalSaleSheet.Order,
    tblFinalSaleSheet.TagNumber,
    tblFinalSaleSheet.FirstName, tblFinalSaleSheet.LastName, tblFinalSaleSheet.City,
    tblFinalSaleSheet.Weight,
    tblFinalSaleSheet.FloorPrice,
    tblMemberFees.MASFee1,
    tblMemberFees.MASFee2,
    tblMemberFees.MASFee3,
    tblMemberFees.MASFee4,
    tblMemberFees.MASFee5,
    tblMemberFees.MASFee6,
    tblMemberFees.MASFee7,
    tblMemberFees.MASFee8,
    tblMemberFees.MASFee9,
    tblMemberFees.MASFee10
    FROM (tblFinalSaleSheet LEFT JOIN tblMemberFees ON tblFinalSaleSheet.ID = tblMemberFees.MemberID) INNER JOIN ((tblBuyers LEFT JOIN qryBlockSalePymtSum ON tblBuyers.BuyerID = qryBlockSalePymtSum.BuyerID) INNER JOIN tblDonationDetail ON tblBuyers.BuyerID = tblDonationDetail.BuyerID) ON tblFinalSaleSheet.ID = tblDonationDetail.MemberID;
    UNION ALL SELECT [Members And Buyers].MemberID,
    [Members And Buyers].BuyerID,
    tblBuyers.BuyerLastName, tblBuyers.BuyerFirstName, tblBuyers.BuyerAddress, tblBuyers.BuyerCity, tblBuyers.BuyerStateOrProvince, tblBuyers.BuyerPostalCode, tblBuyers.BuyerPhoneNumber,
    Null,
    Null,
    Null,
    [Members And Buyers].DonationAmt AS TotalDue,
    Null,
    Null,
    Null,
    Null,
    Null,
    Null,
    tblMembers.FirstName, tblMembers.LastName, tblMembers.City,
    Null,
    Null,
    tblMemberFees.MASFee1,
    tblMemberFees.MASFee2,
    tblMemberFees.MASFee3,
    tblMemberFees.MASFee4,
    tblMemberFees.MASFee5,
    tblMemberFees.MASFee6,
    tblMemberFees.MASFee7,
    tblMemberFees.MASFee8,
    tblMemberFees.MASFee9,
    tblMemberFees.MASFee10
    FROM ((tblBuyers LEFT JOIN qryAddOnPymtSum ON tblBuyers.BuyerID = qryAddOnPymtSum.BuyerID) INNER JOIN ([Members And Buyers] INNER JOIN tblMembers ON [Members And Buyers].MemberID = tblMembers.MemberID) ON tblBuyers.BuyerID = [Members And Buyers].BuyerID) INNER JOIN tblMemberFees ON [Members And Buyers].MemberID = tblMemberFees.MemberID;
    Attached Files Attached Files

  13. #28
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,239
    Provide db per instructions at bottom of my post or click the button at top for more info.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #29
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by June7 View Post
    Provide db per instructions at bottom of my post or click the button at top for more info.
    Are you saying to send the whole program? How do people clean up the sensitive data and still have things work? Also, it is a mess. I wrote it years ago because I was tired of doing it all by hand and kind of piece mealed it together and always meant to go back and clean it up. I am too embarrassed to upload the whole mess. Doesn't my report and join query that I posted help?

    I have joined unlike type records together in the report based on the member key...1 Block Sale record per member with multiple Add On records per member (see report). I just need to know in the summary, if the Block Sale record said Beef and Resale and I also need to know if one of the multiple Add On records per member had a "Resale" buyer (Buyer First Name = "Resale). I tried coding it in the Join Query....which seems to work for the BeefResale question, which I assume is because there is only one record for the Block Sale. I can display that it is a Resale Buyer in the detail but it doesn't show up in the summary. I need to know it in the summary to do calculations differently.

    Is there some way to set a field to True and keep it True if it was True ONCE in the detail to show it in the summary? If there is a ResaleBuyer as one of the multiple Add On Records for a member, I would like to set ResaleBuyer = "True" and keep it true just for that member's summary.

    Please don't give up on me....I'm hoping you can use my report and JoinQuery instead of the "whole big crappy mess".....
    Thank you for your help.

  15. #30
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    I guess if I can't figure it out, I will drop the report into Excel and fix it there.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. The problem with the function sum()
    By end in forum Access
    Replies: 2
    Last Post: 01-31-2016, 03:41 PM
  2. Error Checking in a text box
    By Paul H in forum Forms
    Replies: 2
    Last Post: 07-23-2015, 01:02 PM
  3. Error 0 problem with error checking
    By SemiAuto40 in forum Programming
    Replies: 4
    Last Post: 12-07-2011, 12:21 PM
  4. Problem with IIF function
    By Hulk in forum Forms
    Replies: 3
    Last Post: 03-20-2011, 12:59 PM
  5. Problem checking for Null Value of a Textbox.
    By SIGMA248 in forum Programming
    Replies: 3
    Last Post: 08-09-2010, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums