Multiple Criteria in Code

A

Ann

I am using Access 2002. I have the following code on a button. This code
allows me to pick multiple Course Titles from a list box and generate reports
for only those I've chosen. Now I need to add a second piece of criteria to
the list. I now have txtCourseTitle and dtmStartDate. I need the date too
because the same course can be listed more than once. I didn't write the
code below so I'm not sure how to change it. I tried adding it to temp = in
the Else statement but it didn't work. Can anyone help me out? Thanks in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub
 
D

Douglas J. Steele

My first advice would be to change your existing code. You're far better off
using IN than ORing everything together. Your current code should be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
I am using Access 2002. I have the following code on a button. This code
allows me to pick multiple Course Titles from a list box and generate
reports
for only those I've chosen. Now I need to add a second piece of criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need the date
too
because the same course can be listed more than once. I didn't write the
code below so I'm not sure how to change it. I tried adding it to temp =
in
the Else statement but it didn't work. Can anyone help me out? Thanks in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub
Click to expand...
 
A

Ann

Hi,

Thanks for the help. I'll have to look up IN because I don't know what that
does. I did copy the new code and pasted it into the VB window. Two lines
immediately turned red and I had syntax errors. Only the first line of each,
not the second was red.

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp

and

DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

Douglas J. Steele said:
My first advice would be to change your existing code. You're far better off
using IN than ORing everything together. Your current code should be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
I am using Access 2002. I have the following code on a button. This code
allows me to pick multiple Course Titles from a list box and generate
reports
for only those I've chosen. Now I need to add a second piece of criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need the date
too
because the same course can be listed more than once. I didn't write the
code below so I'm not sure how to change it. I tried adding it to temp =
in
the Else statement but it didn't work. Can anyone help me out? Thanks in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub
Click to expand...


.
Click to expand...
 
A

Ann

I figured out the second one but am still trying to get the first one.

Douglas J. Steele said:
My first advice would be to change your existing code. You're far better off
using IN than ORing everything together. Your current code should be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
I am using Access 2002. I have the following code on a button. This code
allows me to pick multiple Course Titles from a list box and generate
reports
for only those I've chosen. Now I need to add a second piece of criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need the date
too
because the same course can be listed more than once. I didn't write the
code below so I'm not sure how to change it. I tried adding it to temp =
in
the Else statement but it didn't work. Can anyone help me out? Thanks in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub
Click to expand...


.
Click to expand...
 
A

Ann

Sorry for all the replies but I'm trying to figure it out. I'm not a
programmer but I changed the first problem line to:

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & " & ", "

I'm not sure it's right but I did get past that line. Now the last line of
what's listed below is red.

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


Douglas J. Steele said:
My first advice would be to change your existing code. You're far better off
using IN than ORing everything together. Your current code should be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
I am using Access 2002. I have the following code on a button. This code
allows me to pick multiple Course Titles from a list box and generate
reports
for only those I've chosen. Now I need to add a second piece of criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need the date
too
because the same course can be listed more than once. I didn't write the
code below so I'm not sure how to change it. I tried adding it to temp =
in
the Else statement but it didn't work. Can anyone help me out? Thanks in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub
Click to expand...


.
Click to expand...
 
D

Douglas J. Steele

Sorry, my fault

temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "

On the other hand, it _was_ that way in your code, so your sample couldn't
have compiled either.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
Sorry for all the replies but I'm trying to figure it out. I'm not a
programmer but I changed the first problem line to:

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & " & ", "

I'm not sure it's right but I did get past that line. Now the last line
of
what's listed below is red.

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


Douglas J. Steele said:
My first advice would be to change your existing code. You're far better
off
using IN than ORing everything together. Your current code should be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
I am using Access 2002. I have the following code on a button. This
code
allows me to pick multiple Course Titles from a list box and generate
reports
for only those I've chosen. Now I need to add a second piece of
criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need the date
too
because the same course can be listed more than once. I didn't write
the
code below so I'm not sure how to change it. I tried adding it to temp
=
in
the Else statement but it didn't work. Can anyone help me out? Thanks
in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub
Click to expand...


