I have a list of registration numbers and wish to find out if any have not been used, that is are missing. Take the following example where the registration numbers are 123, 124, 125, 127 and 129. I need a formula that can identify 126 and 128 as the missing numbers.
Thanks
Jorge
Sort by the registration number, then add this formula to your detail format (basic syntax):
if {reg_num} - PreviousValue({reg_num}) > 1 then if {reg_num} - PreviousValue({reg_num}) = 2 then formula = cstr({reg_num} - 1, "0") else formula = cstr(PreviousValue({reg_num}) + 1, "0") + "-" + cstr({reg_num} - 1, "0") end if end if
HTH,
Carl
Add a comment