Counting Blank Fields as a Variable

[bsnalex]

I hope someone here can help--I know a very limited amount of VBA.

What I'd like to is create a function that can look at the fields on a form and determine how many of the fields are blank/null (3 fields are dropdowns, 2 are freetext, 1 is free-number). I'm sure it would be something to do with a For Each/Next command, I just don't know the appropriate syntax. Basically I want to look at Field 1 and if it's blank mark a 1, go to field 2, if it's blank mark a 1...after all the fields are determined, add up the "1s" . The end result is I want to take the result of the function and insert it as criteria into the query that runs the search function in the background (the search results finds matches in the search criteria-- I want to count the number of matches and display results n-1-- if that makes sense.

Screenshot of design mode:

The fields [SHID] and [CarryVal] wouldn't need to be counted as they are invisible in the form mode. Thanking anyone in advance.

 

[bsnalex]

Never mind-- a few minutes after i posted I came up with the following code-- it might be a bit long-winded, but it works...

note-- I added a field call "CritVal" into my source table. This code adds up the non-blank fields and inserts that value into [CritVal]. That value is then used as criteria to run against the query.

[COLOR=#000000]Private Sub Command16_Click()[/COLOR]

Dim V1 As Variant
Dim V2 As Variant
Dim V3 As Variant
Dim V4 As Variant
Dim V5 As Variant
Dim V6 As Variant

DoCmd.RefreshRecord

If Nz(Forms![frmSearchTrainees]![SHSNAME], 0) = 0 Then
    V1 = 0
    Else
    V1 = 1
End If

If Nz(Forms![frmSearchTrainees]![SHFNAME], 0) = 0 Then
    V2 = 0
    Else
    V2 = 1
End If

If Nz(Forms![frmSearchTrainees]![SHSPEC], 0) = 0 Then
    V3 = 0
    Else
    V3 = 1
End If

If Nz(Forms![frmSearchTrainees]![SHHEI], 0) = 0 Then
    V4 = 0
    Else
    V4 = 1
End If

If Nz(Forms![frmSearchTrainees]![SHPROG], 0) = 0 Then
    V5 = 0
    Else
    V5 = 1
End If

If Nz(Forms![frmSearchTrainees]![SHCHRT], 0) = 0 Then
    V6 = 0
    Else
    V6 = 1
End If

Me.CritVal = V1 + V2 + V3 + V4 + V5 + V6 
[COLOR=#000000]End Sub
[/COLOR]

I realise there's probably a shorter way of doing this, but it works

<tbody>
</tbody>

 

[xenou]

It probably what I would have done.
In the past I've implemented this approach:
Validate Text Box Entries | Database Solutions for Microsoft Access | databasedev.co.uk

see also:
Validating Data in Microsoft Access | Database Solutions for Microsoft Access | databasedev.co.uk