473,321 Members | 1,667 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Multiple Criteria in Code

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
2 1791
MikeTheBike
639 Expert 512MB
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
AnnMV8
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

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

Similar topics

4
by: ShyGuy | last post by:
I have a table with 4 fields. Three are used for criteria. I can get the DLookup to work with 1 criteria with the following but can't get it to work with 2 or three. NumofAppts = DLookup("",...
0
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list...
4
by: carl.barrett | last post by:
Hi, I have a list box that displays 2 columns. Behind it sits a query with five columns. These are Column1 (DOB), column2 (a concatenated string of Surname Forname, Title), Column3 (Surname),...
0
by: Greg Strong | last post by:
Hello All, In the past I've used a combo box with the 'row source' being an Access SQL union query to select "All" or 1 for only 1 criteria in a query. An example is as follows: SELECT 0 As...
7
by: Ceebaby via AccessMonster.com | last post by:
Hi All Here's hoping someone can help me with this. I have a report based on a query where the criteria for 4 of the fields is set from an unbound form. I want the user to be able to select any...
1
by: Kye | last post by:
It has been a while since using vb an now I have started a project and I am not sure where my mistake is in my code. Basically what I am doing is trying to build a multiple search string with...
2
by: dlevene | last post by:
Hi - newbie here, be gentle. In Access 2003, I've created a report (based on a query) to produce mailing labels for all records where = "Community". is a combo-box field with the values coming from...
4
by: jvan2008 | last post by:
"Form1" combobox "cboModel" Row Source SELECT ., . FROM tblModel ORDER BY ; combobox "cboContactName" SELECT . FROM Query1 ORDER BY ;
3
by: kstevens | last post by:
Please help. I know the sysntax is wrong. Here are some details. I am looking for the sum of past shipped items. I originally wrote this Dsum with only one criteria, but because of multiple...
5
Frinavale
by: Frinavale | last post by:
I'm playing with an ASP.NET MVC application and I've run into a bit of a problem. I am pretty new to ASP.NET MVC and just barely understand the basics to get things to work at this point. I have a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.