UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Select Case With Two Variables, Access 2016    
 
   
LagoDavid
post Mar 6 2019, 01:39 PM
Post#1



Posts: 521
Joined: 12-October 03
From: Texas


Is it possible to use Select Case with two variables? I have spent a couple of hours trying to figure out why my WHERE filter is not giving me the correct data and it just occurred to me that my use of Select Case may not be appropriate. I don't get any errors, but I do not get the correct data either. I have two check boxes and I am trying to use the choices in both of them to filter my query.

My Select is:
CODE
Select Case Me.chkHideLessThan And Me.chkHideInsignificant
      Case Me.chkHideLessThan = -1 And Me.chkHideInsignificant = 0 'hide less than data but show all other
         strWhere = "WHERE tblSampleResultsLIMS.Comparator ='=' And tblSampleResultsLIMS.SampleLIMSID = " & Me.lstSamples & " "
      Case Me.chkHideLessThan = 0 And Me.chkHideInsignificant = 0 ' show all data
         strWhere = "WHERE tblSampleResultsLIMS.SampleLIMSID = " & Me.lstSamples & " "
      Case Me.chkHideLessThan = 0 And Me.chkHideInsignificant = -1 ' hide insignificant but show all others including less than
         strWhere = "WHERE tblParametersBetzmanLIMS.Insignificant = False and tblSampleResultsLIMS.SampleLIMSID = " & Me.lstSamples & " "
      Case Me.chkHideLessThan = -1 And Me.chkHideInsignificant = -1  'hide all less than and all insignificant
         strWhere = "WHERE tblParametersBetzmanLIMS.Insignificant = False and tblSampleResultsLIMS.Comparator = '=' and tblSampleResultsLIMS.SampleLIMSID = " & Me.lstSamples & " "
      Case Else
         MsgBox "Error in Select Case Statement", vbOK + vbExclamation, "Error"
   End Select
Go to the top of the page
 
theDBguy
post Mar 6 2019, 02:18 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,698
Joined: 19-June 07
From: SunnySandyEggo


Hi. Yes you can, but not that way. Instead, try it this way:
CODE
Select Case True
    Case var1=-1 And var2=0
        ...
    Case var1=0 And var2=0
        ...
...
End Select

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Mar 6 2019, 02:22 PM
Post#3


UtterAccess VIP
Posts: 9,722
Joined: 25-October 10
From: Gulf South USA


Hi: theDBguy gave you a good solution, but you could also just use an If ElseIf series to do this ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
LagoDavid
post Mar 6 2019, 02:43 PM
Post#4



Posts: 521
Joined: 12-October 03
From: Texas


YES! IT WORKS PERFECTLY NOW, thank you DBG.

RJD, For my brain the Select statement is more clear than nested IF's. I get confused at times with them.
Go to the top of the page
 
theDBguy
post Mar 6 2019, 03:12 PM
Post#5


Access Wiki and Forums Moderator
Posts: 75,698
Joined: 19-June 07
From: SunnySandyEggo


Hi David. You're welcome. Joe and I were happy to assist. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th July 2019 - 05:31 PM