How to code an ALL option into a Combo Box

Question

I have a combo box on my form with the choice of choosing organization 10, 20, 30....

I have added ALL to the combo list box, but am having trouble implementing an all statement in VBA. Below is the case statement I have to get info from organizations 10, 20, 30. How do I get ALL to generate??

Case Is = 1
    If cboOrg.ListIndex < 0 Then
        Call msg("Please select your organization!")
        Exit Sub
    End If
    sQ = sQ & " CC LIKE '" & cboOrg.Value & "*'"
    ORGCC = Trim(cboOrg.Value)

Answer 1

I think you should only generate the WHERE/AND-clause, when value is not "ALL" (instead of your current assignment):

If (cboOrg.Value <> "ALL") Then
    sQ = sQ & " AND CC LIKE '" & cboOrg.Value & "*'"
End If

To make it work without changing code before (generating AND or WHERE), you could try:

If (cboOrg.Value <> "ALL") Then
    sQ = sQ & " CC LIKE '" & cboOrg.Value & "*'"
Else
    sQ = sQ & " 1=1"
End If

---

Do you really need the LIKE (does CC only start with the value selected) or would
WHERE CC = '" & cboOrg.Value & "'" be sufficient?

Answer 2

Private Sub cmdGo_Click()

Dim db As Database, rs As Recordset, sQ As String

Dim oXL, oExcel As Object

Set oXL = CreateObject("Excel.Application")

fPath = "\\firework\mmcfin\123files\Edmond\Lawson Query\Log\"

myTime = Now()

myFile = Environ("UserName") & "-" & Environ("ComputerName") & "-" & Replace(Replace(Replace(Trim(myTime), "/", "-"), " ", "-"), ":", "-")

pTitle = "Lawson Queries"

Set db = CurrentDb

Select Case cboActBud.ListIndex

    Case Is < 0
        Call msg("Please select your query type first: Actual or Budget!")
        Exit Sub

    Case Is = 0
        sQ = "SELECT * INTO [" & myFile & "] FROM ACT"
        toAdd = "WHERE"

    Case Is = 1
        sQ = "SELECT * INTO [" & myFile & "] FROM BUD"

        If cboBucket.ListIndex < 0 Then
            Call msg("Please select your budget bucket!")
            Exit Sub

        Else

            toAdd = "WHERE BUDGET_NBR = " & cboBucket.Value & " AND"

        End If

End Select

myAcctLo = txtACCT1.Value
myAcctHi = txtACCT2.Value

If IsNull(myAcctLo) Or myAcctLo < 1000 Or myAcctLo > 99999 Then

    Call msg("Account number is missing or invalid!")
    Exit Sub

End If

If IsNull(myAcctHi) Then

    myAcctHi = myAcctLo

End If

If myAcctLo > myAcctHi Then

    Call msg("Account range is invalid!")
    Exit Sub

End If

If myAcctLo < 90000 And myAcctHi >= 90000 Then

    Call msg("You can query amounts or units; but, not both at the same time!")
    Exit Sub

End If

Select Case myAcctLo

    Case Is < 90000: sQ = sQ & "AMT " & toAdd
    Case Is >= 90000: sQ = sQ & "UNT " & toAdd

End Select

Select Case cboLevel.ListIndex

    Case Is < 0
        Call msg("Please select your reporting level: Cost Center or Organization!")
        Exit Sub

    Case Is = 0
        If IsNull(txtCC) Then
            Call msg("Please enter your cost center!")
            Exit Sub
        End If
        sQ = sQ & " CC = " & txtCC.Value
        ORGCC = Trim(txtCC.Value)

    Case Is = 1
        If cboOrg.ListIndex < 0 Then
            Call msg("Please select your organization!")
            Exit Sub
        End If
        sQ = sQ & " CC LIKE '" & cboOrg.Value & "*'"
        ORGCC = Trim(cboOrg.Value)

       If (cboOrg.Value <> "All") Then
        sQ = sQ & " CC LIKE '" & cboOrg.Value & "*'"
        Else
        sQ = sQ & " 1=1"
        End If

End Select

If cboYear.ListIndex < 0 Then

    Call msg("Please select an year!")
    Exit Sub

End If

sQ = sQ & " AND FY = " & cboYear.Value & " AND (ACCT >= " & myAcctLo & " AND ACCT <= " & myAcctHi & ")"

DoCmd.Hourglass True

db.Execute sQ

sQ = "INSERT INTO tblLog (UserName, ComputerName, DateAndTime, ORGORCC, ACCT1, ACCT2, BUDGET, FY) VALUES ('" & _
     Environ("UserName") & "','" & Environ("ComputerName") & "',#" & myTime & "#," & ORGCC & "," & myAcctLo & _
     "," & myAcctHi & "," & IIf(cboBucket.ListIndex < 0, 0, Trim(cboBucket.Value)) & "," & Trim(cboYear.Value) & ")"

db.Execute sQ


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myFile, fPath & myFile, True

With oXL

    .Visible = True
    .Workbooks.Open (fPath & myFile)

End With

Set oXL = Nothing

DoCmd.Hourglass False

db.Close