Solved Not IsNull Issue (1 Viewer)

[EzGoingKev]

Good afternoon.

I have this if statement in VBA -

If (Not IsNull(DLookup("[Part Manufacturer]", "[export_sheet]"))) Then
If (MsgBox("Delete PN's w/o manufacturer names?", 4) = 6) Then
DoCmd.RunSQL "DELETE FROM export_sheet WHERE [Part Manufacturer] IS NULL", -1
End If
End If


Part Manufacturer is a field in the export_sheet table. If there are null values in that field I want the message box to open and ask me if I want to delete. If there are not any null values in that field then it should stop.

It is asking me if I want to delete all the time. It does not matter if there are/are not null values in the Part Manufacturer field. I have played around with it some and cannot get it to work like I want it to. Where did I go wrong?

 

[gemma-the-husky]

If (Not IsNull(DLookup("[Part Manufacturer]", "[export_sheet]"))) Then

This just isn't correct. What are you trying to lookup?

 

[Cronk]

Your dLookup syntax will return the value for one of the records which maybe or not, be a null field
Try

If  IsNull(DLookup("[Part Manufacturer]", "[export_sheet]", len("[Part Manufacturer]"  & "") = 0) = true Then

 

[EzGoingKev]

Your dLookup syntax will return the value for one of the records which maybe or not, be a null field
Try

If  IsNull(DLookup("[Part Manufacturer]", "[export_sheet]", len("[Part Manufacturer]"  & "") = 0) = true Then

That gave me a compile error right after pasted it in.

I had also tried several iterations of -

If Len(DLookup("[Part Manufacturer]", "[accupi_export_sheet]" & "")) = 0 Then

But all those did was not open the message box. I can get it to either never open the box or open it all the time.

 

[gemma-the-husky]

I already asked this, but what are you actually trying to loookup. It makes a difference depending on the datatype of the search item, and the answers given aren't necessarily giving you the right answer.

 

[The_Doc_Man]

Among other things, you have no criteria for the lookup. You are testing a random record. You have this excerpt in the middle of the expression in your first IF statement, which I have edited for purposes of demonstration.

DLookup("[Part Manufacturer]", "[export_sheet]" , X )

The position of the red X is where you would put a criterion statement to select WHICH part to look up. But these items are non-existent in your statement, so having no criteria that means "any record will do." The way that Access works, that means the first record the DLookup examines will immediately be acceptable. Remembering that the order of records in a normal table is unpredictable, you are looking up the information on whatever record happens at the moment to be the first record of the source. If any updates occur in that table, record order can be scrambled.

But then, the way it is written, your query then would remove ALL records with nulls regardless of which one you actually looked up. I don't see the logic here in linking the first IF test vs. what you wanted to do. If you really wanted to dump all records with a null [Part Manufacturer] then just run the query, PERIOD. If you have no nulls, nothing will get deleted.

CurrentDB.Execute "DELETE FROM export_sheet WHERE [Part Manufacturer] IS NULL", acFailOnError
If CurrentDB.RecordsAffected <> 0 then 
    MsgBox CStr( CurrentDB.RecordsAffected) & " record(s) were deleted", vbOKOnly, "Some records were deleted"
End If

If you use this code, you will dump your null cases and find out how many there were. And if you see nothing, you had no nulls.

 

[EzGoingKev]

The position of the red X is where you would put a criterion statement to select WHICH part to look up. But these items are non-existent in your statement, so having no criteria that means "any record will do." The way that Access works, that means the first record the DLookup examines will immediately be acceptable. Remembering that the order of records in a normal table is unpredictable, you are looking up the information on whatever record happens at the moment to be the first record of the source. If any updates occur in that table, record order can be scrambled.

After reading this I changed the way I checked the data for nulls in the Part Manufacturer field. I have everything working the way I want it to.

I do not want it to just delete the data where there are nulls. I wanted it to ask as it would be dependent upon what I am doing. Sometimes I am doing something quick and do not care. Other times I need to stop and add that data.

ETA - I am going to close this out as solved as I no longer need assistance.