Validate Null Values

are you doing this from a form event ?

if not, you should use the form's beforeupdate event..

post more info..


in the design view of the form, select all the controls that you want to validate, then hit F4
select the "Other" tab and place "REQ" in the Tag property

you can then use this SAMPLE code to validate the fields/controls

private sub form_beforeupdate(cancel as integer)
dim ctl as control
for each ctl in me.controls
        if ctl.tag="Req" then
             if ctl & ""="" then
                       msgbox "message here"
                   exit sub
             end if
        end if
next

end sub

No, I am doing this within a query.  Thanks

better use a UDF function

place this codes in a regular module

Function CheckFields(ParamArray sArr()) As string
dim j, fCnt as integer

for j=0 to ubound(sarr)
    if sarr(j) & "" <> "" then
       fCnt=fCnt+1
    end if

next

select case fCnt
      case 0
      CheckFields= "Not Upated"
     case = ubound(sArr) +1
      CheckFields= "Update Completed"
     case else
      CheckFields= "Not Upated"

end select

exit function



now in your query, add this column


select *, checkfields([f1],[f2],[f3]......... to [fn])
from tableX

I would use Capricorn1's solution.

Getting an "Undefined function 'CheckFields' in expression." error.

No points please.

Where did you put the function? It should be in a public code module, not in a forms code module.

You may also have to preface the declaration with PUBLIC, as in:

PUBLIC Function CheckFields(ParamArray sArr()) As string

This is what I have and I am getting the same error.

Public Function CheckFields(ParamArray sArr()) As String
Dim j, fCnt As Integer

For j = 0 To UBound(sArr)
    If sArr(j) & "" <> "" Then
       fCnt = fCnt + 1
    End If

Next

Select Case fCnt
      Case 0
      CheckFields = "Not Upated"
     Case Is = UBound(sArr) + 1
      CheckFields = "Update Completed"
     Case Else
      CheckFields = "Not Upated"

End Select

Exit Function

Select allOpen in new window

did you place the function in a REGULAR module?
what did you name the  Module? hope you did not name it CheckFields !!!

Yes, regular module and named it "test"...I recopied it and now am getting compile error stating "Expected End Function".  Maybe the first time I forgot to copy something and this is something else?

Try this.

I added the type declaration for the variable j.  You cannot list variables and then give them all the same type by putting the Integer at the end of the line.  Each variable requires its own type, otherwise it becomes a variant.

Otherwise, I just changed "upated" to "updated"

Can you post the SQL string you are using to call this function?

Public Function CheckFields(ParamArray sArr()) As String
Dim j as Integer, fCnt As Integer

For j = lBound(sArr) To UBound(sArr)
    If sArr(j) & "" <> "" Then fCnt = fCnt + 1
Next

Select Case fCnt
    Case 0
        CheckFields = "Not Updated"
    Case UBound(sArr) + 1
        CheckFields = "Update Completed"
    Case Else
        CheckFields = "Not Updated"
End Select

Exit Function

Select allOpen in new window

Works perfectly!  :)