424,066 Members | 2,120 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,066 IT Pros & Developers. It's quick & easy.

Multiple Criteria in Code

P: 23
Hi,

I'm hoping someone can help me. I'm not a programmer but found this really nice piece of code that I have been using but it needs to be altered. I had been getting help from Microsoft's website but then they stopped.

I am using Access 2002. I have the following code on a button which was altered by someone who had been helping me but it still doesn't work. The 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 because I now have txtCourseTitle and dtmStartDate. Originally I only had txtCourseTitle. I need the date too because the same course can be listed more than once. The person that had been helping me told me I should be using IN instead of OR.

There were problems that were fixed but others popped up. Right now this line in the code is red.

"[dtmStartDate] = " & Format(YourDateField, "\#yyyy\-mm\-dd\#")

Here is the code he gave me.

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(YourDateField, "\#yyyy\-mm\-dd\#")

'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
Feb 26 '10 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 634
Hi

On the basis that this works
Expand|Select|Wrap|Line Numbers
  1. strCriteria = "[txtCourseTitle] IN (" & _
  2. Left$(strCriteria, Len(strCriteria) - 2) & ")"
Then I would suggest this
Expand|Select|Wrap|Line Numbers
  1. strCriteria = "[txtCourseTitle] IN (" & _
  2. Left$(strCriteria, Len(strCriteria) - 2) & ") AND [dtmStartDate] = #" & Format(YourDateField, "mm/dd/yy") & "#"
??

MTB
Mar 2 '10 #2

P: 23
Thank you for your help. I still had problems with this but did manage to get the it to work a different way. I really don't understand IN and it's a hard thing to Google. I went back to the original way. I'm not sure if it's the best way but it does work. The code is below:
Dim frm As Form, ctl As ListBox, var As Variant
Dim strCriteria As String, temp As String
'Dim dtmStartDate As Date
'Dim txtCourseTitle 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) & _
Me.lstCourses.Column(0, var) & Chr(39) & " And " & "[dtmStartDate] = " & _
Format(Me.lstCourses.Column(1, var), "\#mm\/dd\/yyyy#\" & " 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:
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
Mar 2 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.