Data Manipulation

[Thompyt]

Example I am working from:

I would like to make changes on how the numbers are displayed and how the Change type is displayed.

CR_Numbers: Format([CR_No]+([Sub_No]*.001), "Fixed") is what I am using to get the numbers at this point. What I would like to do for the number is

Case A
CR_No =333 Sub_No =0 Display the first Set of the CR as 333

CR_No =333 Sub_No =1,2,3.... Display the CR as ....01, ...02 (where the . = a space where the 333 would normally reside)

It would look like:


333
......1
......2
......3

Also if the Change Type is the same throughout the CR series, how could I have it display in the first CR where it =.00?

[ssanfu]

CR_No =333 Sub_No =1,2,3.... Display the CR as ....01, ...02 (where the . = a space where the 333 would normally reside)

CR_Numbers2: IIf([Sub_No]=0,Format([CR_No],"000"),Format([Sub_No]*0.01,"     .00"))

Is this close?
Note: there are 5 spaces between the (bold) quote and the .00 for alignment purposes.

Also if the Change Type is the same throughout the CR series, how could I have it display in the first CR where it =.00?

I don't understand this question.

[Thompyt]

Thanks Snafu, I did one little change to CR_Numbers: IIf([Sub_No]=0,Format([CR_No],"#"),Format([Sub_No]*0.01," " & ".00")).
I put CR_No format to "#" to get rid of the 0's preceding the number.

That works great in the datasheet view, CR_Numbers sorted ascending, but now when I view in a report, all the .01 are first. I have it grouped on another field and then sorted by CR_Numbers by entire value from smallest to largest.

Corrected issue, by sorting on the CR_ID field. Which will work as long as the CR's stay in line with the CR and are not put in out of sequence.

The second question was referring to where the word "Shade" came after every CR_No. If it is the same for the whole CR then could there be a way to leave all the others blank, but displayed where the Sub_No (.00) = 00?

I figured it out - Change_Types: IIF(([Sub_No]=.00), (Change_Type), "")

[Thompyt]

Snafu, et all,
If I used this code

Change_Types: IIF(([Sub_No]=.00), (Change_Type), "")

It will delete all but the first Change_Type. how would I go about it if it was to delete only the Change_Types which were the same as the first?

[Snayjay]

I think I understand what you are wanting... in my mind the easiest fix is to use a 2nd field in your table.

FIELD1 | FIELD2
333.00 | 333
333.01 | ...01
333.02 | ...02
333.03 | ...03
334.00 | 334

And instead of displaying FIELD1 on the form, you display FIELD2.

Upon user entry of the FIELD1 data, just do a check to see if it's a .00 and apply the correct text to an invisible FIELD2.

If right(FIELD1.value,3) = ".00" then
   FIELD2 = LEFT(FIELD1, (LEN(FIELD1) - 3))
Else
   FIELD2 = "..." & RIGHT(FIELD1,2)
End If

Still sort your display by FIELD1 so everything is in order, but display FIELD2.

Does that work for what you want?

[Thompyt]

Snayjay, I
like it and actually understand your code. How would I use it in VBA? I would have to put it on the forms VBA?

[Snayjay]

You would need to put it where the record is created and the CR_No and Sub_No are entered or chosen by a user. I could be wrong but it looks like a User is choosing or typing in "333" for the CR_No and then each new iteration is given the next Sub_No in order. So Instead of creating "333.02" from "333" and ".02", just create the display field of "...02" and still save the other 2 fields (as they might be used elsewhere in the program". Really you are only adding a field, putting the code to create the string for the field (from your other 2 - Cr_No and Sub_No, and changing the display field on your form.

[Thompyt]

Right now I could put it in the forms VBA, but I also input it in the table directly at times. I do get you jist though.
Thanks