Page 1 of 4 1234 LastLast
Results 1 to 15 of 46
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Changing the numerical display

    Code in Access: CR_Numbers: IIf([Change Request].[Sub_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01," " & " .00"))

    results in:
    4 ([CR_No] = 4, [Sub_No] = 0)
    .01 ([CR_No] = 4, [Sub_No] = 1)
    .02 ([CR_No] = 4, [Sub_No] = 1)
    .03.([CR_No] = 4, [Sub_No] = 3)........................

    This works great until CR 4 and .01 closes ([Action_Complete]=True) and the display results in


    .02
    .03

    How can I get it to display the main CR Number on the highest remaining Sub_No?
    4.02
    .03........

    I am thinking the trigger would be [Action_Complete] as this is what I use to remove the completed CR_No/Sub_No from the report. Also to add a twist if the CR_No+Sub_No is in a different category. I wouyld like the seperated CR_no to act the same.

    Example:
    Deferred
    4.01

    Approved
    4
    .02
    .03

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    This works great until CR 4 and .01 closes ([Action_Complete]=True) and the display results in
    I am having trouble understanding what your objective is. I guess you are working in a query. Perhaps a subquery with aggregate calcs is a solution.

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ItsMe,
    Yes it is an a query [Switching]. Normal status for Action_Complete=False, but that is in another query [ChngreqQry]. All information derives from [Change Request] table. What I am trying to do is to have the CR_No show up if the CR 4 is closed [Action_Complete]=True and the Sub_No is still open.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    What I am trying to do is to have the CR_No show up if the CR 4 is closed [Action_Complete]=True and the Sub_No is still open.
    Can you use a WHERE clause that includes these parametes?
    WHERE [Action_Complete]=True AND Sub_No = "SomeValue"

    Then with that query, create an alias that does math on Sub_No and concatenates '.00' to the result?

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I am not that good a programmer, thus the question as to how. I thought of that, and all it did for me when I tried it was to make all the closed CR/Sub to be #.## and still had the missing CR_No for the ones left open.

    CR_Numbers: IIf([Action_Complete]=False,(IIf([Change Request].[Sub_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01," " & " .00"))),(Format([Sub_No]*0.01,[CR_no] & "" & " .00")))

    The effect i get is that all Closed CR'sare displayed as #.## and not what I need.

    I think I need some sort of Dlookup code for it to check to see if the next higher CR_No/Sub_No combination is closed or open, then if closed put the CR_No in front of the next highest Sub_No.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I am not sure about your data structure. So, you may need information from the previous or subsequent record.
    https://support.microsoft.com/en-us/kb/101081

    As far as applying criteria to a query, you can use the query designer. While in design view, type an expression in the criteria field for a specific column. For example, Is Not Null. Then you can switch to Datasheet View to see how the criteria affects the query. Once you understand how it affects the retrieval of records, you can switch to SQL view to see how it was done.

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    How do you look at the main CR_number with next lowest Sub_Number to see if it is closed/open or if the status is different from current CR?


    IE current record you are on = CR_NO=4 SUB_No=2 you want to look at the next lower Sub where CR_No = 4 Sub_No=1 to check to see if it is still open or if the status is different from current one.

    Status is Approved/Open/Denied/Withdrawn.......

    Thanks

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    ... to see if it is still open or if the status is different from current one
    I would start (not the complete solution, just something to start with) by using an alias to simply retrieve where CR_No = 4 Sub_No=1 ...

    In design view of your query, I would add syntax similar to the following. So paste the following into the grid, in a new column, in the Field row.
    Code:
    MyAliasForSub: DLookUp("[CR_No]","[Change Request]","[CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1")
    I do not know your data structure and I am only guessing at what you want/need. I do know this is not the final result. However, look at what this example does in Datasheet view of your query. See if it is retrieving the correct record. I do not know what your Keys are and it may be necessary to use another query to retrieve the correct sub-no. In other words, this is using a table and you may need to further define the relation between sub and num.

  9. #9
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    (Column 1) CR_ID - Autonumber
    (Column 2) CR_No from Table [Change Request].[CR_No]
    (Column 3) Sub_No from Table [Change Request].[Sub_No]
    (Column 4) CR_Number: Format(([Change Request].[CR_No]+([Sub_No]*0.01)),"Fixed")
    (Column 5) CR_Numbers: IIf([SUB_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01," " & " .00"))
    (Column 6) MyAliasForSub: DLookUp("[CR_No]","[Change Request]","[CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) provides the following output (Column 4):
    (Column 7) Action_Complete from Table [Change Request].[Action_Complete]
    (Column 8) Status 1: IIf([HR]=0 And IIf(Not IsNull([GO_Votes]),([GO_Votes]),(IIf(Not IsNull([O6_Votes]),([O6_Votes]),([AORB_Votes])))),IIf(Not IsNull([GO_Votes]),([GO_Votes]),(IIf(Not IsNull([O6_Votes]),([O6_Votes]),([AORB_Votes])))),IIf(Not IsNull([GO_Votes]),([GO_Votes]),(IIf(Not IsNull([O6_Votes]),([O6_Votes]),([AORB_Votes])))))

    Click image for larger version. 

Name:	Outcome4.png 
Views:	28 
Size:	66.5 KB 
ID:	22984

    MyAliasForSub may help later if I use this as a concantenated output. What I am looking for is.

    We will use as an example: CR_Number 1.00 and 1.01 / CR_Numbers 1 and .01

    A. If CR_Number 1.00 is closed and CR_Number 1.01 is open, then I want the 1.01 to display.

    B. If CR_Number 1.00 is AO Approved (Status 1) and CR_Number 1.01 is AO Denied (Status 1), then I want the 1.01 to display.

    C. Id all the CR_Number are open/closed then it qould be displayed as CR_Numbers.

    The same would happen for all subsequent sub numbers.

    Did I muddy the waters?

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    MyAliasForSub may help later if I use this as a concantenated output.
    That is the idea. Use that as a first step. I believe you when you say you have several things that you need to take care of. That is the very nature of an application. If I were to manage all of the issues with this one query that need to be addressed, I would tackle them one at a time. I suggest you approach the big picture the same way I would; Bite off small pieces. In other words, do not expect me to work for you. You need to help yourself. We are here to assist you.

  11. #11
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]"=True) This would be used to look up each instance of the field Action_Complete in the Change Request where Action_Complete is TRUE?
    .............................Expression........... ....Domain..................Criteria

    CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1)
    ..Criteria...| Understood

    Not really understanding:

    Concantenate &
    Field [CR_No]
    Concantenate &
    ???? "
    Logic AND
    Field [Sub_No]=
    ???? "
    Concantenate &
    Field [Sub_No]-1)

    Can someone clarify?

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    The code in post # 8 is my best guess how you might implement the example I linked to in post #6. The idea is to get information from the subsequent record in your query. I provided the link in post #6 in response to your comment that you needed a value from a subsequent record.

    This code you provided is not going to retrieve information from a subsequent or previous record because it is not subtracting 1 from a numeric Key value.
    DLookUp("[Action_Complete]","[Change Request]","[Action Complete]"=True)

    If you look at the code I provided, you might notice there is some math happening ...
    ...AND [Sub_No]=" & [Sub_No]-1

    I am not sure if this is the subject/topic you are currently working on.

  13. #13
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Subsequent oir previous? I can see the math with you taking the sub_no and subtracting 1. I am gathering this is looking to see what the previous Sub_no is. I understand this DLookUp("[Action_Complete]","[Change Request]","[Action Complete]"=True) is only looking for to see if that records Action_Complete is true or not, and doesn't have the math involved.

    Adding the code after that is where it confises me.

    & CR_No means the value of the CR

    " AND [Sub_No]=" & [Sub_No]-1") means look at the Sub_No's and place the value of the CR instead.

    MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]"=True And "CR_No=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) gives me a constant -1 output.
    MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]"=True & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) = no output blank/null

    I am looking at having 3 fields driving the change. Action_Complete=TRUE and Sub_No=0 Status 1 = all the same to cause the output to be 4.01 instead of .01 where 4.00 is closed. When all are closed then it reverts back to the norm as described in CR_Numbers.

    How do I look up the multiple fields? DLookUp("[Action_Complete]" AND "[Sub_No]" AND "[Status 1]","[Change Request]","[Action Complete]"=True AND "[Sub_No]"=0 AND "[Status 1]"= (Another Dlookup?) & ???

    I know we have to do the DLOOKup, but wouldn't there be an IIF statement? Iff(Action_Complete=True and Sub_No=0, DLookUp("[Action_Complete]" AND "[Sub_No]" AND "[Status 1]","[Change Request]","[Action Complete]"=True AND "[Sub_No]"=0 & [CR_Number], CR_Numbers)

    the bold part is where I am having issues with.

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I am looking at having 3 fields driving the change.
    Yup, I think you should concentrate on getting some sort of result considering only one field.

    You are correct, ...AND [Sub_No]=" & [Sub_No]-1 is looking to the previous.

    MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]"=True And "CR_No=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) gives me a constant -1 output.
    MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]"=True & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) = no output blank/null
    You have a couple different issues with each of your attempts. I suggest you simplify things and retrieve the value of Action_Complete from the Change Request table WHERE Sub_No = the current Sub_No plus one.

    So something like this ...
    Code:
    MyAlias: DLookup("[Action_Complete]", "[Change Request]", "[Sub_No]=" & [Sub_No] + 1)
    If you can get that to work, where you actually get a value from one of the records within table, then you can add on to it. I see the next step as adding a key value to your criteria. This is why I was suggesting something like
    Code:
    "[CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1"
    But, adding another field to the criteria is not an easy thing to do. The syntax has to be perfect. Also, we may not be able to use the Change Request table. We may need to create another query that provides the appropriate subset and replace Change Request with the name of a query.

  15. #15
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Alias: DLookUp("[Action_Complete]" And "[Sub_No]","[Change Request]","[Action_Complete]"=True And "[Sub_No]"=0 & "[CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1)

    Doesn't get me anywhere

Page 1 of 4 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