By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,068 Members | 899 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,068 IT Pros & Developers. It's quick & easy.

SQL Statement Access VBA If Then Else

gcoaster
100+
P: 117
How To Use A SQL SELECT Statement With Access VBA

Hi Everyone,
I was wondering if anyone had a good resource for SQL Statement examples in Access VBA using just simply select, from where and OR - example

SELECT*column1,*column2, ...
FROM*table_name
WHERE*condition1*OR*condition2*OR*condition3 ...
recordsource = MyListBox

What I am trying to do is fill an Unbound ListBox on a form (Named MAINFORM) (Data Source tbleMAIN)
where tblMAIN.RECORDID OR forms!MAINFORM.combobox1(0) OR forms!MAINFORM.combobox2(0)

Oh and one more thing, I would also like to filter out ID's that are 0 (Zero) 👍🏽
Jun 5 '20 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,761
Would Finding Jet SQL Help be the sort of thing you're looking for?
Jun 5 '20 #2

gcoaster
100+
P: 117
Hi NeoPa!
Just looking for some examples that use SQL Statements in Access VBA I can learn from

What I would love to find is a book or a website that has a good resource of examples, using the example below in access VBA.

Examples that use AND, IF Else, But I would love to find just one example that uses OR, I am finding this is really hard to find. all of the examples I find use AND. have not found one simple example that uses OR. Not sure how to format things in VBA. hope i am making sense! 😎

Expand|Select|Wrap|Line Numbers
  1. Dim SQL As String
  2. SQL  = SELECT tblRECORDS *
  3.  
  4. FROM tblRECORDS
  5. WHERE tblRECORDS.[RecordID]= [Forms]![frmPEOPLE]![txtnameD]
  6. OR
  7. tblRECORDS.RecordID2 =" [Forms]![frmPEOPLE]![Spouse1ID].(0) 
  8. OR
  9. tblRECORDS.[RecordID3]="[Forms]![frmPEOPLE]![Spouse2ID].(0)
  10.  
  11. Me.lstBox.RowSource = SQL  
  12. Me.lstBox.Requery
Jun 5 '20 #3

NeoPa
Expert Mod 15k+
P: 31,761
I don't believe I can help too much with that I'm sorry to say. Certainly as far as such a specific requirement goes for resources.

However, let me see if I can explain it in a way that makes sense :

In the WHERE clause you have what is essentially Boolean arithmetic - except it's really just ordinary arithmetic that is eventually converted to Boolean in order to be processed by the SQL engine.

In short, any numeric result of zero is treated as False whereas any other result is treated as True. So :
Expand|Select|Wrap|Line Numbers
  1. WHERE (Blah blah blah)
will fail if the Blah blah blah part is zero but succeed otherwise.
AND & OR are actually Boolean operators so they can be used in arithmetic but are specifically Boolean. AND takes all the bits (Binary DigITs) from one side, and all those form the other, and leaves a zero for any bit in the result where either side has a zero. OR takes all the bits from one side, and all those form the other, and leaves a one for any bit in the result where either side has a one. That's what is technically going on under the hood.

Just as with ordinary arithmetic, the operator works on the values on either side of it and produces a result (EG. 1 + 2 = 3) so True AND False = False & True OR False = True. All well and good if there is only the one operator but when more are involved we have to remember the order of precedence. For Boolean operators the NOT is highest, followed by AND & then OR. Parentheses come higher than anything else of course, and if all else is equal then you process the order from left to right.

How does that help us? Well, each operator has a resultant value on each side of it with the exception of NOT which only has a single operand to its right. NOT is a very simple operation where each bit is switched from its existing value to the other (of zero & one). When all the operators have been processed then the result of the last operation is the value that determines whether or not that particular record is included in the output or not.

