Parameter question

  • Thread starter Thread starter BJM
  • Start date Start date
B

BJM

I have a problem with setting the criteria for a control
on a form used to collect parameters from an underlying
query which then opens a report.

A combobox control asks the teachers to specify a course
code and then populates another combobox with only those
students enrolled in the chosen course. The problem is a
course might have three or more sections (it's a long,
administrative story). The point is, when the teacher
selects a course code, it includes a section number
suffix, so only those students listed in that one section
appear in the list for the second control. Teachers know
what course a student is in, but not necessary what
section the office has them registered to.

Is there some way with an SQL statement, or a trim
function or both that I could have the original control
only see the first five characters of the course code and
then populate the second control with all the students in
all sections.

For example, we have a computer course whose code is
BTT20. With the sections added the course code becomes
BTT20-01 or BTT20-02, etc.

Thank you for any insight you might have,
BJM
 
Hi,
Sure, use the left function to ge the 1st 5 characters:

Left(CourseCode,5)

you can use it a query.

That's if it's always 5 characters. If you need to take everything up to but not
including the '-' then:

Left(CourseCode,InStr(1,CourseCode,"-",vbTextCompare) -1)
 
Dan Artuso said:
Hi,
Sure, use the left function to ge the 1st 5 characters:

Left(CourseCode,5)

you can use it a query.

That's if it's always 5 characters. If you need to take everything up to but not
including the '-' then:

Left(CourseCode,InStr(1,CourseCode,"-",vbTextCompare) -1)
Click to expand...

Just a quick addition to Dan's answer. If you use the last example in a
query, replace vbTextCompare with the number 1. Jet database engine won't
know what vbTextCompare is.
 
That's wonderful. Thanks guys. But I'm not to sure exactly
where to write the Left function as I am referencing a
form control to set a query parameter.

And then how do I tell the second control to use the
trimmed value from the first control and thus return
students' names from all sections?

Again thanks,
BJM
 
The row source query for the second combo box should be something like this:

SELECT Students FROM TableName WHERE
CourseCode=Left([Forms]![FormName]![FirstComboBoxName], 5);
 
Back
Top