Fixing a code error (Access 03)

Topic

New Reply

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

Reply | Quote

Replies

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 “.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks Hans that did the trick. I appreciate the help. Fay

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote