Let me know if post #14 is any help. It seems we posted at the same time. The code you have here has syntactical errors.
I can see some changes using your last post.
Subz: DLookUp("[CR_No]","[Change Request]","[CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) I can concanotate and make it work to an extent. BUT I need it where Action Complete =true and sub_no=0
I am thinking "Sub_No=" means where it does = 0. So I need to lookup Action_Complete.
Subz: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]=True" And "[CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) = all cells -1
Subz: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]=True" & "[CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) = all cells -#Error
I will go on faith that you tested the previous code and it is returning a value that you can somewhat recognize. If you need to add sub_no = 0 to the existing criteria, then why would you also want [Sub_No]=" & [Sub_No] - 1 ?Subz: DLookUp("[CR_No]","[Change Request]","[CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) I can concanotate and make it work to an extent. BUT I need it where Action Complete =true and sub_no=0
I need it to look to see if Sub_no=0 and Action_Complete=True, if it does then activate the code to make the Sub_no<>0 to display CR_No+(Sub_No)*.01) ie:
CR_No=1
Sub_no.........Action_Complete.......Output
0....................False......................1
0....................True.......................1
1....................False...................... .01
1....................True.......................1. 01
2....................False...................... .02
2....................True.......................2. 02
Then you need to start by creating a new query. This new query will be a subquery. In you new query, add the criteria so you get
Then add a column or columns that you can create a join(s) to your main query.Sub_no=0 and Action_Complete=True
From there, you will need an alias. You should be able to include your alias in your subquery. Maybe something like ...
MyAlias: [CR_No] & "_" & ([Sub_No]*.01)
See if you can get that working. I am not sure what you mean by "activate the code to make Sub_No to display". If you are talking about running an update query, you will need to do some additional stuff. If you just want to display the result, use an alias.
I already have the query called "Switching"
CR_Number: Format(([Change Request].[CR_No]+([Sub_No]*0.01)),"Fixed")
CR_Numbers: IIf([SUB_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01," " & " .00"))
I can use these 2 with an IIF statement IIF(Sub_no=0 and Action_Complete=True (For the main CR+Sub_no (Dlookup statement?)), CR_Number, CR_Numbers)
Yeah, so I am not seeing the need to go to the next or previous record. I am not going to be able to fully understand your data. I will say that it seems you need to join on a subquery. So I think post #20 is relevant. I say this because you already tried doing math with a single query. At least it seems you were using a single query named Switching.I can use these 2 with an IIF statement IIF(Sub_no=0 and Action_Complete=True (For the main CR+Sub_no (Dlookup statement?)), CR_Number, CR_Numbers)
The way you are explaining it, you need a subset. You do not need the previous or the next record. You need to find all of the children of "Something". So use post #20 to create that subset and use Switching as a main query.
If you do not need a subquery, you should be able to use post #20 as an exercise to concatenate the math with your other field. Hint, you do not need an IIf if there is criteria that eliminates the Sub_No = 0. Like I said before, break off little pieces. If a very small piece works, isolate that and create another small piece that works. Then, assemble your working pieces.
Main Query:
If I use this as an embedded Query:Code:SELECT [Change Request].CR_ID, [Change Request].CR_No, Format(([Change Request].[CR_No]+([Sub_No]*0.01)),"Fixed") AS CR_Number, IIf([SUB_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01," " & " .00")) AS CR_Numbers, IIf([Close_CR] And [Sub_no]<>0,[CR_Number],[CR_Numbers]) AS CR_Numberz, [Change Request].Action_Complete, IIf([Sub_No]=0,Format([Date_ID],"dd mmm yy"),"") AS Date_IDs, IIf(([Level] Or [Soft Level]="Level 1") And [Action_Complete]=False And [HR]<>0,"AORB OOB in Progress",Switch([AO_Vote] Like "Approve*","AO Approved",[AO_Vote] Like "Defer*","AO Deferred",[AO_Vote] Like "Den*","AO Denied",[AO_Vote]="Pending","AO Pending",[AO_Vote] Like "Withdraw*","AO Withdrawn",[AO_Vote]="Hold","AO Hold",[AO_Vote]="Open","Open")) AS AORB_Votes, IIf(([Level]="Level 3 Cat I" Or [Soft Level]="Level 3 Cat I" Or [Level]="Level 2" Or [Soft Level]="Level 2") And [Action_Complete]=False And [HR]<>0,"CCB OOB in Progress",Switch([O6_Vote] Like "Approve*","CCB Approved",[O6_Vote] Like "Defer*","CCB Deferred",[O6_Vote] Like "Den*","CCB Denied",[O6_Vote]="Pending","CCB Pending",[O6_Vote]="Withdrawn","CCB Withdrawn",[O6_Vote]="Hold","CCB Hold")) AS O6_Votes, IIf(([Level]="Level 3 Cat II" Or [Soft Level]="Level 3 Cat II") And [Action_Complete]=False And [HR]<>0 And Not IsNull([O6_Vote]),"GO OOB in Progress",Switch([GO_Vote]="Approved","GO Approved",[GO_Vote]="Denied","GO Denied",[GO_Vote]="Pending","GO Pending",[GO_Vote]="Deferred","GO Deferred",[GO_Vote]="Withdrawn","GO Withdrawn")) AS GO_Votes, IIf([CMB]>17 And ([Level]="Level 1" Or [Soft Level]="Level 1" And [Level]="Level 2" Or [Soft Level]="Level 2") And [AO_Vote]<>"Open","TEWG " & [Status 1],[Status 1]) AS Status, IIf(([Level]="Level 1" Or [Soft Level]="Level 1") And [Action_Complete]=False And [HR]<>0,"AORB OOB in Progress",Switch([AO_Vote] Like "Approve*","Approved",[AO_Vote] Like "Defer*","Deferred",[AO_Vote] Like "Den*","Denied",[AO_Vote]="Pending","Pending",[AO_Vote] Like "Withdraw*","Withdrawn",[AO_Vote]="Hold","Deferred",[AO_Vote]="Open","Open")) AS AORB, IIf(([Level]="Level 3 Cat I" Or [Soft Level]="Level 3 Cat I" Or [Level]="Level 2" Or [Soft Level]="Level 2") And [Action_Complete]=False And [HR]<>0,"CCB OOB in Progress",Switch([O6_Vote] Like "Approve*","Approved",[O6_Vote] Like "Defer*","Deferred",[O6_Vote] Like "Den*","Denied",[O6_Vote]="Pending","Pending",[O6_Vote]="Withdrawn","Withdrawn",[O6_Vote]="Hold","Deferred")) AS O6, IIf(([Level]="Level 3 Cat II" Or [Soft Level]="Level 3 Cat II") And [Action_Complete]=False And [HR]<>0 And Not IsNull([O6_Vote]),"GO OOB in Progress",Switch([GO_Vote]="Approved","Approved",[GO_Vote]="Denied","Denied",[GO_Vote]="Pending","Pending",[GO_Vote]="Deferred","Deferred",[GO_Vote]="Withdrawn","Withdrawn",[GO_Vote]="Hold","Hold")) AS [GO], 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]))))) AS [Status 1], IIf([Action_Complete]=False,"Open",(IIf(Not IsNull([GO]),([GO]),(IIf(Not IsNull([O6]),([O6]),([AORB])))))) AS Votes FROM Sub_Switch INNER JOIN [Change Request] ON Sub_Switch.CR_No = [Change Request].CR_No;
SELECT [Change Request].CR_No AS Close_CR, [Change Request].CR_No
FROM [Change Request]
WHERE ((([Change Request].Sub_No)=0) AND (([Change Request].Action_Complete)=True))
GROUP BY [Change Request].CR_No, [Change Request].CR_No
HAVING ((([Change Request].CR_No)<>0));
This is without a Dlookup statement
I can get what I want except now I cannot see any open CR_no's Its closer, but no cigar
I am not seeing any math in your sub query there. You have [Change Request].CR_No AS Close_CR and then you do not bother to do any math with it in the subquery object. Also, you do not include Close_CR within the SELECT statement of your main query.I can get what I want except now I cannot see any open CR_no's Its closer, but no cigar
Consistently, you have many problems with the examples you work worth. Can you get an alias the does the correct math? Can you do this in a query that does not have a dozen alias' and IIf statements? What I am saying here is, you jumped the gun by adding that subquery to the main query. Go back and get the math correct in the alias. Keep the WHERE statement so you do not get funny math with the 0 value.
ItsMe,
I understand where you are comming from. I am self taught with what I have so far and a LOT of help from this site. I am in NO way an expert and for the most part have NO clue on some of this stuff. Consider me an amature swimmer above his head in water.
With the Close_CR it set up all the CR's that were closed. I was trying to go around the Dlookup with an IIF. All the IIFs I have in this query is because I didn't want to clutter the Base Query with Code when I reference the Base query for my reports. This the name of Switching for this query.
I CANNOT get anAlias to do the corrct math as I do NOT completely understand the criteria portion of the Dlook-up function. I have looked on line with other links in the MS world from google. They do not have the "[field]=" & ..... of the criteria. I do not need to do math to have the look up if Action_complete is True where Sub_no=0, there is no math in that, it is true/false.
The math part is the making of the CR+Sub to equal #.## if it is Action_complete is true and Sub=0, or .## if sub=O and Action_Complete is false. That part of the Criteria in Dlookup I do NOT understand.
Perhaps I did not make it clear that there does not seem to be a need for the DLookup. I know I mentioned there were two options. I thought you were clear on deciding it is not needed or at least clear that performing math on the CURRENT record might be what you needed.I CANNOT get anAlias to do the corrct math as I do NOT completely understand the criteria portion of the Dlook-up function.
In an effort to determine if performing math on the CURRENT record might be what you need. Build a small and simple query. Do the math. Here I have built a query that I believe will work (and I mean work as part of the big picture not that it is the big picture). And, it concatenates the result to another field. Do yourself and me a favor and test this query. Try to make it work without muddying the waters and add a bunch of stuff to it. At the risk of repeating myself, you are trying to do too much at once. You cannot shingle a roof if you have not yet poured the foundation. I cannot help if I am clueless about what works and what does not. You cannot use the shot gun approach.
Code:SELECT [Change Request].[CR_No], [Change Request].[CR_No] & "_" & ([Change Request].[Sub_No]*.01) AS MyAlias FROM [Change Request] WHERE ((([Change Request].Sub_No)=0) AND (([Change Request].Action_Complete)=True))
Jumping back to the DLookup() function for a minute:
SYNTAX
DLookup(Expr, Domain, Criteria)
Expr Required String An expression that identifies the field whose value you want to return. Domain Required String A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name Criteria Optional Variant Criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE.
---------------------------------------------------------------
Looking at the DLookup in Post #13:
Expr = "[Action_Complete]"Code:MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]"=True And "CR_No=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1)
Domain = "[Change Request]"
Good so far...
The substring in RED is not within quotes which is causing an error.
The criteria should be
Criteria = "[Action Complete]=True And [CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1
Putting it together:
Code:MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]=True And [CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1)
---------------------------------------------------------------
Looking at the DLookup in Post #15:
Expr = "[Action_Complete]" And "[Sub_No]" <<--BAD - can return value from only 1 fieldCode: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)
Domain = "[Change Request]" <<--OK
Criteria = "[Action_Complete]"=True And "[Sub_No]"=0 & "[CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1 <<--BAD - 1) quotes in wrong place and 2) the "=0 equals sign should be to the left of the quote. The zero should not be there.
Again, it should look like:
Code:MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action Complete]=True And [CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1)
---------------------------------------------------------------
Be aware that DLookup() returns only 1 value that meets the criteria, but not necessarily the same value. If there are multiple records that meet the criteria, DLookup() could return a different value each time it executes.
You can run a query to check how many records meet the criteria.
Substitute values for the BLUE text and execute the query. If more than 1 record is returned, you need to add more criteria so 1 record is returned.Code:SELECT Action_Complete FROM Change Request WHERE Action Complete = True And CR_No = " & [CR_No] & " AND Sub_No = " & [Sub_No]-1
ItsMe,
The code you gave me does the same as my subquery. It blocks all the Action_complete=False.
snafu,
MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action _Complete]=True And [CR_No]=" & [CR_No] & "AND [Sub_No]=" & [Sub_No]-1)
Gives me #Error for all MyAlias fields and "The object doesn't contain the Automation object "Action_Complete."
ItsMe,
The code you gave me does the same as my subquery. It blocks all the Action_complete=False.
snafu,
MyAlias: DLookUp("[Action_Complete]","[Change Request]","[Action _Complete]=True And [CR_No]=" & [CR_No] & "AND [Sub_No]=" & [Sub_No]-1)
Gives me #Error for all MyAlias fields and "The object doesn't contain the Automation object "Action_Complete."
Code:SELECT [Change Request].CR_No, Format(([Change Request].[CR_No]+([Sub_No]*0.01)),"Fixed") AS CR_Number, IIf([SUB_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01," " & " .00")) AS CR_Numbers, DLookUp("[Action_Complete]","[Change Request]","[Action _Complete]=True And [CR_No]=" & [CR_No] & "AND [Sub_No]=" & [Sub_No]-1) AS MyAlias, [Change Request].Action_Complete, IIf(([Level] Or [Soft Level]="Level 1") And [Action_Complete]=False And [HR]<>0,"AORB OOB in Progress",Switch([AO_Vote] Like "Approve*","AO Approved",[AO_Vote] Like "Defer*","AO Deferred",[AO_Vote] Like "Den*","AO Denied",[AO_Vote]="Pending","AO Pending",[AO_Vote] Like "Withdraw*","AO Withdrawn",[AO_Vote]="Hold","AO Hold",[AO_Vote]="Open","Open")) AS AORB_Votes, IIf(([Level]="Level 3 Cat I" Or [Soft Level]="Level 3 Cat I" Or [Level]="Level 2" Or [Soft Level]="Level 2") And [Action_Complete]=False And [HR]<>0,"CCB OOB in Progress",Switch([O6_Vote] Like "Approve*","CCB Approved",[O6_Vote] Like "Defer*","CCB Deferred",[O6_Vote] Like "Den*","CCB Denied",[O6_Vote]="Pending","CCB Pending",[O6_Vote]="Withdrawn","CCB Withdrawn",[O6_Vote]="Hold","CCB Hold")) AS O6_Votes, IIf(([Level]="Level 3 Cat II" Or [Soft Level]="Level 3 Cat II") And [Action_Complete]=False And [HR]<>0 And Not IsNull([O6_Vote]),"GO OOB in Progress",Switch([GO_Vote]="Approved","GO Approved",[GO_Vote]="Denied","GO Denied",[GO_Vote]="Pending","GO Pending",[GO_Vote]="Deferred","GO Deferred",[GO_Vote]="Withdrawn","GO Withdrawn")) AS GO_Votes, IIf([CMB]>17 And ([Level]="Level 1" Or [Soft Level]="Level 1" And [Level]="Level 2" Or [Soft Level]="Level 2") And [AO_Vote]<>"Open","TEWG " & [Status 1],[Status 1]) AS Status, IIf(([Level]="Level 1" Or [Soft Level]="Level 1") And [Action_Complete]=False And [HR]<>0,"AORB OOB in Progress",Switch([AO_Vote] Like "Approve*","Approved",[AO_Vote] Like "Defer*","Deferred",[AO_Vote] Like "Den*","Denied",[AO_Vote]="Pending","Pending",[AO_Vote] Like "Withdraw*","Withdrawn",[AO_Vote]="Hold","Deferred",[AO_Vote]="Open","Open")) AS AORB, IIf(([Level]="Level 3 Cat I" Or [Soft Level]="Level 3 Cat I" Or [Level]="Level 2" Or [Soft Level]="Level 2") And [Action_Complete]=False And [HR]<>0,"CCB OOB in Progress",Switch([O6_Vote] Like "Approve*","Approved",[O6_Vote] Like "Defer*","Deferred",[O6_Vote] Like "Den*","Denied",[O6_Vote]="Pending","Pending",[O6_Vote]="Withdrawn","Withdrawn",[O6_Vote]="Hold","Deferred")) AS O6, IIf(([Level]="Level 3 Cat II" Or [Soft Level]="Level 3 Cat II") And [Action_Complete]=False And [HR]<>0 And Not IsNull([O6_Vote]),"GO OOB in Progress",Switch([GO_Vote]="Approved","Approved",[GO_Vote]="Denied","Denied",[GO_Vote]="Pending","Pending",[GO_Vote]="Deferred","Deferred",[GO_Vote]="Withdrawn","Withdrawn",[GO_Vote]="Hold","Hold")) AS [GO], 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]))))) AS [Status 1] FROM [Change Request];
I get:Code:SELECT [Change Request].CR_ID, [Change Request].CR_No, Format(([Change Request].[CR_No]+([Sub_No]*0.01)),"Fixed") AS CR_Number, IIf([SUB_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01," " & " .00")) AS CR_Numbers, IIf([SUB_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01,"" & " .00")) AS CR_Numb, DLookUp("[CR_No]","[Change Request]","[Action_Complete]=True AND [CR_No]=" & [CR_No] & " AND [Sub_No]=" & [Sub_No]-1) AS Subz, [Subz]+[CR_Numb] AS CNumberz, [Change Request].Action_Complete FROM [Change Request];
Its close, real close. But now I am off 1 Sub_No. Look at CR 18. 18.00, 18.01, 18.04 are closed, 18.02 and 18.03 are open. 18.01 and 18.02 are displayed instead of 18.02 and 18.03.
I am sorry to say, I am unable to offer further assistance on this topic. I do not know how to communicate the messages I feel are important.