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

    Fixing a code error (Access 03)

    I have the following block of code. everything works until the very last Where line. It has something to do with the very end I think. Thank you for the help. Fay

    "WHERE (((tblLearners.Inactive)=0) AND ((tblLearnerDepartments.PerDiem2Unit)= " & strDept & "))"

    Complete code

    Dim strDept As String
    Dim strStaff As String

    DoCmd.SetWarnings False

    'Selects the departments based on the the net user to restrict the records they
    'have access to just the staff members of their departments.
    strDept = " SELECT tblLearners.LastName, tblLearners.Nickname, " & _
    "tblDepartmentDirectors.Department, tblDepartmentDirectors.LearnerID, " & _
    "tblLearners.NetUser " & _
    "INTO tblDepartmentSelected FROM tblLearners INNER JOIN tblDepartmentDirectors ON " & _
    "tblLearners.LearnerID = tblDepartmentDirectors.LearnerID " & _
    "WHERE (((tblLearners.NetUser)=GetNetUser()))"

    ' Execute it
    DoCmd.RunSQL strDept

    'Select the staff members from the departments selected in the above code and creates a table.
    strStaff = " SELECT tblLearners.Classification, tblLearners.LastName, " & _
    "tblLearners.FirstName, tblLearners.MiddleName, tblLearners.Nickname, " & _
    "tblLearners.Suffix, tblLearners.Birthday, tblLearners.Salutation, " & _
    "tblLearners.Inactive, tblLearners.Credential, tblLearners.DateAdded, " & _
    "tblLearners.AllowLowCensus, tblLearners.EndContract, tblLearners.Agency, " & _
    "tblLearners.AgencyTele, tblLearners.Manager, tblLearners.ManagerAssist, " & _
    "tblLearners.NetUser, tblLearnerDepartments.Department, " & _
    "tblLearnerDepartments.Position, tblLearnerDepartments.StartDate, " & _
    "tblLearnerDepartments.EndDate, tblLearnerDepartments.[Dept#], " & _
    "tblLearnerDepartments.Status, tblLearnerDepartments.PerDiem2Unit " & _
    "INTO tblDirectorStaff FROM tblLearnerDepartments INNER JOIN tblLearners ON " & _
    "tblLearnerDepartments.LearnerID = tblLearners.LearnerID " & _
    "WHERE (((tblLearners.Inactive)=0) AND ((tblLearnerDepartments.PerDiem2Unit)= " & strDept & "))"

    ' Execute it
    DoCmd.RunSQL strStaff
    DoCmd.SetWarnings True

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

    Re: Fixing a code error (Access 03)

    Is PerDiem2Unit a text field? If so, you must place quotes around the value:

    "WHERE (((tblLearners.Inactive)=0) AND ((tblLearnerDepartments.PerDiem2Unit)= " & Chr(34) & strDept & Chr(34) & "))"

    Chr(34) is the double quote character ".

  3. #3
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fixing a code error (Access 03)

    You can also include the quotes like this:

    "WHERE (((tblLearners.Inactive)=0) AND ((tblLearnerDepartments.PerDiem2Unit)= """ & strDept & """))"

    That's two double-quotes within the quoted string to represent a double-quote so you end up with three double-quotes in a row. It's easier to see this in the IDE.

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

    Re: Fixing a code error (Access 03)

    Welcome to Woody's Lounge!

    Yes, that is another valid way to do this. See Chr() function and quotes within strings (all) for an overview.

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

    Re: Fixing a code error (Access 03)

    Thank you Hans. The code doesn't break, but it didn't populate the second table based on the first table. There are two issues, I think.

    Issue 1: In the first table the departments field is call Department and I am wanting to limit the PerDiem2Unit field, which is a text field.
    Issue 2: There will be more than one department that is selected in the first table.

    How do I combine multiple Departments into one filter to use in the PerDiem2Unit field?

    We used the For Each ... itemselected code before when I was using a listbox. But that will not work in this situation.

    Is this a place that I would use the concat function you created and mention in post 301,393? I

    Thank you. Fay

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

    Re: Fixing a code error (Access 03)

    Oh wait, I didn't look at your code closely enough. You cannot do it this way, for strDept is the SQL for an append query. You cannot use an append query as criteria in another query. Try this:

    - First, create strDept and run this SQL statement.
    - Then, redefine strDept. and continue with the rest of the code, but with a change in the WHERE line:

    strDept = "SELECT Department FROM tblDepartmentSelected"

    'Select the staff members from the departments selected in the above code and creates a table.
    strStaff = "SELECT tblLearners.Classification, tblLearners.LastName, " & _
    ...
    "WHERE tblLearners.Inactive=0 AND tblLearnerDepartments.PerDiem2Unit IN( " & strDept & ")"

    ' Execute it
    DoCmd.RunSQL strStaff
    DoCmd.SetWarnings True

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Fixing a code error (Access 03)

    Just curious, wouldn't the character ; be required at the end of the sql string as well?
    Regards,

    Gary
    (It's been a while!)

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

    Re: Fixing a code error (Access 03)

    No, that's entirely optional. Access always adds a semi-colon at the end of the SQL of a query, but the query will work equally well without it. Try the following:
    - Open a query in design view.
    - Select View | SQL.
    - Delete the semi-colon at the end. Be careful not to remove more than that.
    - Select View | Datasheet.
    - You shouldn't get an error message.

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

    Re: Fixing a code error (Access 03)

    Thanks Hans that did the trick. I appreciate the help. 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
  •