Docmd.OpenFrom & SQL WHERE Clause (1 Viewer)

jeran042 · Jul 11, 2018

I can use someone's keen eye to help me troubleshoot my WHERE clause.

Here is what I have for code:

Code:

Private Sub txtSubscriptions_Click()

Dim sWhere      As String
Dim sDepartment As String


sDepartment = DLookup("DEPARTMENT", "qryDepartment", "COST_CENTER= " & [txtCost_PROGRAM])

sWhere = "[COST_CENTER] = " & Me.txtCost_FRAT_MIS & " Or " _
        & "[COST_CENTER] = " & Me.txtCost_CERE & " Or " _
        & "[COST_CENTER] = " & Me.txtCost_SUPPORT & " Or " _
        & "[COST_CENTER] = " & Me.txtCost_HISPANIC & " Or " _
        & "[COST_CENTER] = " & Me.txtCost_MKTING & " Or " _
        & "[COST_CENTER] = " & Me.txtCost_PROGRAM & " Or [COST_CENTER] = " & Me.txtCost_ONLINE & " And [CATEGORY] = " & "'" & "SUBSCRIPTIONS" & "'"

Debug.Print sWhere

If txtSubscriptions.value <> 0 Then
    DoCmd.OpenForm "frm: Ledger Detail", acViewNormal, , sWhere, , acDialog
Else
    MsgBox sDepartment & " has no records for: " & vbNewLine & _
        "SUBSCRIPTIONS", vbInformation, "NO RECORDS"
End If


End Sub

The Debug.Print sWhere returns:

Code:

[COST_CENTER] = 1900 Or [COST_CENTER] = 1909 Or [COST_CENTER] = 1908 Or [COST_CENTER] = 1905 Or [COST_CENTER] = 1907 Or [COST_CENTER] = 1906 Or [COST_CENTER] = 1903 And [CATEGORY] = 'SUBSCRIPTIONS'

The form opens, and the [COST_CENTER] criteria filters correctly, but the WHERE clause is not filtering the [CATEGORY]. I'm positive it has to do with the quotation marks.

Can someone please help me figure out where I am going wrong?

Much appreciated,

lpapad · Jul 11, 2018

When you create a logic statement with OR and AND you have to use parenthesis

The following statements are different:

1-> A OR B OR C AND D
2-> (A OR B OR C) AND D

I think you should use parentheses like in the example no 2

Cronk · Jul 11, 2018

Does the form's recordsource contain the field CATEGORY? Perhaps it is a lookup ID
Is CATEGORY as string? Do any of its values contain an apostrophe in the data?

lpapad · Jul 11, 2018

From http://www.databasedev.co.uk/sql-multiple-conditions.html

If we include multiple operators in the query, SQL Server evaluates them in the following order:

Parentheses - if you group conditional SQL statements together by parentheses, SQL Server evaluates the contents of these first.
Arithmetic - multiplication (using the operators *, /, or %)
Arithmetic - addition (using the operators + or -)
Other - String Concatenator (+)
Logical - NOT
Logical - AND
Logical - OR

jeran042 · Jul 11, 2018

lpapad said:
When you create a logic statement with OR and AND you have to use parenthesis

The following statements are different:

1-> A OR B OR C AND D
2-> (A OR B OR C) AND D

I think you should use parentheses like in the example no 2

The line "2-> (A OR B OR C) AND D" was exactly it. The parentheses fixed my problem!

Final code:

Code:

Private Sub txtSubscriptions_Click()

Dim sWhere      As String
Dim sDepartment As String


sDepartment = DLookup("DEPARTMENT", "qryDepartment", "COST_CENTER= " & [txtCost_PROGRAM])

sWhere = "([COST_CENTER] = " & Me.txtCost_FRAT_MIS & " Or " _
        & "[COST_CENTER] = " & Me.txtCost_CERE & " Or " _
        & "[COST_CENTER] = " & Me.txtCost_SUPPORT & " Or " _
        & "[COST_CENTER] = " & Me.txtCost_HISPANIC & " Or " _
        & "[COST_CENTER] = " & Me.txtCost_MKTING & " Or " _
        & "[COST_CENTER] = " & Me.txtCost_PROGRAM & " Or [COST_CENTER] = " & Me.txtCost_ONLINE & ") And [CATEGORY] = " & "'" & "SUBSCRIPTIONS" & "'"

Debug.Print sWhere

If txtSubscriptions.value <> 0 Then
    DoCmd.OpenForm "frm: Ledger Detail", acViewNormal, , sWhere, , acDialog
Else
    MsgBox sDepartment & " has no records for: " & vbNewLine & _
        "SUBSCRIPTIONS", vbInformation, "NO RECORDS"
End If


End Sub

The WHERE clause now looks like this:

Code:

([COST_CENTER] = 1900 Or [COST_CENTER] = 1909 Or [COST_CENTER] = 1908 Or [COST_CENTER] = 1905 Or [COST_CENTER] = 1907 Or [COST_CENTER] = 1906 Or [COST_CENTER] = 1903) And [CATEGORY] = 'SUBSCRIPTIONS'

Thank you for your prompt answer, and for teaching me something!

Docmd.OpenFrom & SQL WHERE Clause (1 Viewer)

jeran042

Registered User.

lpapad

Registered User.

Cronk

Registered User.

lpapad

Registered User.

jeran042

Registered User.

Similar threads

Users who are viewing this thread