I'm looking for some guidance on how to approach a problem. We are continuously pulling recurring work order tasks from one database into a new one that I've built in Access (one with more ability to track man hours and other info). Most of the fields map nicely.
The trades field in the original is rather polluted. It was called AssignedTo and instead of simply having Electrician and Mechanical as choices, multi-users over the years have added about 50 names and initials of personnel.
So I'd like to have a query that looks at that field when the record is imported and says:
Expand|Select|Wrap|Line Numbers
- IIf([AssignedTo]="Elect",1,IIf([AssignedTo]="Mech",2,Null))
Because I already have a query that looks for Work Orders with Null values in the trades field and identifies them as orphans. We have a process to bring them up and assign them so they don't fall thru the cracks.
Can anyone tell me if using Null will work or should I use Nz, which would put a zero in that field? And in which case, will my orphan query that has an IIf IsNull statement pick up the zeros and tag them as orphans?
Thanks for any education you can offer. Still learning ;)