subset of Where in SQL from code isn't working

Banaticus

Registered User.
Local time
Today, 07:30
Joined
Jan 23, 2006
Messages
153
In VB code, I build a SQL statment. I then set the RowSource of a Combo Box to that SQL statement. My code doesn't seem to be adding the final clause to the WHERE statment:
AND [App Info].[App Type] <> 'Canceled/Terminated'

You see, I thought I'd put in an unbound checkbox on the form and have the code look at it to see whether or not to filter out canceled people. Depending on whether or not the checkbox is checked, the MsgBox's that I put in are correctly telling me True or False, but the RowSource of the Combo Box (which the SQL statement resets) still shows people that have been 'Canceled/Terminated'. I've tried copy/pasting that term, just to make sure it's spelled correctly, still doesn't seem to work. The ShowCanTer checkbox seems to do nothing.
Code:
Private Sub GoGoNamesComboBox(WhichName As String) 'Nod to Inspector Gadget
'MsgBox ("GoGoNamesComboBox begins")
    'Create the string to hold the SQL statment
    Dim SqlString As String

    'Add a SELECT to the SQL statment
    SqlString = "SELECT [App Info].[Soc Sec #] as [Soc Sec], [Last Name] & ', ' & [First Name] & ' ' & [MA] AS Name FROM [App Info] "

    'Selectively add a WHERE to the statement
    Select Case WhichName
        'Case "All" We just ignore this case, as it has no WHERE statement
        Case "AB"
            SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'A%' or [App Info].[Last Name] aLike 'B%'"
        Case "CD"
            SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'C%' or [App Info].[Last Name] aLike 'D%'"
        Case "EFG"
            SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'E%' or [App Info].[Last Name] aLike 'F%' or [App Info].[Last Name] aLike 'G%'"
        Case "HIJ"
            SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'H%' or [App Info].[Last Name] aLike 'I%' or [App Info].[Last Name] aLike 'J%'"
        Case "KL"
            SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'K%' or [App Info].[Last Name] aLike 'L%'"
        Case "M"
            SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'M%'"
        Case "NOPQ"
            SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'N%' or [App Info].[Last Name] aLike 'O%' or [App Info].[Last Name] aLike 'P%' or [App Info].[Last Name] aLike 'Q%'"
        Case "RS"
            SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'R%' or [App Info].[Last Name] aLike 'S%'"
        Case "TUVWXYZ"
            SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'T%' or [App Info].[Last Name] aLike 'U%' or [App Info].[Last Name] aLike 'V%' or [App Info].[Last Name] aLike 'W%' or [App Info].[Last Name] aLike 'X%' or [App Info].[Last Name] aLike 'Y%' or [App Info].[Last Name] aLike 'Z%'"
    End Select
'MsgBox (SqlString)

    'Again, selectively add a WHERE to the statement
    'The MsgBox's are for error checking
    If Me.ShowCanTer = False Then 'ShowCanTer is a checkbox on the form
'MsgBox ("ShowCanTer is false")
        SqlString = SqlString & " AND [App Info].[App Type] <> 'Canceled/Terminated'"
'MsgBox (SqlString)
    Else
'MsgBox ("ShowCanTer is true")
    End If

    'Ordo the statement
    SqlString = SqlString & " ORDER BY REPLACE(Nz([App Info].[Last Name]),' ',''), REPLACE(Nz([App Info].[First Name]),' ',''), REPLACE(Nz([App Info].MA),' ','');"
'MsgBox (SqlString)

    'Set it all up nicely and finish it off
    Me.Names_Combo_Box.RowSource = SqlString
    Me.Names_Combo_Box.SetFocus
    Me.Names_Combo_Box.Requery
    SetSlider  'Get the vertical Slider on the side of the Combo Box right
    Me.Names_Combo_Box.Dropdown
'MsgBox ("GoGoNamesComboBox ends")
End Sub
 