.
Click to expand...
Click to expand...
 
A

Ann

Thanks, that one is fine but the last line in the following is still red
(starting with "[dtmStartDate]....

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format([dtmStartDate], "\#yyyy\-mm\-dd\#")


Douglas J. Steele said:
Sorry, my fault

temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "

On the other hand, it _was_ that way in your code, so your sample couldn't
have compiled either.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
Sorry for all the replies but I'm trying to figure it out. I'm not a
programmer but I changed the first problem line to:

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & " & ", "

I'm not sure it's right but I did get past that line. Now the last line
of
what's listed below is red.

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


Douglas J. Steele said:
My first advice would be to change your existing code. You're far better
off
using IN than ORing everything together. Your current code should be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I am using Access 2002. I have the following code on a button. This
code
allows me to pick multiple Course Titles from a list box and generate
reports
for only those I've chosen. Now I need to add a second piece of
criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need the date
too
because the same course can be listed more than once. I didn't write
the
code below so I'm not sure how to change it. I tried adding it to temp
=
in
the Else statement but it didn't work. Can anyone help me out? Thanks
in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub



.
Click to expand...
Click to expand...


.
Click to expand...
 
A

Ann

This was my original line:

temp = "[txtCourseTitle] = " & Chr(39) & ctl.ItemData(var) & Chr(39) & ", "

There was "[txtCourseTitle] = ". That was taking out. I don't need that
part?

Douglas J. Steele said:
Sorry, my fault

temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "

On the other hand, it _was_ that way in your code, so your sample couldn't
have compiled either.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
Sorry for all the replies but I'm trying to figure it out. I'm not a
programmer but I changed the first problem line to:

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & " & ", "

I'm not sure it's right but I did get past that line. Now the last line
of
what's listed below is red.

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


Douglas J. Steele said:
My first advice would be to change your existing code. You're far better
off
using IN than ORing everything together. Your current code should be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I am using Access 2002. I have the following code on a button. This
code
allows me to pick multiple Course Titles from a list box and generate
reports
for only those I've chosen. Now I need to add a second piece of
criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need the date
too
because the same course can be listed more than once. I didn't write
the
code below so I'm not sure how to change it. I tried adding it to temp
=
in
the Else statement but it didn't work. Can anyone help me out? Thanks
in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Douglas J. Steele

You're right: it makes sense now how I ended up with that spurious quote.
No, you don't need to include the field name each time: that's one of the
bonuses of using IN.

Instead of

Field1 = 'value1' Or Field1 = 'value2' Or Field1 = 'value3'

you can use the shorter

Field1 IN ('value1', 'value2', 'value3')

It should be
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")

(There was a missing ampersand at the end of the second line)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
This was my original line:

temp = "[txtCourseTitle] = " & Chr(39) & ctl.ItemData(var) & Chr(39) & ",
"

There was "[txtCourseTitle] = ". That was taking out. I don't need that
part?

Douglas J. Steele said:
Sorry, my fault

temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "

On the other hand, it _was_ that way in your code, so your sample
couldn't
have compiled either.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
Sorry for all the replies but I'm trying to figure it out. I'm not a
programmer but I changed the first problem line to:

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & " & ", "

I'm not sure it's right but I did get past that line. Now the last
line
of
what's listed below is red.

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


:

My first advice would be to change your existing code. You're far
better
off
using IN than ORing everything together. Your current code should be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I am using Access 2002. I have the following code on a button. This
code
allows me to pick multiple Course Titles from a list box and
generate
reports
for only those I've chosen. Now I need to add a second piece of
criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need the
date
too
because the same course can be listed more than once. I didn't
write
the
code below so I'm not sure how to change it. I tried adding it to
temp
=
in
the Else statement but it didn't work. Can anyone help me out?
Thanks
in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub



.
Click to expand...


.
Click to expand...
Click to expand...
 
A

Ann

Hi,

I still get a syntax error on the first two lines of the following. All
three lines are red, except the underscore at the end of the first line, and
the first two lines are highlighted when I click the debug button.

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


Douglas J. Steele said:
You're right: it makes sense now how I ended up with that spurious quote.
No, you don't need to include the field name each time: that's one of the
bonuses of using IN.

Instead of

Field1 = 'value1' Or Field1 = 'value2' Or Field1 = 'value3'

you can use the shorter

Field1 IN ('value1', 'value2', 'value3')

It should be
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")

(There was a missing ampersand at the end of the second line)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
This was my original line:

temp = "[txtCourseTitle] = " & Chr(39) & ctl.ItemData(var) & Chr(39) & ",
"

There was "[txtCourseTitle] = ". That was taking out. I don't need that
part?

Douglas J. Steele said:
Sorry, my fault

temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "

On the other hand, it _was_ that way in your code, so your sample
couldn't
have compiled either.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Sorry for all the replies but I'm trying to figure it out. I'm not a
programmer but I changed the first problem line to:

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & " & ", "

I'm not sure it's right but I did get past that line. Now the last
line
of
what's listed below is red.

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


:

My first advice would be to change your existing code. You're far
better
off
using IN than ORing everything together. Your current code should be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I am using Access 2002. I have the following code on a button. This
code
allows me to pick multiple Course Titles from a list box and
generate
reports
for only those I've chosen. Now I need to add a second piece of
criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need the
date
too
because the same course can be listed more than once. I didn't
write
the
code below so I'm not sure how to change it. I tried adding it to
temp
=
in
the Else statement but it didn't work. Can anyone help me out?
Thanks
in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Douglas J. Steele

The line continuation character is missing at the end of the second line:

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " & _
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ann said:
Hi,

I still get a syntax error on the first two lines of the following. All
three lines are red, except the underscore at the end of the first line,
and
the first two lines are highlighted when I click the debug button.

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


Douglas J. Steele said:
You're right: it makes sense now how I ended up with that spurious
quote.
No, you don't need to include the field name each time: that's one of the
bonuses of using IN.

Instead of

Field1 = 'value1' Or Field1 = 'value2' Or Field1 = 'value3'

you can use the shorter

Field1 IN ('value1', 'value2', 'value3')

It should be
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")

(There was a missing ampersand at the end of the second line)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Ann said:
This was my original line:

temp = "[txtCourseTitle] = " & Chr(39) & ctl.ItemData(var) & Chr(39) &
",
"

There was "[txtCourseTitle] = ". That was taking out. I don't need
that
part?

:

Sorry, my fault

temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "

On the other hand, it _was_ that way in your code, so your sample
couldn't
have compiled either.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Sorry for all the replies but I'm trying to figure it out. I'm not
a
programmer but I changed the first problem line to:

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & " & ", "

I'm not sure it's right but I did get past that line. Now the last
line
of
what's listed below is red.

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


:

My first advice would be to change your existing code. You're far
better
off
using IN than ORing everything together. Your current code should
be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview,
,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I am using Access 2002. I have the following code on a button.
This
code
allows me to pick multiple Course Titles from a list box and
generate
reports
for only those I've chosen. Now I need to add a second piece of
criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need
the
date
too
because the same course can be listed more than once. I didn't
write
the
code below so I'm not sure how to change it. I tried adding it
to
temp
=
in
the Else statement but it didn't work. Can anyone help me out?
Thanks
in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview,
,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
A

Ann

Hi,
I have a new error: "Error 2465", MS can't find the field "|" referred to
in your expression.

It's the same three lines being highlighted. I also changed the format to
the date because mine is entered as mm/dd/yyyy.

I listed all the code again.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " & _
"[dtmStartDate] = " & Format([dtmStartDate], "\#mm\/dd\/yyyy\#")
'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

Douglas J. Steele said:
The line continuation character is missing at the end of the second line:

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " & _
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ann said:
Hi,

I still get a syntax error on the first two lines of the following. All
three lines are red, except the underscore at the end of the first line,
and
the first two lines are highlighted when I click the debug button.

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


Douglas J. Steele said:
You're right: it makes sense now how I ended up with that spurious
quote.
No, you don't need to include the field name each time: that's one of the
bonuses of using IN.

Instead of

Field1 = 'value1' Or Field1 = 'value2' Or Field1 = 'value3'

you can use the shorter

Field1 IN ('value1', 'value2', 'value3')

It should be
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")

(There was a missing ampersand at the end of the second line)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

This was my original line:

temp = "[txtCourseTitle] = " & Chr(39) & ctl.ItemData(var) & Chr(39) &
",
"

There was "[txtCourseTitle] = ". That was taking out. I don't need
that
part?

:

Sorry, my fault

temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "

On the other hand, it _was_ that way in your code, so your sample
couldn't
have compiled either.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Sorry for all the replies but I'm trying to figure it out. I'm not
a
programmer but I changed the first problem line to:

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & " & ", "

I'm not sure it's right but I did get past that line. Now the last
line
of
what's listed below is red.

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


:

My first advice would be to change your existing code. You're far
better
off
using IN than ORing everything together. Your current code should
be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview,
,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I am using Access 2002. I have the following code on a button.
This
code
allows me to pick multiple Course Titles from a list box and
generate
reports
for only those I've chosen. Now I need to add a second piece of
criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need
the
date
too
because the same course can be listed more than once. I didn't
write
the
code below so I'm not sure how to change it. I tried adding it
to
temp
=
in
the Else statement but it didn't work. Can anyone help me out?
Thanks
in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview,
,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub



.



.



.
Click to expand...
Click to expand...

.
Click to expand...
 
A

Ann

Here's what's in my listbox.

Course One 04/22/2009
Course Two 05/01/2009
Course Three 01/02/2010

and I need all three or two, etc.

Ann said:
Hi,
I have a new error: "Error 2465", MS can't find the field "|" referred to
in your expression.

It's the same three lines being highlighted. I also changed the format to
the date because mine is entered as mm/dd/yyyy.

I listed all the code again.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " & _
"[dtmStartDate] = " & Format([dtmStartDate], "\#mm\/dd\/yyyy\#")
'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

Douglas J. Steele said:
The line continuation character is missing at the end of the second line:

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " & _
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ann said:
Hi,

I still get a syntax error on the first two lines of the following. All
three lines are red, except the underscore at the end of the first line,
and
the first two lines are highlighted when I click the debug button.

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


:

You're right: it makes sense now how I ended up with that spurious
quote.
No, you don't need to include the field name each time: that's one of the
bonuses of using IN.

Instead of

Field1 = 'value1' Or Field1 = 'value2' Or Field1 = 'value3'

you can use the shorter

Field1 IN ('value1', 'value2', 'value3')

It should be
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")

(There was a missing ampersand at the end of the second line)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

This was my original line:

temp = "[txtCourseTitle] = " & Chr(39) & ctl.ItemData(var) & Chr(39) &
",
"

There was "[txtCourseTitle] = ". That was taking out. I don't need
that
part?

:

Sorry, my fault

temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "

On the other hand, it _was_ that way in your code, so your sample
couldn't
have compiled either.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Sorry for all the replies but I'm trying to figure it out. I'm not
a
programmer but I changed the first problem line to:

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & " & ", "

I'm not sure it's right but I did get past that line. Now the last
line
of
what's listed below is red.

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


:

My first advice would be to change your existing code. You're far
better
off
using IN than ORing everything together. Your current code should
be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview,
,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I am using Access 2002. I have the following code on a button.
This
code
allows me to pick multiple Course Titles from a list box and
generate
reports
for only those I've chosen. Now I need to add a second piece of
criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need
the
date
too
because the same course can be listed more than once. I didn't
write
the
code below so I'm not sure how to change it. I tried adding it
to
temp
=
in
the Else statement but it didn't work. Can anyone help me out?
Thanks
in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview,
,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

After the line where you assing the value to strCriteria, put a line of code

Debug.Print strCriteria

Once your code runs, go the Immediate Window (using Ctrl-G) and see what's
printed there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ann said:
Hi,
I have a new error: "Error 2465", MS can't find the field "|" referred to
in your expression.

It's the same three lines being highlighted. I also changed the format to
the date because mine is entered as mm/dd/yyyy.

I listed all the code again.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " & _
"[dtmStartDate] = " & Format([dtmStartDate], "\#mm\/dd\/yyyy\#")
'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse", acViewPreview, ,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

Douglas J. Steele said:
The line continuation character is missing at the end of the second line:

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " & _
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ann said:
Hi,

I still get a syntax error on the first two lines of the following.
All
three lines are red, except the underscore at the end of the first
line,
and
the first two lines are highlighted when I click the debug button.

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")


:

You're right: it makes sense now how I ended up with that spurious
quote.
No, you don't need to include the field name each time: that's one of
the
bonuses of using IN.

Instead of

Field1 = 'value1' Or Field1 = 'value2' Or Field1 = 'value3'

you can use the shorter

Field1 IN ('value1', 'value2', 'value3')

It should be
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND " &
"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")

