Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining fields (Access03)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining fields (Access03)

    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.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining fields (Access03)

    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 600x800 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.Coun t = 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.Coun t = 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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining fields (Access03)

    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] & "#"

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining fields (Access03)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining fields (Access03)

    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] & "#"

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining fields (Access03)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combining fields (Access03)

    You have a problem right here:

    strWhere4 = "DateOfClassStart= Between" &

    It should be

    strWhere4 = "DateOfClassStart Between" &
    Charlotte

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining fields (Access03)

    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

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining fields (Access03)

    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.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining fields (Access03)

    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] & "#)"

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining fields (Access03)

    Bless you Hans. That worked. Thank you. Fay

  13. #13
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining fields (Access03)

    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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining fields (Access03)

    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.

  15. #15
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining fields (Access03)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •