Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 46
  1. #31
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    With this code I can get closer to what I need. CR_Numb: IIf([SUB_No]>0 And [Action_Complete]=False,[CR_Number],[CR_Numbers]) without using the Dlookup. How can I set up the trigger of:

    CR_No & Sub_No format for Sub_No<>0 and Action_Complete=True is CR_Number: Format(([CR_No]+([Sub_No]*0.01)),"Fixed") IF Sub_No=0 and Action_Complete=True else CRNumbers: IIf([SUB_No]=0,Format([CR_No],"#"),Format([Sub_No]*0.01," " & " .00"))

  2. #32
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Snafu et all,
    Table=[Change Request]
    Controls= Sub_No, Action_Complete

    Is this the correct code for looking up Sub_No=0 where Action_Complete=True

    Alias: DLookUp("Sub_No","[Change Request]","Sub_No=0 And Action_Complete=True")

    Thanks

  3. #33
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Is this the correct code for looking up Sub_No=0 where Action_Complete=True
    I suppose it is the correct code, yes. However, DLookup does not consider 'Controls' and their values. DLookup considers Domains. It will look at data within a table or a query object.

    Also, why retrieve the value of Sub_No where Sub_No=0? I can tell you from where I sit that Sub_No=0.

  4. #34
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I want the Sub-CRs Sub_no>0 to look at the base Sub_no to see of it is closed and the current Sub_No is open. Later to check if it is a different status. If it is then the CR_No + (Sub_no )*.01 formatting will change.

    Thus my reasoning for it to look at Sub_no=0 I'm not too logical in my though processing, so if there is a different way of reasoning/logic.

  5. #35
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I want the Sub-CRs Sub_no>0...
    Then use a criteria like ...
    "Sub_No>0 And Action_Complete=True"

  6. #36
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Alias: DLookUp("Sub_No","[Change Request]","Sub_No>0 And Action_Complete=True") gives me a 1 for all fields

    Alias: DLookUp("Sub_No","[Change Request]","Sub_No<>0 And Action_Complete=True") gives me a 1 for all fields

    Alias: DLookUp("Sub_No","[Change Request]","Sub_No=0 And Action_Complete=True") gives me a 0 for all fields

    Alias: DLookUp("Sub_No","[Change Request]","Sub_No<0 And Action_Complete=True") gives me a null for all fields

    I don't see any diferentiation betwwen it being closed or open. Unless 1 = False for Action_Complete / 0 = True for Action_Complete /Null = no Sub_No's

  7. #37
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    CR_Numbers: IIf([SUB_No]=0,[CR_No],Format([Sub_No]*0.01," " & " .00"))
    CR_Number: Format(([CR_No]+([Sub_No]*0.01)),"Fixed")
    Alias: DLookUp("Action_Complete","[Chng_ReqQry]","Sub_No=0 And Action_Complete=False")
    Numberz: IIf([Sub_No]<>0 And [Action_Complete]=False<>[Alias],[CR_Number],[CR_Numbers])
    Action_Complete
    CR_No

    Gives me this out come.

    I am looking at 4, 10, 18, and 22

    Open CR's 4 and 18 are working as I planned. 10 and 22 starts fine, but is not correct. The sub CRs look like column 1

    Closed CR's are wooking as intended. I am thinking it is my Alias code.

    Click image for larger version. 