That all may sound daunting and complicated so let's explore a couple of illustrative examples. I'm only interested in :
  1. Boy children between the ages of 5 and 15.
    Expand|Select|Wrap|Line Numbers
    1. WHERE [Gender]='M'
    2.   AND [Age] Between 5 And 15
    No need for parentheses here.
  2. Boy children between the ages of 5 and 15.
    Expand|Select|Wrap|Line Numbers
    1. WHERE NOT([Gender]='F'
    2.    OR [Age]<5
    3.    OR [Age]>15)
    We need the NOT to be applied after all the other operators so we use parentheses.
  3. Boy children between the ages of 5 and 15.
    Expand|Select|Wrap|Line Numbers
    1. WHERE [Gender]='M'
    2.   AND NOT([Age]<5
    3.    OR     [Age]>15)
    We need the NOT to be applied after the OR operator so we use parentheses.
Remember that the WHERE clause is applied to each record as it is processed so the criteria is not the same as you would express it for the group as a whole. "I want all men from India and Pakistan." doesn't mean all men who are from both India & Pakistan. It means all men who, individually, are either from India OR Pakistan. So, the criteria would use the OR operator. Not the AND operator :
Expand|Select|Wrap|Line Numbers
  1. WHERE [Country]='India'
  2.    OR [Country]='Pakistan'
Jun 5 '20 #4

gcoaster
100+
P: 117
Oh that is awesome
Thank you, I do understand this and you make it very simple to understand. the thing I dont understand is how to use this in VBA

How would you convert this to VBA when the form loads?


Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim THERECORDS As String
  3. SELECT *
  4. FROM tblMAIN 
  5.  
  6. WHERE tblMAIN.personID = Forms!mainform.cboperson.(0) OR
  7.  WHERE tblMAIN.person2ID = Forms!mainform.cboperson2.(0) OR
  8.  WHERE tblMAIN.person3ID = Forms!mainform.cboperson3.(0) OR
  9.  
  10. 'Donít include Records That Have A Zero as an ID
  11.  
  12. ORDER BY tblMAIN.FirstName
  13. Listbox source = THERECORDS
  14. End Sub
Jun 5 '20 #5

gcoaster
100+
P: 117
How would you add this to VBA?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim SQL as String
  3. SELECT *
  4. FROM tblWORLD
  5. WHERE [Country]='CBO1'
  6.    OR [Country]='TextBox1'
  7. Display these records in an unbound list box
  8. End Sub
Jun 5 '20 #6

gcoaster
100+
P: 117
Also, a resource where I can find easy examples of the following horribly written SQL converted to Access VBA would be great.

Expand|Select|Wrap|Line Numbers
  1. Private*Sub*Form_Current()
  2. Dim*SQL*as*String
  3. SQL = SELECT**
  4. FROM*tblMAIN
  5. IF
  6. [MAINID]='CBO1.(0)'
  7. ***OR*[PERSONID]='CBO2.(0)'
  8. If They Donít Match display in an unbound textbox = nothing found dude
  9. EndIf
  10. IF
  11. SQL = SELECT**
  12. FROM*tblMAIN
  13. IF
  14. [MAINID]='CBO1.(0)'
  15. ***OR*[PERSONID]='CBO2.(0)'
  16. 'If They Match Then Fill The ListBox
  17. 'Display*these*records*in*an*unbound*list*box
  18. Listbox source = SQL
  19. End*Sub
Jun 5 '20 #7

gcoaster
100+
P: 117
Or Should I Be focusing on Using =DLookup instead of SQL Statements in VBA
Jun 5 '20 #8

NeoPa
Expert Mod 15k+
P: 31,761
It looks like this one is going to be spread quite broadly.

I'm busy just now but will try to answer each of your questions in turn. I believe I now have a better idea of what you're after and I'll guide you through some of it when I can - today or tomorrow.

It's winding round to 01:45 for me here now so not sure how much I'll get done before the morrow.
Jun 6 '20 #9

NeoPa
Expert Mod 15k+
P: 31,761
GCoaster:
The thing I dont understand is how to use this in VBA.
Let's be very clear. VBA can be used to create a string with SQL code in it. VBA can't be used as the RecordSource of a Form. The only connection between SQL & VBA is that VBA can be used to manipulate text - hopefully intelligently. It's very powerful in this respect, but it certainly can't be used in place of SQL. They are not similar things.

If we want to look at how you can work with SQL in a Form's RecordSource then there are a number of options.

