Assistance with Concatenate function

[Justin Busch]

I am looking for assistance using the Concatenate function by Duane Hookom
to display data in the details area of my report horizontally.

I am using Access 2003
My Tables:

courseInformation:
-courseID (primary key)
-courseName

instructorInformation
-instructorID (primary key)
-instructorLastName
-instructorFirstName
-instructorCampus
-instructorDepartment
-instructorSeniority

DLFSeniorityList
-instructorID
-courseID
-courseModality
-semesterYearTaught
-numberSections

instructorInformation.instructorID has a one-to-many relationship with
DLFSeniorityList.instructorID
courseInformation.courseID has a one-to-many relationship with
DLFSeniorityList.courseID

I am trying to replace an old Excel version of this report in Access.

The report is formatted to display:
courseID, instructorName, Campus, department, courseName, modality,
Semseter/Year, sneiority

I have set up a query to be used for the report that includes:
courseID, instructorLastName, instructorFirstName, instructorCampus,
instructorDepartment, courseName, courseModality, semesterYearTaught,
instructorSeniority, instructorID

The report is first ordered by the courseID and then ordered by
instructorLastName and instructorFirstName. (More than one instructor can
have taught the course and each instructor can have taught the course more
than once.)

detail:
=Concatenate("select semesterYearTaught from DLFSeniorityQuery where
courseID =" & [courseID] & " and instructorID =" & [instructorID])

When trying to run the report I get the following MS VB error:
Run-time error '3061':
Too few parameters. Expected 2.

Where am I going wrong?

Justin Busch
(e-mail address removed)

 

[Duane Hookom]

I think you missed one critical attribute of your data, field data types.
Are CourseID and InstructorID both text fields? If so, try

=Concatenate("SELECT semesterYearTaught FROM DLFSeniorityQuery WHERE
courseID =""" & [courseID] & """ AND instructorID =""" & [instructorID] &
"""")

If that doesn't work, let us know.

 

[Justin Busch]

Duane,

That worked perfect! I just needed to add the closing double quote for the
double quote before the SELECT statement.

Thank you!

I think you missed one critical attribute of your data, field data types.
Are CourseID and InstructorID both text fields? If so, try

=Concatenate("SELECT semesterYearTaught FROM DLFSeniorityQuery WHERE
courseID =""" & [courseID] & """ AND instructorID =""" & [instructorID] &
"""")

If that doesn't work, let us know.

--
Duane Hookom
MS Access MVP
--

I am looking for assistance using the Concatenate function by Duane Hookom
to display data in the details area of my report horizontally.

I am using Access 2003
My Tables:

courseInformation:
-courseID (primary key)
-courseName

instructorInformation
-instructorID (primary key)
-instructorLastName
-instructorFirstName
-instructorCampus
-instructorDepartment
-instructorSeniority

DLFSeniorityList
-instructorID
-courseID
-courseModality
-semesterYearTaught
-numberSections

instructorInformation.instructorID has a one-to-many relationship with
DLFSeniorityList.instructorID
courseInformation.courseID has a one-to-many relationship with
DLFSeniorityList.courseID

I am trying to replace an old Excel version of this report in Access.

The report is formatted to display:
courseID, instructorName, Campus, department, courseName, modality,
Semseter/Year, sneiority

I have set up a query to be used for the report that includes:
courseID, instructorLastName, instructorFirstName, instructorCampus,
instructorDepartment, courseName, courseModality, semesterYearTaught,
instructorSeniority, instructorID

The report is first ordered by the courseID and then ordered by
instructorLastName and instructorFirstName. (More than one instructor
can have taught the course and each instructor can have taught the course
more than once.)

detail:
=Concatenate("select semesterYearTaught from DLFSeniorityQuery where
courseID =" & [courseID] & " and instructorID =" & [instructorID])

When trying to run the report I get the following MS VB error:
Run-time error '3061':
Too few parameters. Expected 2.

Where am I going wrong?

Justin Busch
(e-mail address removed)