String formating

[Thompyt]

All,
How can I sort by rs!Status so it shows one time for all that is under it? Then is spits out the next status level with all that is under it?

How can I split up the [ChangeRequested] control? Put a Chr(9) & Chr(9) & at each line break?

Also I am trying to formats to be easier to read.

At the moment the below code does this Example:

Approved

CR 59 - Change
whatever changes
to
the changes

Approved

CR 60 - Disapprove
Whatever does changes
from
whatever changed

Set rs = CurrentDb.OpenRecordset("SELECT Status,CRNumbers,[ChangeRequested]FROM qryDayActEmail ORDER BY Status,CRID ASC")
rs.MoveFirst
While Not rs.EOF
strMsg = strMsg & rs!Status & vbCrLf & Chr(9) & "CR " & rs!CRNumbers & " - " & PlainText(rs![ChangeRequested]) & vbCrLf & vbCrLf
rs.MoveNext
Wend
rs.Close


I am trying to get this:

Approved

CR 59 - Change
whatever changes
to


the changes

CR 60 - Disapprove
Whatever does changes
from
whatever changed

[andy49]

Something similar to this? The code for 59 and 60 are more clearly defined so the Status is managed more efficiently

Set rs = CurrentDb.OpenRecordset("SELECT Status,CRNumbers,[ChangeRequested] FROM qryDayActEmail ORDER BY Status,CRID ASC")
 rs.MoveFirst
 While Not rs.EOF
select case rs!Numbers
case = 59
 strMsg = strMsg & rs!Status & vbCrLf & Chr(9) & "CR  " & rs!CRNumbers & " - " & PlainText(rs![ChangeRequested]) & vbCrLf & vbCrLf
case = 60
strMsg = strMsg &  vbCrLf & Chr(9) & "CR  " & rs!CRNumbers & " - " & PlainText(rs![ChangeRequested]) &  vbCrLf & vbCrLf 
End select

 rs.MoveNext

 Wend
 rs.Close

[Thompyt]

Andy49,
I see what you are doing in your suggestion. My fault for not explaining better lies in that rs!CRNumbers is always increasing and doesn't stay at 59 or 60.

[andy49]

Are they in ascending order? 59,60,61...............................125,126 and so on

[andy49]

How about something that tests the status.............

Set rs = CurrentDb.OpenRecordset("SELECT Status,CRNumbers,[ChangeRequested] FROM qryDayActEmail ORDER BY Status,CRID ASC")
 rs.MoveFirst
 While Not rs.EOF
select case rs!Status
case = "approved"
 strMsg = strMsg & rs!Status & vbCrLf & Chr(9) & "CR  " & rs!CRNumbers & " - " & PlainText(rs![ChangeRequested]) & vbCrLf & vbCrLf
case = "disapprove"
strMsg = strMsg &  vbCrLf & Chr(9) & "CR  " & rs!CRNumbers & " - " & PlainText(rs![ChangeRequested]) &  vbCrLf & vbCrLf 
End select

 rs.MoveNext

 Wend
 rs.Close

[Thompyt]

Andy49,
I see what you are doing in your suggestion. My fault for not explaining better lies in that rs!CRNumbers is always increasing and doesn't stay at 59 or 60.
Correct, but they can also be .01, .02.....
29
.01
.02
40
50
.01

And so on.
The numbers start with
CRNo & SubNo

CRNo + Format([SubNo]*0.01,"Fixed"))

I do have CRID as the Autonumber index, that always increments. But it doesn't mean that the different Status sets will always go from low to high.

[Thompyt]

The easiest way is to lay down the types of rules I abide by.

CRID is the autonumber index

I have levels of votes

[Level]
Level 1
Level 2
Level 3 Cat I
Level 3 Cat II

Next I have Status'
[Status]
Approve
Deny
Defer
Hold
Withdraw

Then I have types of votes
[Types]
CMB
TEWG
Field

Then we have the base change number = [CRNo], the sub change number based off from CRNo = [SubNo]

So each level can have a different Status and is not always in ascending numerical sequence, or even ascending CRID sequence.

I can sort through a query the status