?
Solved

Validate Null Values

Posted on 2011-10-13
12
Medium Priority
?
259 Views
Last Modified: 2012-05-12
Hello Experts,

I need to validate multiple fields (~15) for null values.  If all fields are null I will display "Not Upated"; if one or more fields are completed but not all I will display "Partially Updated"; if all fields are completed I would display "Update Completed".

I have tried using a nested IIF statements but I get a "too complex" error from the query.  Any insights would be appreciated.

Thanks
0
Comment
Question by:ShadowIT
  • 5
  • 4
  • 3
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36963018
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
0
 

Author Comment

by:ShadowIT
ID: 36963152
No, I am doing this within a query.  Thanks
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36963268
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



0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

Expert Comment

by:Dale Fye
ID: 36963398
I would use Capricorn1's solution.
0
 

Author Comment

by:ShadowIT
ID: 36963424
Getting an "Undefined function 'CheckFields' in expression." error.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36963455
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
0
 

Author Comment

by:ShadowIT
ID: 36963486
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

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36963508
did you place the function in a REGULAR module?
what did you name the  Module? hope you did not name it CheckFields !!!
0
 

Author Comment

by:ShadowIT
ID: 36964268
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?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36964345
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

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36964390
it is my mistake,

just change this part

   exit function

with

   end function

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 Updated"

End Select

End Function

Open in new window





0
 

Author Closing Comment

by:ShadowIT
ID: 36964780
Works perfectly!  :)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question