access to the field in ms access using vba

Question

ihave table "customer" the table contain many field ineed to check just one of them by example field "phone no" , if contain null value show msgbox "there are null value" and if not contain null value show msgbox" ok " check field "phone no " from table " customer" if any row of field "phone no" contain a null value or not, The data is in the table ,

Set db = CurrentDb
Dim x As String
Dim rs As dao.Recordset
Set rs = db.OpenRecordset("SELECT [phone no] FROM customer;")
Set x = rs
If x = Null Then
MsgBox "There is space"
Else
MsgBox "There is no space"
End If
end sub```

SELECT count(*) from tablename where [filedname] is null; then check rs.Fields(0).Value — Tim Williams
thank you but this code to count field ?! ineed to show msgbox if field contain null or not ,,,, Can you explain more please — Mohammad M.sharhan
It's not clear from your question what you want to do: in your code you select the field values for all of the records in the table. Which of those records do you want to look at? One of them? All of them? My suggestion gives you a count of how many records in the table have a null value for that field. If you want something else then please explain what you're looking for. — Tim Williams
ok , ihave table "customer" the table contain many field ineed to check just one of them by example field "phone no" , if contain null value show msgbox "there are null value" and if not contain null value show msgbox" ok " — Mohammad M.sharhan
MsgBox "Found " & rs.Fields(0).Value & " null values in field '" & filedname & "'" — Tim Williams

June7 June7 · Accepted Answer · 2020-03-19T01:24:13

Can't directly use Null in an equality expression. There is nothing to compare to, not even Null=Null. Use Nz(), IsNull(), or for use only in queries Is Null.

Actually, since x variable is declared as String and String cannot hold Null, any test for Null will always fail. Don't need x variable nor recordset. A domain aggregate function can return a count of records where data is null. If there is no match then 0 will return.

MsgBox "There are " & DCount("*", "Customers", "IsNull([phone no])") & " records without phone number."

Keep in mind that empty string is not same as Null. If field has empty string, it will not count as Null. Advise not to allow empty string in fields. The following will handle both Null or empty string:

MsgBox "There are " & DCount("*", "Customers", "Nz([phone no],'')=''") & " records without phone number."

access to the field in ms access using vba

1 Answers