• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 488
  • Last Modified:

Access - Switch stmt for testing that 3 User Inputs are not null

Hello again!

I have a Form where the user inputs some info.  I am wanting to validate that all 3 of the boxes are completed before it allows them to proceed.

I know I can just do 3 If statements but the other day someone (I think it was Mbiz but could be wrong) used an example of a putting another text box that the value in that single text box gets switched from "Yes" to "No" using something like Switch(nz(xxx), "xxx", nz(yyy), "yyyy", nz(www), "www", true, "Yes", "No")

I attempted with just two variables but.... all I get is "Yes" regardless of if it should be "yes" or "no"

Actually the three fields I have to test are:
txtZipCodeNew
cboFIPSState
cboCity

This was my feeble attempt:

=Switch(Nz([txtZipCodeNew],0)<>0,"Yes",nz([cboFIPSState],0)<>0,"Yes",True,"Yes")

Open in new window

0
wlwebb
Asked:
wlwebb
  • 5
  • 4
1 Solution
 
IrogSintaCommented:
How about this way:

If Len([txtZipCodeNew] + Len([cboFIPSState]) + Len([cboCity]) = 0 Then 
    Msgbox "You have missing information",,"Incomplete"
Else
    'continue process
End If

Open in new window

0
 
wlwebbAuthor Commented:
I don't understand how that could work......  
txtZipCodeNew's length would be 5
cboFIPSState would be 2
and  
cboCity's length could be anything.... right now I have 6000 City Names.....so it's length is between 1 and 4 for now......
0
 
IrogSintaCommented:
Ah, I misunderstood.  The code I gave you checks if at least one value is entered.  Here's a modification:
If Len([txtZipCodeNew]=0 Or Len([cboFIPSState])=0 Or Len([cboCity]) = 0 Then 
    Msgbox "You have missing information",,"Incomplete"
Else
    'continue process
End If

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
wlwebbAuthor Commented:
Tried.........
    If Len(Me.txtZipCodeNew) = 0 Or Len(Me.cboFIPSState) = 0 Or Len(Me.cboCity) = 0 Then
        MsgBox "All of the information for the New Zip Code must be completed!", vbOKOnly
        Exit Sub
    
    Else
        Me.ZipCode = Me.txtZipCodeNew
        Me.CityNameID = Me.cboCity.Column(0)
        Me.FIPSStateCode = Me.cboFIPSState.Column(0)
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Close , "", acSaveNo
    
    End If

Open in new window


It goes through all the Code even though every field is blank... (I tried the Save click when all fields were blank)
0
 
IrogSintaCommented:
Can you put a breakpoint and then hover your mouse over each expression to see what the results are?  In other words, what does Len(Me.txtZipCodeNew) evaluate to?
0
 
wlwebbAuthor Commented:
Null, null, null
0
 
IrogSintaCommented:
Okay, try this.
    If Nz(Me.txtZipCodeNew,0)=0 Or Nz(Me.cboFIPSState,0)=0 Or Nz(Me.cboCity,0)=0 Then
        MsgBox "All of the information for the New Zip Code must be completed!", vbOKOnly
        Exit Sub
    
    Else
        Me.ZipCode = Me.txtZipCodeNew
        Me.CityNameID = Me.cboCity.Column(0)
        Me.FIPSStateCode = Me.cboFIPSState.Column(0)
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Close , "", acSaveNo
    
    End If

Open in new window

0
 
wlwebbAuthor Commented:
NOW that works!!!!!!!!  I didn't think you could use Nz in the vb code only in the Control Source... great!  Thank you for your persistence.!!!!!
0
 
IrogSintaCommented:
I just tried this and it works too.  Just FYI.
If IsNull(Me.Text0) + IsNull(Me.Text0) + IsNull(Me.Text0) < 0 Then
If they all have data, the result will be zero.  Otherwise, the result will be either -1, -2 or -3.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now