Access Tip

[rbowman]

I need a nudge in the right direction. I have a Table that has a list of "items" with a column for registration number. On occasion a cell in that column will be empty (with no registration#) this can happen for one of two reasons. one is because of an error and the other reason would be that I have one item that does not require a registration. What I would like to do is create a "NoReg" table with a list of the items not needing a registration number. Then create a join query against the 2 tables. I have a common column of data in both Tables for the join. Idealy what I would like it to do is when it finds a blank value in the registration column then check the "NoReg" table to see if there is a match and put "No Reg Needed" in that blank cell in the column. Otherwise it will leave the registration number listed in the column. This would make finding errors much easier as blanks would be true errors. The only thing I don't know is what value to build the criteria from for that column. Would it be a Iif statement? Or is it even doable?

Thanks



Rick

[June7]

Yes, an IIf in the query should produce what you want.

The query jointype should probably be 'show all records from Table and only those from NoReg that are equal'.

What are the common linking fields?

IIf(IsNull(NoReg.linkfield), "No Reg Needed", RegNum)

[rbowman]

There is a column with the item description in each table. So I will be linking the descriptions and when it finds the item not needing a "regnum" then I want to put "No Registration Needed" in the "Reg Number" column associated with that item. IF the cell is not blank then I want the actual registration number that is in that column. I will plug my values into your sample Iif statement and see if I can get it to work. I just wasn't quite sure what to start with.

Thanks

Rick