Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More parameters to add (Access2000-03)

    I ran into the thing with following. I have
    Private Sub Summary_Click()
    Dim strWhere As String
    On Error GoTo ErrHandler

    DoCmd.SetWarnings False

    DoCmd.OpenQuery "1"
    DoCmd.OpenQuery "2"

    If Not IsNull(Forms!Reports_Switchboard!ID_Name) Then
    strWhere = "REC_ID=" & Chr(34) & Forms!Reports_Switchboard!ID_Name.Column(0) & Chr(34)


    End If


    DoCmd.OpenReport ReportName:="REC_REPORT", View:=acViewPreview, WhereCondition:=strWhere
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Report canceled - ignore this
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub
    __________________________________________________ ___

    However 2 dates Date From and Date To text boxes (where they typing the date) were added.
    How do I accomodate it all so Report reacts to dates?
    Date field type is Date/Time

    Thanks

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

    Re: More parameters to add (Access2000-03)

    Will the Date From and Date To text boxes always be filled in, or do you want to allow the user to leave one or both of them blank?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More parameters to add (Access2000-03)

    They will mostly leave them blank.

    Thanks so much

    P.S For the queries I am using your solution :
    >=[Forms]![Switchboard]![StartDate] Or [Forms]![Switchboard]![StartDate] Is Null

    <=[Forms]![Switchboard]![EndDate] Or [Forms]![Switchboard]![EndDate] Is Null

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

    Re: More parameters to add (Access2000-03)

    Change the lines
    <code>
    If Not IsNull(Forms!Reports_Switchboard!ID_Name) Then
    strWhere = "REC_ID=" & Chr(34) & Forms!Reports_Switchboard!ID_Name.Column(0) & Chr(34)
    End If
    </code>
    to
    <code>
    If Not IsNull(Forms!Reports_Switchboard!ID_Name) Then
    strWhere = " AND REC_ID=" & Chr(34) & Forms!Reports_Switchboard!ID_Name.Column(0) & Chr(34)
    End If

    If Not IsNull(Forms!Reports_Switchboard![Date From]) Then
    strWhere = " AND [DateField] >= #" & Format(Forms!Reports_Switchboard![Date From], "mm/dd/yyyy") & "#"
    End If

    If Not IsNull(Forms!Reports_Switchboard![Date To]) Then
    strWhere = " AND [DateField] <= #" & Format(Forms!Reports_Switchboard![Date To], "mm/dd/yyyy") & "#"
    End If

    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 6)
    End If
    </code>
    Replace DateField with the name of the relevant date field.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More parameters to add (Access2000-03)

    Brilliant! Thanks as always!

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More parameters to add (Access2000-03)

    Hans,
    here is what's happening.

    If Not IsNull(Forms!Applicant_Activity_Reports_Switchboar d!Recruiter_ID_Name) Then
    strWhere = " AND RECRUITER_ID =" & Chr(34) & Forms!Applicant_Activity_Reports_Switchboard!Recru iter_ID_Name.Column(0) & Chr(34)
    End If
    ------strWhere reads RECRUITER_ID fine
    If Not IsNull(Forms!Applicant_Activity_Reports_Switchboar d!Date_From) Then
    strWhere = " AND DateFrom >= #" & Format(Forms!Applicant_Activity_Reports_Switchboar d!Date_From, "mm/dd/yyyy") & "#"
    End If
    ----------strWhere reads DateFrom fine from the first textbox Date_From

    If Not IsNull(Forms!Applicant_Activity_Reports_Switchboar d!Date_To) Then
    strWhere = " AND DateTo <= #" & Format(Applicant_Activity_Reports_Switchboard!Date _To, "mm/dd/yyyy") & "#"
    End If
    ----------strWhere holds the value of the DateFrom and skips right to the error and I am seeing 'Object required' error.

    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 6)
    End If


    Anything I am missing. It is not reading second date value! Odd isn't it?

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

    Re: More parameters to add (Access2000-03)

    Oops - one mistake by me (sorry about that) and one by you. Try this:
    <code>
    If Not IsNull(Forms!Applicant_Activity_Reports_Switchboar d!Recruiter_ID_Name) Then
    strWhere = " AND RECRUITER_ID =" & Chr(34) & Forms!Applicant_Activity_Reports_Switchboard!Recru iter_ID_Name.Column(0) & Chr(34)
    End If

    If Not IsNull(Forms!Applicant_Activity_Reports_Switchboar d!Date_From) Then
    strWhere = strWhere & " AND DateFrom >= #" & Format(Forms!Applicant_Activity_Reports_Switchboar d!Date_From, "mm/dd/yyyy") & "#"
    End If

    If Not IsNull(Forms!Applicant_Activity_Reports_Switchboar d!Date_To) Then
    strWhere = strWhere & " AND DateTo <= #" & Format(Forms!Applicant_Activity_Reports_Switchboar d!Date_To, "mm/dd/yyyy") & "#"
    End If

    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 6)
    End If
    </code>
    I forgot to concatenate strWhere with the previous value, and you forgot Forms! one time.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More parameters to add (Access2000-03)

    The difference is that YOU can find your mistakes:-)

    Thanks so much!

Posting Permissions

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