Re-add main CR Number

[Thompyt]

CR_Number: IIf([Change Request].[Sub_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01," " & " .00"))
Output
1.00
1.01
1.02
2.00
3.00
3.01

CR_Numbers: IIf([Change Request].[Sub_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01," " & " .00"))
Output
1
.01
.02
2
3
.01

For the most part this is sufficient, but as you know there is always an exception.

When I am using the CR_Numbers, there may be a time where I don't have the main CR number displayed:

i.e.: .01

What I would like to do is have another code made and then put an iif statement around the CR_Numbers code to fix it. Or have the total code embeded.

If there is no main CR displayed, then the code should re-add the main CR number to the Sub CR number.

So if this is the out put:

3.00
.01

.05

Most won't know what .05 belongs to. Thus I would like to have it display as

3.00
.01

4.05

[June7]

I don't understand. If there is no CR number then what is there to add to the sub? Where does the 4 come from?

Show example raw data as well as desired output.

[Thompyt]

Output: Copy Of Chng_ReqQry.zip

SELECT [Change Request].CR_ID, [Change Request].CR_No, [Change Request].Sub_No, Format(([Change Request].[CR_No]+([Sub_No]*0.01)),"Fixed") AS CR_Number, IIf([Change Request].[Sub_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01,"    " & " .00")) AS CR_Numbers, [Change Request].Change_Type, [Change Request].Level, [Change Request].[Soft Level], [Level] & Chr(13) & Chr(10) & [Soft Level] AS Levels, IIf(IsNull([Soft Level]) Or [Soft Level]="",[Level],[Soft Level]) AS Levelz, [HB_Version] & Chr(13) & Chr(10) & [Approx_Page] AS [HB Vers], [Requestor] & Chr(13) & Chr(10) & [Sponsor] AS People, [Change Request].Action_Complete
FROM Switching INNER JOIN [Change Request] ON Switching.CR_ID = [Change Request].CR_ID
WHERE ((([Change Request].CR_No)<>0));

Look at CR 18 CR_Number & CR_Numbers.

If CR 18.00 is closed then it wont display, but if CR 18.01 is open, then I get the display .01. I need it to display when the Sub_No 0 is closed so that the client knows the main CR number. I want the others to continue as is the norm though.


The 4 comes from the main CR number in which the Sub_No is assigned. But you have asked the same question as the client. What CR is this sub CR referring to?



Better?