The quickest way of determining if a field is null

[chrisbrookeanna]

Hi,

Im using DLOOKUP to determine whether a field is null. Is there a quicker
way in VB? I cant just use SQL because I need to identify the name of the null field to show to the user.

Thanks.

 

[ghudson]

Are you asking how to test if a text box within a form is null or a field within the record of a table is null?

I am guessing the latter and you will need to throw an IF into your dlookup to account for the error if it is null.

 

[jvangils]

In VBA it would look something like

If IsNull(rst!Field)=true then....

 

[dcx693]

Im using DLOOKUP to determine whether a field is null. Is there a quicker way in VB?

To test one field? You can try querying the table using DAO/ADO and testing the recordcount, but it might not be any faster.

 

[Jon K]

Im using DLOOKUP to determine whether a field is null. Is there a quicker way in VB?

I tried the following code on a table containing 333,841 records and found that DLookup() was fast if the fields used in the criteria were indexed.

Private Sub Command0_Click()
   
  Me.txtFound = DLookup("IncreasingNumber_Field", "TableName", _
               "Fld3='" & Me.txtFld3 & "' and Fld4='" & Me.txtFld4 & "'")
   
End Sub

Without the indexes, it took roughly 2.8 seconds to run. When [Fld3] and [Fld4] were indexed, it took only 0.006 second.

Indexing can often optimise simple criteria like FieldA=Something.