Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding a WHERE clause to query SQL in VBA (2002)

    Ok..I have an existing crosstab query, from which I would like to be able to filter for one or more of the records that it is grouped by. I would like to apply this filter from selections in a listbox. Below is the SQL for my query, and below that is the code I've got started but dont know where to go next from my listbox selections. I want to filter my crosstab for the location(s) selected in the list box. If I can do this through the query's design view, that would probably be much easier.

    The query's SQL:
    SELECT qryLocationCompany.LOCATION, qryLocationCompany.COMPANY, Val(NZ([QRYLOCBYCO].[MO],0)) AS MO, Val(NZ([QRYLOCBYCO].[ME],0)) AS ME, Val(NZ([QRYLOCBYCO].[NO],0)) AS [NO], Val(NZ([QRYLOCBYCO].[NE],0)) AS NE, Val(NZ([QRYLOCBYCO].[AO],0)) AS AO, Val(NZ([QRYLOCBYCO].[AE],0)) AS AE, Val(NZ([QRYLOCBYCO].[Total Of SSN],0)) AS [Total Of SSN]
    FROM qryLocationCompany LEFT JOIN qryLocByCo ON (qryLocationCompany.LOCATION = qryLocByCo.Location) AND (qryLocationCompany.COMPANY = qryLocByCo.Company)
    GROUP BY qryLocationCompany.LOCATION, qryLocationCompany.COMPANY, Val(NZ([QRYLOCBYCO].[MO],0)), Val(NZ([QRYLOCBYCO].[ME],0)), Val(NZ([QRYLOCBYCO].[NO],0)), Val(NZ([QRYLOCBYCO].[NE],0)), Val(NZ([QRYLOCBYCO].[AO],0)), Val(NZ([QRYLOCBYCO].[AE],0)), Val(NZ([QRYLOCBYCO].[Total Of SSN],0));


    The code I have started:
    Private Sub cmdViewReport_Click()
    Dim varItem As Variant
    Dim strSQL As String

    If Me.lstLocations.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more fields.", vbExclamation
    Me.lstLocations.SetFocus
    Exit Sub
    End If
    For Each varItem In Me.lstLocations.ItemsSelected
    'edit SQL to apply Location filter here
    'create SQL of the query

    CurrentDb.QueryDefs("qryReportAll").SQL = strSQL
    DoCmd.OpenQuery "qryReportAll", acViewNormal, acReadOnly

    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding a WHERE clause to query SQL in VBA (2002)

    See for example <post#=465456>post 465456</post#> for a method to construct a WHERE part from a multi-select list box in code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •