Combining fields (Access03)

Topic

New Reply

I am trying to set date limiting criteria. I have a option group which based on the selected option sets the txtCriteria to either =, , <=, or AND. Also it displays one or two text boxes, txtRosterStart or txtRosterEnd. That all works correctly. I now need to retrive and use the txtCriteria, txtRosterStart, or txtRosterEnd. Here is where the trouble begins. I have the follow SQL the WHERE line is the issue.

SELECT tblCombinedRegistration.LearnerID, tblCombinedRegistration.ClassName, tblCombinedRegistration.ClassNumber, tblCombinedRegistration.ClassID, tblCombinedRegistration.CancelledNoShow, tblCombinedRegistration.DateTimeRegistered, tblCombinedRegistration.DateOfClassStart, tblCombinedRegistration.ISDateOfClassStart, tblCombinedRegistration.Grade
FROM tblCombinedRegistration
WHERE (((tblCombinedRegistration.DateOfClassStart)=[Forms]![frmReports]![txtCriteria] & [Forms]![frmReports]![txtRosterStart]));

The above Where gives me the error message: It is typed incorrectly (I used the builder) or to complex.

The following does work. WHERE (((tblCombinedRegistration.DateOfClassStart)=[Forms]![frmReports]![txtRosterStart]));

Any help or direction would be appreciated. Thank you. Fay

Reply | Quote

Replies

You already have an = operator in the WHERE part, you cannot just combine that with < etc. Also, AND makes no sense in this situation. And, perhaps most importantly, you cannot specify the comparison operator dynamically in a stored query. It will only work if you build the SQL string in code.

Dim strSQL As String
strSQL = "SELECT LearnerID, ClassName, ClassNumber, ClassID, " & _
"CancelledNoShow, DateTimeRegistered, DateOfClassStart, " & _
"ISDateOfClassStart, Grade FROM tblCombinedRegistration " & _
"WHERE DateOfClassStart " & Me.txtCriteria & " #" & Me.txtRosterStart & "#"

What to do with this SQL string depends on what you want to accomplish.

Reply | Quote

On frmReports I have a list box for each departments, credentials, and classes. I have all of the code working correcly to limit to the appropriate selections. From frmReports I go to form frmReportsPicker to pick the actual reports. I move to this second form because of space issues, there are users still using 600×800 resolution.

I need to have the latitude to limit or not limit the returned information based on dates.

What I am working with is this whole block of code the sections I am still trying to figure out is the strWhere3 string and the If statement just prior to the display of the report. I am now getting a syntax error in the strWhere3 section.

Private Sub cmdWhoAttend_Click()
On Error GoTo Err_cmdWhoAttend_Click

Dim stDocName As String
Dim strInactive As String
Dim strDate As String
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim strWhere3 As String

If Forms!frmReports!lstDept.ItemsSelected.Count = 0 And Forms!frmReports!lstCredentials.ItemsSelected.Count = 0 And Forms!frmReports!lstClasses.ItemsSelected.Count = 0 Then
MsgBox “No departments, credentials, or classes are selected!!”, vbInformation
[Forms]![frmReports].SetFocus
Exit Sub
ElseIf Forms!frmReports!lstDept.ItemsSelected.Count = 0 Or Forms!frmReports!lstCredentials.ItemsSelected.Count = 0 Or Forms!frmReports!lstClasses.ItemsSelected.Count = 0 Then
MsgBox “Either no departments, credentials, or classes are selected!!”, vbInformation
[Forms]![frmReports].SetFocus
Exit Sub
End If

‘Runs the function that creates a table holding registrations based on the
‘lstClass selected items.
Classes

For Each varItm In Forms!frmReports!lstDept.ItemsSelected
strWhere1 = strWhere1 & “, ” & Chr(34) & Forms!frmReports!lstDept.ItemData(varItm) & Chr(34)
Next varItm
strWhere1 = “PerDiem2Unit In (” & Mid(strWhere1, 3) & “)”

For Each varItm In Forms!frmReports!lstCredentials.ItemsSelected
strWhere2 = strWhere2 & “, ” & Chr(34) & Forms!frmReports!lstCredentials.ItemData(varItm) & Chr(34)
Next varItm
strWhere2 = “Credential In (” & Mid(strWhere2, 3) & “)”

‘ SQL to capture class dates
strWhere3 = “SELECT tblCombinedRegistration.LearnerID, ” & _
“tblCombinedRegistration.ClassName, tblCombinedRegistration.ClassNumber, ” & _
“tblCombinedRegistration.ClassID, tblCombinedRegistration.CancelledNoShow, ” & _
“tblCombinedRegistration.DateTimeRegistered, tblCombinedRegistration.DateOfClassStart, ” & _
“tblCombinedRegistration.ISDateOfClassStart, tblCombinedRegistration.Grade” & _
“FROM tblCombinedRegistration” & _
“WHERE (((tblCombinedRegistration.DateOfClassStart)=[Forms]![frmReports]![txtCriteria] & “#” & [Forms]![frmReports]![txtRosterStart]))&”#” ”

‘Compile report based on criteria set for date reporting
If Forms!frmReports!frmDates = 6 Then
strWhere = strWhere1 & ” AND ” & strWhere2
‘This ElseIF statement deals with the Between date criteria
ElseIf Forms!frmReports!frmDates = 7 Then
‘I need to be able to display two dates here
Else
strWhere = strWhere1 & ” AND ” & strWhere2 & ” AND ” & strWhere3
End If

stDocName = “rptWhoAttended”
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdWhoAttend_Click:
Exit Sub

Err_cmdWhoAttend_Click:
MsgBox Err.Description
Resume Exit_cmdWhoAttend_Click

End Sub

Thank you for your help. I am in over my head, again. Fay

Reply | Quote

Try

‘ SQL to capture class dates
strWhere3 = “SELECT LearnerID, ClassName, ClassNumber, ClassID, CancelledNoShow, ” & _
“DateTimeRegistered, DateOfClassStart, ISDateOfClassStart, Grade ” & _
“FROM tblCombinedRegistration WHERE DateOfClassStart=” & _
[Forms]![frmReports]![txtCriteria] & “#” & [Forms]![frmReports]![txtRosterStart] & “#”

Reply | Quote

I think the strWhere3 part is working now because I now get an error message saying the Syntax error in the query expression and goes on to list the selected departments, credentials, classes, and correct criteria (except it didn’t list the last # sign. I need to work on the IF statement it this point it think. Thanks for the help. I have to go out of town now. Fay

Reply | Quote

Oops, I was concentrating on the SQL part, but you shouldn’t provide a complete SQL string as WhereCondition, only the part after WHERE:

strWhere3 = “DateOfClassStart=” [Forms]![frmReports]![txtCriteria] & “#” & [Forms]![frmReports]![txtRosterStart] & “#”

Reply | Quote

During my 7 hour drive I was wondering if I could have shortened it down to what you did. Okay here is the current situation/code

Dim strWhere4 As String

‘ SQL to capture class dates
strWhere3 = “DateOfClassStart=” & _
[Forms]![frmReports]![txtCriteria] & “#” & [Forms]![frmReports]![txtRosterStart] & “#”

‘This is to handle the Between this date AND that date
strWhere4 = “DateOfClassStart= Between” & _
“#” & [Forms]![frmReports]![txtRosterStart] & “#” & ” AND ” & _
“#” & [Forms]![frmReports]![txtRosterEnd] & “#”

‘Compile report based on criteria set for date reporting
‘This part deals with those reports that are not date limited
If Forms!frmReports!frmDates = 7 Then
strWhere = strWhere1 & ” AND ” & strWhere2
‘This is the Between two dates option
ElseIf Forms!frmReports!frmDates = 6 Then
strWhere = strWhere1 & ” AND ” & strWhere2 & ” AND ” & strWhere4
‘This deals with all other date restrictions such as , =, and =
Else
strWhere = strWhere1 & ” AND ” & strWhere2 & ” AND ” & strWhere3
End If

The = 7 part works as it should.

The two last sections gives me “Syntax error ( missing operator) in query expression. I don’t see the error.

Thank you for your help.
Fay

Reply | Quote

You have a problem right here:

strWhere4 = “DateOfClassStart= Between” &

It should be

strWhere4 = “DateOfClassStart Between” &

Reply | Quote

I continued to get the error message with your line until I adapted it

Your line
strWhere4 = “DateOfClassStart Between” &
my line
strWhere4 = “DateOfClassStart Between ” &

I finally fixed strWhere3
strWhere = “DateOfClassStart ” &
The change here was the removal of the =.

Thank you to Hans and Charlotte for all of your help. It work exactly like I envisioned it and need it to work. Fay

Reply | Quote

I have the following code all worked correctly until I added the OR ISDateOfClassStart section to the strWhere3 and strWhere4 lines.

When I run the code and specify dates that use the strWhere3 and strWhere4 my data is partially messed up.

I get all departments, including ones I didn’t select, and those departments only display the DateOfClassStart dates. The selected department is displayed and does properly display both the DateOfClassStart and ISDateOfClassStart dates. Also the report doesn’t properly restrict the credentials.

strWhere1 restricts departments
strWhere2 restricts credentials

‘ To capture class dates
strWhere3 = “DateOfClassStart OR ISDateOfClassStart ” & _
[Forms]![frmReports]![txtCriteria] & “#” & [Forms]![frmReports]![txtRosterStart] & “#”

‘To capture class registrations between two dates
strWhere4 = “DateOfClassStart OR ISDateOfClassStart Between ” & _
“#” & [Forms]![frmReports]![txtRosterStart] & “#” & ” AND ” & _
“#” & [Forms]![frmReports]![txtRosterEnd] & “#”

‘Compile report based on criteria set for date reporting
If Forms!frmReports!frmDates = 7 Then
strWhere = strWhere1 & ” AND ” & strWhere2
ElseIf Forms!frmReports!frmDates = 6 Then
strWhere = strWhere1 & ” AND ” & strWhere2 & ” AND ” & strWhere4
Else
strWhere = strWhere1 & ” AND ” & strWhere2 & ” AND ” & strWhere3
End If

Thank you for your help.

Reply | Quote

What is the purpose of “DateOfClassStart OR ISDateOfClassStart”? If you want to apply the date restriction to DateOfClassStart or to ISDateOfClassStart, you have to repeat the full criteria for both. Moreover, you must put “A Or B” between parentheses, for And has a higher priority than Or (just like * has a higher priority than + in arithmetic). For example:

strWhere3 = “(DateOfClassStart ” & [Forms]![frmReports]![txtCriteria] & “#” & _
[Forms]![frmReports]![txtRosterStart] & “# Or ISDateOfClassStart ” & _
[Forms]![frmReports]![txtCriteria] & “#” & [Forms]![frmReports]![txtRosterStart] & “#)”

and

strWhere4 = “(DateOfClassStart Between ” & “#” & [Forms]![frmReports]![txtRosterStart] & _
“# And #” & [Forms]![frmReports]![txtRosterEnd] & “# Or ISDateOfClassStart Between ” & _
“#” & [Forms]![frmReports]![txtRosterStart] & “# And #” & [Forms]![frmReports]![txtRosterEnd] & “#)”

Reply | Quote

Bless you Hans. That worked. Thank you. Fay

Reply | Quote

Different report, but building off this report that I am still pleased to say is working like a champ.

Now what I need to do is to report that the learner did or didn’t attend the classes so I modified the query that gives the final report so that there is a left join between two of the three tables used. Here is the SQL statement:

SELECT tblLearners.LearnerID, tblLearners.LastName, tblLearners.Nickname, tblLearners.Inactive, tblLearners.Credential, tblLearnerDepartments.StartDate, tblLearnerDepartments.PerDiem2Unit, tblCombinedRegistration.ClassName, tblCombinedRegistration.ClassNumber, tblCombinedRegistration.ClassID, tblCombinedRegistration.CancelledNoShow, tblCombinedRegistration.DateTimeRegistered, tblCombinedRegistration.DateOfClassStart, tblCombinedRegistration.Grade, tblCombinedRegistration.ISDateOfClassStart
FROM (tblLearners INNER JOIN tblLearnerDepartments ON tblLearners.LearnerID = tblLearnerDepartments.LearnerID) LEFT JOIN tblCombinedRegistration ON tblLearners.LearnerID = tblCombinedRegistration.LearnerID
WHERE (((tblLearners.Inactive)=0));

Everything works correctly up to the point where I put date restrictions into the mix. What I want to see is all of those that have had the class, for example after 01/01/2005, and if their class date was before that then there should be nothing after their name indicating they need the class again. How should I approach this issue? Bottom line what I need to see is everyone from the selected department listed on the report and the date should be present if it meets the date criteria and no date displayed if it doesn’t meet the date criteria. This way managers have an overall view of the situation regarding a specific requirement.

Thank you. Fay

Reply | Quote

If I understand correctly, you can do this by using two queries.

1) Create a query that selects the records from tblCombinedRegistration that meet the date criteria, for example

SELECT * FROM tblCombinedRegistration WHERE DateOfClassStart >= #01/01/2005#

or

SELECT * FROM tblCombinedRegistration WHERE DateOfClassStart >= [Forms]![frmSelect]![txtStartDate]

where txtStartDate is a text box on the form frmSelect in which the user enters a date.

2) Create a query like the one you now have, but with the above query instead of tblCombinedRegistration.

Reply | Quote

Thanks Hans, I actually figured it out and it is working as I want it to. I think I am starting to learn. What I would like to know is did I take the long route or was there an easier way to do what I wanted. Here is the code.

Long code fragment (3820 characters) moved to attachment by HansV

Reply | Quote

The code looks OK, but it could be made a little bit shorter, for example by using Select Case … End Select instead of repeating If .. ElseIf statements, and by looking for other repeating elements. I also assembled only the Where-condition for the dates that is actually needed, instead of always assembling both.

See the attached version (2480 characters instead of 3820).

Reply | Quote

Cool. As I just told my office mate I am still in using a sledge hammer when a tack hammer would work better. I am learning. Thanks Hans I will study what you did. Fay

Reply | Quote

You’re making real progress!

Reply | Quote