(There was a missing ampersand at the end of the second line)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

This was my original line:

temp = "[txtCourseTitle] = " & Chr(39) & ctl.ItemData(var) & Chr(39)
&
",
"

There was "[txtCourseTitle] = ". That was taking out. I don't need
that
part?

:

Sorry, my fault

temp = Chr(39) & ctl.ItemData(var) & Chr(39) & ", "

On the other hand, it _was_ that way in your code, so your sample
couldn't
have compiled either.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Sorry for all the replies but I'm trying to figure it out. I'm
not
a
programmer but I changed the first problem line to:

temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & " & ", "

I'm not sure it's right but I did get past that line. Now the
last
line
of
what's listed below is red.

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField,
"\#yyyy\-mm\-dd\#")


:

My first advice would be to change your existing code. You're
far
better
off
using IN than ORing everything together. Your current code
should
be

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "Chr(39) & ctl.ItemData(var) & Chr(39) & ", "
strCriteria = strCriteria & temp
Next var
End If

'Construct the WHERE clause
strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse",
acViewPreview,
,
strCriteria

ExitOpen:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If

End Sub

If you do that, then you can change

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") "

to

strCriteria = "[txtCourseTitle] IN (" & _
Left$(strCriteria, Len(strCriteria) - 2) & ") AND "
"[dtmStartDate] = " & Format(YourDateField,
"\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I am using Access 2002. I have the following code on a button.
This
code
allows me to pick multiple Course Titles from a list box and
generate
reports
for only those I've chosen. Now I need to add a second piece
of
criteria
to
the list. I now have txtCourseTitle and dtmStartDate. I need
the
date
too
because the same course can be listed more than once. I
didn't
write
the
code below so I'm not sure how to change it. I tried adding
it
to
temp
=
in
the Else statement but it didn't work. Can anyone help me
out?
Thanks
in
advance.

Private Sub Command8_Click()

Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String

Set frm = Forms!frmSummaryOfEvaluationsByCourseParameter
Set ctl = frm!lstCourses

'If no selection, display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a course."
Exit Sub
'builds SQL WHERE clause
'using each of the selected projects
Else
For Each var In ctl.ItemsSelected
temp = "[txtCourseTitle] = " & Chr(39) & _
ctl.ItemData(var) & Chr(39) & " Or "
strCriteria = strCriteria & temp
Next var
End If

'deletes the final Or from the WHERE clause
strCriteria = Left$(strCriteria, Len(strCriteria) - 4)

'outputs report
On Error GoTo ErrorOpen
DoCmd.OpenReport "rptSummaryOfEvalutionsByCourse",
acViewPreview,
,
strCriteria

ExitOpen:
Exit Sub

ErrorOpen:
If Err = 2501 Then
Resume ExitOpen
Else
MsgBox Err.Description
Resume ExitOpen
End If


Set ctl = Nothing
Set frm = Nothing
End Sub



.



.



.
Click to expand...

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Criteria in Listbox 3
Multiple Criteria in Code 0
Run delete query instead of report... 4
Blank Report Message 2
Running a parameter query off more than one multi-select list box 1
Error in multiple select in a list box code 4
ctl.Name 1
VBA works in orignal but fails in copy of database 1

Top