By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Change a field in a report.

100+
P: 443
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
May 4 '12 #1

✓ answered by 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)

Expand|Select|Wrap|Line Numbers
  1. SELECT [first field], [second field], ...,
  2.        IIF([YourTimeField]=999, "DNF", [YourTimeField] AS [MixedTime]
  3.        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

Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,357
Use the IIf() function to return DNF if they didn't finish the task.
May 4 '12 #2

100+
P: 443
Can I change the field even if it's a numeric field and I'm putting in a string?
May 5 '12 #3

100+
P: 443
Expand|Select|Wrap|Line Numbers
  1. i = vtask
  2. For i = 1 to vtask
  3. Vtime = "task" & i
  4. me(vtime).value = iif(me(vtime) = 999, "DNF", me(vtime))
  5. 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
May 5 '12 #4

Expert Mod 2.5K+
P: 2,545
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)

Expand|Select|Wrap|Line Numbers
  1. SELECT [first field], [second field], ...,
  2.        IIF([YourTimeField]=999, "DNF", [YourTimeField] AS [MixedTime]
  3.        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
May 5 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.