Searching and replacing "null" values

[biederboat]

Caution: my ability to jump in ad "do" things on Access is ahead of my terminology and any formal (but limited) actual training. We have a database with a table that has records that have an "orphaned" field (i.e. the record in the linked table was somehow deleted). I believe this is called a "broken object reference". We are also using this table with a front & back end. When I look at the table through the front end, I clearly see the orphaned fields, they contain the "old" (and now missing) link of 8822 (the idfield of the deleted record in the other table). There are many records that are validly "null" in this field and others that have a valid value. I am trying to replace the "8822" value with the new correct value. I can filter that table to see just "nulls" in this table but I still get all the records that are either null or have the broken field value of 8822 (i.e. they seem to be treated the same as far as any data references). Is there any way I can do a "search and replace" to put a valid value in place of 8822? If I go through the back end then all the records that had "8822" also look to be "null". Unfortunately, there is not other criteria I can think of to limit it to just the records with the broken references (unless there's a function just for that).



Hopefully I explained that clearly enough (if not hopefully the screen shot below will help). Searching did not yield any information that I could find. Fortunately, this is almost a temporary field but it could cause problems for future maintenance so I'ld like to fix it. If nothing else, going through and doing it manually won't be a huge undertaking but enough of one that spending time here trying to find a solution is worthwhile.

Thanks for any assistance,
BB

[ranman256]

you dont do 'search & replace' , you run an update query. This replaces all of them at once.

you can query on nulls , in the query, enter the field criteria: IS NULL.
then you can update those too.

[biederboat]

you dont do 'search & replace' , you run an update query. This replaces all of them at once.

you can query on nulls , in the query, enter the field criteria: IS NULL.
then you can update those too.

Thanks, I do know about the update query function and meant to mention/ask about that but forgot. The problem I see is that it will replace any field that either really is null or contains the "8822" (which is seems to treat as null). Unfortunately, there's no other distinguishing criteria that I can think of to otherwise limit the records to just those with the broken links (unless there's a special function I don't know about). Note: I edited the original post to clarify some of this.

Thanks again,
BB

[Bulzie]

Are you using Relationships on those tables with Referential Integrity set to Update and Delete child record if main record is updated? Also not sure I understand, so when you open the actual database that has the table that contains that 8822 MaterialsID value, it does not show as 8822 on those records but is blank? That appears to be a text field so if you not using Ref Integrity, not sure why you can't see the 8822.

Is the MaterialsID in the main table an autonumber or just number? If just a number can you add a temp record with 8822 back in the master table to get the link back?

Also maybe add Val(MaterialsID) to the update query and look for 8822 in criteria to try to get just those records.

[biederboat]

Are you using Relationships on those tables with Referential Integrity set to Update and Delete child record if main record is updated? Also not sure I understand, so when you open the actual database that has the table that contains that 8822 MaterialsID value, it does not show as 8822 on those records but is blank? That appears to be a text field so if you not using Ref Integrity, not sure why you can't see the 8822.

Is the MaterialsID in the main table an autonumber or just number? If just a number can you add a temp record with 8822 back in the master table to get the link back?

Also maybe add Val(MaterialsID) to the update query and look for 8822 in criteria to try to get just those records.

I did try to re-enter the 8822 but since it is an autonumber it apparently won't let me do that (at least "manually" it wouldn't). We do have a DB programmer (outside contractor) who is great and I'm sure he can "fix" this. I'm just trying to learn more on my own so we can be a little more self-sufficient. We did have a conversation on Referential Integrity and, even if he had to fess up that it should have been there (my understanding is that it isn't); I wouldn't by any means crucify him over it. Overall he's created an outstanding program for us and has been extremely cost-effective.

I think the only thing I didn't answer is that when I open the table that had the original 8822 (that I can't manually re-enter), it doesn't have anything at all there (somehow got accidentally deleted despite an ref. integ.; if it was there). And I can't really "see" (i.e. set criteria) for the 8822 records (those in the screen shot) as they apparently get treated as null values. To close that, I can't really do an update query on changing "null" to the correct value as there are other truly valid records that have that field as null.

Hope I explained that all correctly; again my db-lingo is rather "raw" is the best way I can explain it.



Thanks,
BB

[CJ_London]