Last edited:
When you use both AND and OR in a complex conditional statement, you usually need to use parentheses to ensure that the statement is evaluated as you intend. To take your last case, I'll put the parens where the order of presidence puts them and then I'll show you where they need to be to work the way you intend:

SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'T%' or [App Info].[Last Name] aLike 'U%' or [App Info].[Last Name] aLike 'V%' or [App Info].[Last Name] aLike 'W%' or [App Info].[Last Name] aLike 'X%' or [App Info].[Last Name] aLike 'Y%' or ([App Info].[Last Name] aLike 'Z%'"
SqlString = SqlString & " AND [App Info].[App Type] <> 'Canceled/Terminated')"

Should Be:
When you use both AND and OR in a complex conditional statement, you usually need to use parentheses to ensure that the statement is evaluated as you intend. To take your last case, I'll put the parens where the order of presidence puts them and then I'll show you where they need to be to work the way you intend:

SqlString = SqlString & "WHERE ([App Info].[Last Name] aLike 'T%' or [App Info].[Last Name] aLike 'U%' or [App Info].[Last Name] aLike 'V%' or [App Info].[Last Name] aLike 'W%' or [App Info].[Last Name] aLike 'X%' or [App Info].[Last Name] aLike 'Y%' or ([App Info].[Last Name] aLike 'Z%')"
SqlString = SqlString & " AND [App Info].[App Type] <> 'Canceled/Terminated')"

In shorthand what you have is:
a or b or (c AND d)
What you need is:
(a or b or c) AND d
 
I would think, Pat's correct.

small digression, You said VB, as opposed to VBA, which I'm not familiar with.
I don't know the "alike" operator, but I do know the "Like" operator.

With Like, you can shorten your code by,

WHERE [App Info].[Last Name] Like '[T-Z]*'

...maybe VB, has something similiar?.
 
Thanks. I put in aLike so that I could use % instead of putting in Like with *. That should give me forward compatibility just in case I move to a SQL server someday. I'll test out '[T-Z]%' soon as I get in tomorrow morning.

It's been my impression this whole time that I'm writing code in the Visual Basic editor that's inside Microsoft Access, so I've been calling it VB. To this date, I haven't found anything that VB 6.0 can do that I can't also do in the Microsoft Access VB editor. What's VBA?
 
Visual Basic for Applications. exclusive to MS, I believe.
I don't know VB, except for the fact, that they are very close in syntax & functionality.
But have read, that they are definately, not the same???


Now, you've aroused my curiosity.
I have to try the "ALike" statement in ACCess,
...never ever seen it, in SQL overviews???
Thx!
 
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/ima0601.asp

DAO uses * as a wildcard, ADO (and SQL server) uses % as a wildcard. I didn't want to have to go through ever darn module everywhere and change * to a % so I've used aLike with % which should (so I'm hoping) stop that problem, I think.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dninvb01/html/ivb0115.asp
It looks like [T-Z] is 'first character is T and second character is Z' It looks like I should use:
aLike '[TUVWXYZ]%'
 
Last edited:
No, using the square brackets, gives the range.
try it in the query pane, just to see the results.

Alike '[T-Z]%'
 
" ([App Info].[Last Name] aLike '[E-G]%')" <--Notice E-G
" ([App Info].[Last Name] aLike '[EFG]%')" <--Notice EFG
The list that each of those two expressions returns are the same length and a cursory examination seems to indicate that they're identical. I guess either way works. Thanks for letting me know about square brackets. It will sure simplify my queries.
 
As long as you are using Access to access SQL Server, DB2, Oracle, or whatever, you can leave your SQL as Access standard. You don't need to "think ahead". I have used standard Access syntax against many different back end databases without problems.

The difference between VB and VBA is that VBA contains support for the application in which it is embedded. So Access VBA contains support for the Access object model which includes things such as queries, tabledefs, forms, etc; Word VBA contains support for the Word object model which includes things such as paragraphs, fonts, etc; Excel VBA contains support for the Excel object model which includes such things as cells, ranges, etc.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom