Change a field in a report.

[CD Tom]

I don't know if this can be done but thought I'd ask anyway, as there are lots of very smart people out there.
I have a report that shows a persons time it took to complete a task. If he doesn't complete the task he gets a time of 999 when printing the report that shows only the times I would like to change the 999 to a DNF (did not finish). I don't want to change the record only have it show up on the report. There are many different task so one person could have two or more 999 that need to be changed to the DNF.
Is this possible of should I just leave the 999.
Thanks for any advice.

Tom

[Rabbit]

Use the IIf() function to return DNF if they didn't finish the task.

[CD Tom]

Can I change the field even if it's a numeric field and I'm putting in a string?

[CD Tom]

i = vtask
For i = 1 to vtask
Vtime = "task" & i
me(vtime).value = iif(me(vtime) = 999, "DNF", me(vtime))
next i

when I use this code it gives me an error "you can't assign a value to this object" I'm sure that this is because it's trying to change the underline record. Is there any way to only change the report and leave the underline record unchanged?
I hope this makes sense.

Thanks

[Stewart Ross]

You don't need to do anything in VBA code at all. Use a query and add a computed field, something like this (with placeholders for the field names, as you haven't told us them)

SELECT [first field], [second field], ...,
       IIF([YourTimeField]=999, "DNF", [YourTimeField] AS [MixedTime]
       FROM [Your Table]... 

You'd base your report on the new query instead of the table. You then have access to the aliased field - MixedTime in the example above.

If you don't want to use a query to do so you can instead add a textbox to the report and set its control source property to

=IIF([YourTimeField]=999, "DNF", [YourTimeField])

but you'd have to have an instance of field [YourTimeField] on the report - with its visible property set to No if you don't want it to come out on the report.

No need to use VBA at all...

-Stewart