I suspect that given your column is called MaterialsID and you have things like ABG& 35mm populating and you say ' 8822 records (those in the screen shot) as they apparently get treated as null values' that this field has been set up as a lookup field and whatever the ID is for 8822, does not exist in the materials table.

If this is the case, I recommend you change the field back to a number field and remove the lookup. You will then see more clearly what data you actually have.

[Bulzie]

So the main table that had the record with a MaterialsID of 8822 is no longer there. But you are saying when you open the table that had the linked data records(the table you are showing in your original post) you can see 8822 in that MaterialsID field but if you put it in a query it treats it as nulls? Seems it should just show 8822 in that text field. If you create a query with that table above and added a column with Len(MaterialsID) and run it, does it return 4 for those with "8822"? Sorry I may be way off on this, seems like you are saying you can see 8822 but if you use a query it treats it as nulls?

[biederboat]

So the main table that had the record with a MaterialsID of 8822 is no longer there. But you are saying when you open the table that had the linked data records(the table you are showing in your original post) you can see 8822 in that MaterialsID field but if you put it in a query it treats it as nulls? Seems it should just show 8822 in that text field. If you create a query with that table above and added a column with Len(MaterialsID) and run it, does it return 4 for those with "8822"? Sorry I may be way off on this, seems like you are saying you can see 8822 but if you use a query it treats it as nulls?

That is 100% the case, treats it as a "null". However, I can click on the end of the value, start backspacing, and then finishing typing a valid value of "83352" which does happen to be a valid value. Doesn't seem like it should but for some reason it does allow that.

BB

[Bulzie]

So again, if you put table tblBOMMaterial in a query, select MaterialsID field and in criteria put "8822", it returns nothing? Not sure how you can see it in the table but using it in query it says it is null.

[CJ_London]

@bieder - have you read post#6?

[biederboat]

So again, if you put table tblBOMMaterial in a query, select MaterialsID field and in criteria put "8822", it returns nothing? Not sure how you can see it in the table but using it in query it says it is null.

Correct. Stating it another way, if I select "blanks" on the filter, I get true blanks (nulls) and fields that have the "8822".

@bieder - have you read post#6?

Sorry, I did read it but got side tracked. I'm a bit concerned (not being fully trained) as this really is a text column. What will happen to the text values if I change it as suggested? I might try this on an old back-up copy but I'm not fully understanding how this might help.

Thanks all,
BB

Here's what the table looks like after filtering for "blanks":

[Bulzie]

It appears to be text, not numeric field. Always make a copy to test with, don't use live. Maybe add another column to the table as a Text field and use Update query to copy the values from MaterialsID to this new field, see what you get. Seems like you need it to recognize the 8822 as either a text or numberic value so you can isolate just those records.

[CJ_London]

Sorry, I did read it but got side tracked. I'm a bit concerned (not being fully trained) as this really is a text column. What will happen to the text values if I change it as suggested?

if it is a text column and not a lookup field then not a problem, nothing to change. I can see that MachineID is a dropdown so was questioning whether MaterialsID is also a dropdown - plus the name implies an ID, not a description. Just to satisfy me, go into table design, select the machineID field and click on the lookup tab - what does it say for Display Control? If it doesn't say text box, provide a screenshot of the lookup properties because that is where your problem will lie.

[biederboat]

Just to satisfy me, go into table design, select the machineID field and click on the lookup tab - what does it say for Display Control?

Hi, did you mean materialsID? I assumed so and here's what it is (in the table design where this field is selected from a drop-down). Just for reference, the lookup propertied for machineID appear to be the same except for the source.

Thanks,
BB

[CJ_London]

Sorry, yes I meant materialsID. This is as I thought, a lookup field. It is not a text field but a numeric field of two columns, the first is the ID - and the second is text for the part number and the first column is hidden - see column widths.

Lookup fields are not good for anything except the simplest tasks and waste a lot of time because you forget - how much time have you spend on this so far?

See this link about why they are bad

http://access.mvps.org/access/lookupfields.htm

So, recommend you remove the lookup (just change the display control to text box) and you can see what you really have. Then ask your original question again from a more enlightened perspective. I would also do the same for your other 'ID' fields.

Remember users should not have access to queries and tables, only forms and reports, so how data is displaying in a table or query is irrelevant