Name:	Out2.png 
Views:	20 
Size:	46.9 KB 
ID:	23092

  8. #38
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    OK playing around I get these 2 codes where it works for certain part, but not others, how can I combine these to get the outcome?

    This gets me where there are sub_No 2 & 3 show the CR + the Sub CR when sub_no=0 and Action_Complete is true.
    Expr2: IIf([Sub_No]=0,[CR_No],IIf([Sub_No]<>0 And [Action_Complete]=False,[CR_Number],[CR_Numbers]))

    This gets me all the others correct when Sub_No=0 is False then all the open CR's are shown correctly.
    Expr4: IIf([Sub_No]=0,[CR_No],IIf([Sub_No]<>0 And [Action_Complete]=True,[CR_Number],[CR_Numbers]))

  9. #39
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I keep coming back ItsMe code changed a little: MyAlias: DLookUp("[Sub_No]","[Change Request]","Action_Complete = True And [Sub_No]=0" & [Sub_No] & " AND [CR_No]=" & [CR_No])

    This puts a null where the CR_No+Sub_no has action_complete=false. Which is good. If I have CR_no+Sub_no 18.00 is closed but 18.02 and 18.03 is open - it still makes then as originally designed as .02 and .03, just the same as all the other open CR_No+Sub_No's. I cannot have both?

    The difference is that the CR_No + Sub_No=0 of that series is Action_Complete=True in comparison where CR_No + Sub_no=0 is Action Complete=False.

    How do I get it to look at the state of Action_Complete if Sub_no=0 to set up the formatting of any subsequent Sub_No's and Action_Complete=False


    I was thinking Dlookup was the way, Is there an alternate?


    Thanks

  10. #40
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Attached is a simplified version of what I am trying to do.

    Of you will note that in CR18 I have made a comment in the Change Requested part denoting it is CR18 and nestled between CR10 and CR24. Of which you would not easily identy without the comment. Thus the need to have the main CR-No added when Sub_CR=0 and it is closed.

    Written out: If records of CR_No set where Sub_No=0 and Action_Complete=True or current record <> Status where Sub_No=0, then Sub_No = CR_Number else CR_Numbers

    Start of coding:
    IIF([CR_No] record set) and [Sub_No]=0 and [Action_complete]=True or ([CR_No] record set) and [Sub_No]=0 And [Status]<>current record [Status],[CR_Number],[CR_Numbers])



    I think I see where the subquery previously mentioned fits it. It would work for both both situations.
    Attached Files Attached Files

  11. #41
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ItsMe,
    I tried the subquery with exists, looks like it doesn't do anything.
    Code:
    SELECT Chng_ReqQry.CR_Number, Chng_ReqQry.CR_Numbers, Chng_ReqQry.Action_Complete
    FROM Chng_ReqQry
    WHERE (((Exists (SELECT Chng_ReqQry.CR_No
    FROM Chng_ReqQry
    WHERE (((Chng_ReqQry.Sub_No)=0) AND ((Chng_ReqQry.Action_Complete)=True))
    GROUP BY Chng_ReqQry.CR_No
    HAVING (((Chng_ReqQry.CR_No)<>0))))<>False) AND ((Chng_ReqQry.CR_No)<>0));

  12. #42
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    The top is the edited pdf of what I am trying to get to. It is the highlighted CR 18.02. The bottom part is the output of the report I normally get.

    Of CR_No= 18, Sub_No=0: Action_Complete=True Therefore it is NOT displayed on the Report

    Now CR_No= 18, Sub_No=2: Action_Complete=False So this is displayed as .02, but is stuck on at the end of CR 10 with its subsets.

    Click image for larger version. 

Name:	Example.png 
Views:	11 
Size:	33.4 KB 
ID:	23141


    Click image for larger version. 

Name:	ExamplQRY.png 
Views:	11 
Size:	30.9 KB 
ID:	23144

    The above numbering system is from below (CR_Numbers) I have black boxed CR 10 and 18 for referencing. CR_No=10 and Sub_no=0 is redlined and showing that Action_Complete=False (No Checkmark). CR_No=18 and Sub_No=0 is redlined and showing where Action_Complete=True (Checkmarked). Since 18.00 is checkmarked it is not on this report. It is taken out by the query running the report.

    The whole thing revolves around whatever CR_No and where (Sub_no=0 AND Action_Complete=True). If the criteria is met, then it will not show on the report. Since it will not show on the report, I need to have the CR_Number column replace that instance.

  13. #43
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Are you saying you still want a CR 18 identifier to show in report with the .02 and .03 records below it even though the 18 record is filtered out of the report?

    Or

    Do without the 18 identifier row and just show the concatenated values instead of .02 and .03? This means condition for display is dependent on data in another record - whether or not the CR 18 record is Complete. This can be tricky and involves a subquery or DLookup().
    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. #44
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I want the 18 added to the .02, but nowhere else.

  15. #45
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Using a DLookup():

    IsCRNumComplete: DLookup("Action_Complete", "[Change Request]", "CR_No = " & [CR_No] & " AND Sub_No=0")

    And expression using that value:

    IIf([IsCRNumComplete] And [sub_no]<>0,[CR_No] & Trim([CR_Numbers]),[CR_Numbers])

    Both of these can be done in queries or in textboxes on report.
    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.

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

Similar Threads

  1. Numerical Formatting
    By Thompyt in forum Programming
    Replies: 2
    Last Post: 12-12-2014, 06:28 PM
  2. Replies: 2
    Last Post: 11-18-2013, 10:30 AM
  3. Numerical Issues
    By kwooten in forum Queries
    Replies: 3
    Last Post: 01-10-2012, 01:15 PM
  4. Replies: 7
    Last Post: 10-03-2011, 12:44 PM
  5. Changing information display method in forms
    By mrbaggins in forum Forms
    Replies: 5
    Last Post: 10-21-2010, 06:00 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
  •  
Other Forums: Microsoft Office Forums