Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace question (Access03)

    Here is my code, the questions are below

    Private Sub cmdProd_Click()
    Dim strSQL As String
    Dim strWhere As String

    If Forms!Main!lstDeptP.ItemsSelected.Count = 0 Then
    MsgBox "No departments are selected!!", vbInformation
    [Forms]![Main].SetFocus
    Exit Sub
    End If

    For Each varItm In Forms!Main!lstDeptP.ItemsSelected
    strWhere = strWhere & ", " & Chr(34) & Forms!Main!lstDeptP.ItemData(varItm) & Chr(34)
    Next varItm
    strWhere = "PerDiem2Unit In (" & Mid(strWhere1, 3) & ")"

    DoCmd.SetWarnings False

    'Creates the base table that Productivity will be moved over to Excel
    strSQL = " SELECT tblLearners.LastName, tblLearners.Nickname, tblLearners.Credential, " & _
    "tblLearnerDepartments.PerDiem2Unit, tblLearners.Inactive INTO tblProductivity " & _
    "FROM qryLimitDepartments INNER JOIN (tblLearners INNER JOIN tblLearnerDepartments ON " & _
    "tblLearners.LearnerID = tblLearnerDepartments.LearnerID) ON " & _
    "qryLimitDepartments.Department = tblLearnerDepartments.PerDiem2Unit " & _
    "WHERE (((tblLearners.Inactive) = 0)) ORDER BY tblLearners.LastName, tblLearners.Nickname"

    ' Execute it
    DoCmd.RunSQL strSQL

    End Sub

    I need to limit the tblLearnerDepartments.PerDiem2Unit in the strSQL to the strWhere statement above. The above creates the tblProductiving but doesn't limit to the selected departments.

    I tried

    "WHERE (((tblLearners.Inactive) = 0)) ORDER BY tblLearners.LastName, tblLearners.Nickname AND " & strWhere

    With the above statement I get a syntax error (missing Operator tblLearners.Nickname AND PerDiem2Unit().

    I would appreciate any help. Thank you. Fay

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Replace question (Access03)

    2 things.

    First of all, you have this line:
    strWhere = "PerDiem2Unit In (" & Mid(strWhere1, 3) & ")"
    Where did strWhere1 come from?

    Secondly, you've misplaced where you put strWHERE in the SQL. In needs to be part of the WHERE statement:
    "WHERE (((tblLearners.Inactive) = 0) AND " & strWHERE & ") ORDER BY tblLearners.LastName, tblLearners.Nickname"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace question (Access03)

    Thank you Mark. I knew it was something simple but I just wasn't seeing it. This was the first time I used that strWhere with a query like this. Fay

Posting Permissions

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