Substituting Data From Another Field For Blank Values

[shewolf]

I've got a database with a field PKGNum, that was typically a three digit number, that used to be the primary tracking number for each project. You could have the same package number for multiple customers. About two years ago they began to use what they now call a PMIS number which is a unique number assigned for each project. So I have a seperate field in my database to track that.

I use a query to select information from my database to generate labels for my files. What I want to be able to do is have the system understand that if the field PMIS is blank, for a given record, to give me the PKGNum instead, so that the correct color coded value will print out on the labels. What I've been doing is having folks doing the data entry put the package number into the PMIS field if it's an old file, essentially repeating the value, so that I can pull the report using just the PMIS field for the color coded.

However, I would prefer to not have them have to enter duplicate information. Is there a way to tell the system when it's running the query, if PMIS is blank temporarily fill in the field with the value from PKGNum?

Any and all assistance is greatly appreciated.

 

[neileg]

Create a query that contains a calculated field like this:

NewNum:iif(isnull(PMIS), PGKNum, PMIS)

Then use this query instead of the table.