Let's start with the easiest. Design a saved QueryDef which includes everything and only filters out items you know you'll never want. That way you only need to apply a filter. That leaves the VBA work as just working on what would normally fit in the WHERE clause. You can then open the Form (DoCmd.OpenForm()) with a WhereCondition parameter that is applied on the fly and doesn't need to be saved in the design of the Form itself. If that's a practical option for you then it's probably your best bet.

Sometimes you want to get a little more flexible and change more than simply the filtering. You can do this in the Form's OnOpen() Event procedure either by changing the RecordSource property directly or, as changing it often leads to it not containing what you expect on future occasions, to store a template of the SQL in the Tag property and using your VBA to update this as required before setting the RecordSource to the resultant text value.

It's possible to design links to Controls on your Form within your filtering. I tend to avoid that as it can lead to dependencies that restrict how easy it is to reuse such a Form. I will typically use the Replace() function, or my own upgraded version of it MultiReplace() (Let me know if you want that code.), and include items in a template flagged by a leading percentage (%) in order to replace these with known values where necessary.

I still have a very unclear understanding of the type of filtering you're looking for but if we assume, for now, that you have a [MainID] field that always needs to be non-zero & a [PersonID] field (Numeric.) that needs to match the value from a control named [txtPerson] from some other form called [frmMenu] then, in the code to open the new form (We'll call [frmPerson] for now.), we would have something like :
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2.  
  3. strWhere = Replace("([MainID]<>0) AND ([PersonID]=%P)", "%P", Me.txtPerson)
  4. Call DoCmd.OpenForm(FormName:="frmPerson", WhereCondition:=strWhere)
Obviously, in such a case you might include the filtering for [MainID]<>0 in your QueryDef but I include it here for illustrative purposes.
GCoaster:
Or Should I Be focusing on Using =DLookup instead of SQL Statements in VBA
No. Just no.
Jun 6 '20 #10

gcoaster
100+
P: 117
"*MultiReplace()*(Let me know if you want that code.),"
YES!!

Thank you for this
4 Weeks Ago #11

NeoPa
Expert Mod 15k+
P: 31,761
Have fun :-)
Expand|Select|Wrap|Line Numbers
  1. 'MultiReplace() takes each pair of parameters from avarArgs() and replaces the
  2. '  first with the second wherever found in strMain.
  3. 'Using VbBinaryCompare means that case is recognised and not ignored.
  4. '08/05/2013 Updated to support passing of an array directly into avarArgs.
  5. Public Function MultiReplace(ByRef strMain As String _
  6.                            , ParamArray avarArgs() As Variant) As String
  7.     Dim intX As Integer
  8.     Dim avarVals() As Variant
  9.  
  10.     'Code to handle avarArgs passed as an existing array.
  11.     If (UBound(avarArgs) = LBound(avarArgs)) _
  12.     And IsArray(avarArgs(LBound(avarArgs))) Then
  13.         ReDim avarVals(LBound(avarArgs) To UBound(avarArgs(LBound(avarArgs))))
  14.         For intX = LBound(avarVals) To UBound(avarVals)
  15.             avarVals(intX) = avarArgs(LBound(avarArgs))(intX)
  16.         Next intX
  17.     Else
  18.         avarVals = avarArgs
  19.     End If
  20.     If (UBound(avarVals) - LBound(avarVals)) Mod 2 = 0 Then Stop
  21.     MultiReplace = strMain
  22.     For intX = LBound(avarVals) To UBound(avarVals) Step 2
  23.         MultiReplace = Replace(Expression:=MultiReplace, _
  24.                                Find:=Nz(avarVals(intX), ""), _
  25.                                Replace:=Nz(avarVals(intX + 1), ""), _
  26.                                Compare:=vbBinaryCompare)
  27.     Next intX
  28. End Function
NB. I use a Stop statement in line #20 if this is ever called with an even number of parameters (IE. The main string as well as multiple pairs of strings for the replacements). You could throw an error if you prefered but I wanted to catch this in development mode if ever I call it badly.
4 Weeks Ago #12

Post your reply

Sign in to post your reply or Sign up for a free account.