• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • 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
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.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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