SQL Statement Access VBA If Then Else

[gcoaster]

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) 👍🏽

[NeoPa]

Would Finding Jet SQL Help be the sort of thing you're looking for?

[gcoaster]

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! 😎

Dim SQL As String
SQL  = SELECT tblRECORDS *
 
FROM tblRECORDS
WHERE tblRECORDS.[RecordID]= [Forms]![frmPEOPLE]![txtnameD]
OR
tblRECORDS.RecordID2 =" [Forms]![frmPEOPLE]![Spouse1ID].(0) 
OR
tblRECORDS.[RecordID3]="[Forms]![frmPEOPLE]![Spouse2ID].(0)
 
Me.lstBox.RowSource = SQL  
Me.lstBox.Requery

[NeoPa]

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 :

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.
   

   WHERE [Gender]='M'
     AND [Age] Between 5 And 15

   No need for parentheses here.
2. Boy children between the ages of 5 and 15.
   

   WHERE NOT([Gender]='F'
      OR [Age]<5
      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.
   

   WHERE [Gender]='M'
     AND NOT([Age]<5
      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 :

WHERE [Country]='India'
   OR [Country]='Pakistan'

[gcoaster]

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?

Private Sub Form_Current()
Dim THERECORDS As String
SELECT *
FROM tblMAIN 
 
WHERE tblMAIN.personID = Forms!mainform.cboperson.(0) OR
 WHERE tblMAIN.person2ID = Forms!mainform.cboperson2.(0) OR
 WHERE tblMAIN.person3ID = Forms!mainform.cboperson3.(0) OR
 
'Don’t include Records That Have A Zero as an ID
 
ORDER BY tblMAIN.FirstName
Listbox source = THERECORDS
End Sub

[gcoaster]

How would you add this to VBA?

Private Sub Form_Current()
Dim SQL as String
SELECT *
FROM tblWORLD
WHERE [Country]='CBO1'
   OR [Country]='TextBox1'
Display these records in an unbound list box
End Sub

[gcoaster]

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

Private*Sub*Form_Current()
Dim*SQL*as*String
SQL = SELECT**
FROM*tblMAIN
IF
[MAINID]='CBO1.(0)'
***OR*[PERSONID]='CBO2.(0)'
If They Don’t Match display in an unbound textbox = nothing found dude
EndIf
IF
SQL = SELECT**
FROM*tblMAIN
IF
[MAINID]='CBO1.(0)'
***OR*[PERSONID]='CBO2.(0)'
'If They Match Then Fill The ListBox
'Display*these*records*in*an*unbound*list*box
Listbox source = SQL
End*Sub

[gcoaster]

Or Should I Be focusing on Using =DLookup instead of SQL Statements in VBA

[NeoPa]

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.

[NeoPa]

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 :

Dim strWhere As String
 
strWhere = Replace("([MainID]<>0) AND ([PersonID]=%P)", "%P", Me.txtPerson)
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.

[gcoaster]

"*MultiReplace()*(Let me know if you want that code.),"
YES!!

Thank you for this

[NeoPa]

Have fun :-)

'MultiReplace() takes each pair of parameters from avarArgs() and replaces the
'  first with the second wherever found in strMain.
'Using VbBinaryCompare means that case is recognised and not ignored.
'08/05/2013 Updated to support passing of an array directly into avarArgs.
Public Function MultiReplace(ByRef strMain As String _
                           , ParamArray avarArgs() As Variant) As String
    Dim intX As Integer
    Dim avarVals() As Variant
 
    'Code to handle avarArgs passed as an existing array.
    If (UBound(avarArgs) = LBound(avarArgs)) _
    And IsArray(avarArgs(LBound(avarArgs))) Then
        ReDim avarVals(LBound(avarArgs) To UBound(avarArgs(LBound(avarArgs))))
        For intX = LBound(avarVals) To UBound(avarVals)
            avarVals(intX) = avarArgs(LBound(avarArgs))(intX)
        Next intX
    Else
        avarVals = avarArgs
    End If
    If (UBound(avarVals) - LBound(avarVals)) Mod 2 = 0 Then Stop
    MultiReplace = strMain
    For intX = LBound(avarVals) To UBound(avarVals) Step 2
        MultiReplace = Replace(Expression:=MultiReplace, _
                               Find:=Nz(avarVals(intX), ""), _
                               Replace:=Nz(avarVals(intX + 1), ""), _
                               Compare:=vbBinaryCompare)
    Next intX
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.