locked
Trouble selecting specified data from a table using VB code within MS Access RRS feed

  • Question

  • Hi,

    I can and have seen similar queries, however, since my database is a little more complex (in my basic opinion :P ) I would really appreciate a little more specific help.

    I have a table called: Call_Quality_Issues

    This comprises the following fields and Data types:
    ID (default primary key)
    Analyst = Text
    Call reference = Text
    Feedback = Memo
    Date of Feedback = Date/Time
    CQ Type = Text

    I have a form within the Access Database that has 2 "Select Date" combo boxes (frmCQdates):
    CMB_FBCH_DateFrom & CMB_FBCH_DateUntil

    They both bring up an ActiveX control (FRM_FBCH_mscal1) to populate the date using the following code:
    Private Sub CMB_FBCH_DateFrom_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

    -------------------------------------------------------------------------------

    Set cboOriginator = CMB_FBCH_DateFrom

    FRM_FBCH_mscal1.Visible = True
    FRM_FBCH_mscal1.SetFocus

    If Not IsNull(cboOriginator) Then
       FRM_FBCH_mscal1.Value = cboOriginator.Value
    Else
       FRM_FBCH_mscal1.Value = Date
    End If

    End Sub

    Private Sub CMB_FBCH_DateUntil_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

    Set cboOriginator = CMB_FBCH_DateUntil

    FRM_FBCH_mscal1.Visible = True
    FRM_FBCH_mscal1.SetFocus

    If Not IsNull(cboOriginator) Then
       FRM_FBCH_mscal1.Value = cboOriginator.Value
    Else
       FRM_FBCH_mscal1.Value = Date
    End If

    End Sub

    Private Sub FRM_FBCH_mscal1_Click()

    cboOriginator.Value = FRM_FBCH_mscal1.Value
    cboOriginator.SetFocus
    FRM_FBCH_mscal1.Visible = False
    Set cboOriginator = Nothing

    End Sub

    -------------------------------------------------------------------------------

    This part works perfectly, but I am having some real trouble trying to modify this code so I can select
    This is what I currently have for the button (cmdAll):

    -------------------------------------------------------------------------------

    Private Sub cmdAll_Click()

    Dim strReport As String
        Dim strDateField As String
        Dim strWhere As String
        Dim strAnalyst As String
        Dim lngView As Long
        Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
       
        'DO set the values in the next 3 lines.
        strReport = "Call_Quality_Issues"      'Put your report name in these quotes.
        strDateField = "[Analyst]" 'Put your field name in the square brackets in these quotes.
        strAnalyst = "Analyst"
        lngView = acViewPreview     'Use acViewNormal to print instead of preview.
       
        strAnalyst = CMB_Analyst.Value
       
        'Build the filter string.
        If IsDate(Me.CMB_FBCH_DateFrom) Then
            strWhere = "(" & strDateField & " >= " & Format(Me.CMB_FBCH_DateFrom, strcJetDate) & ")"
        End If
        If IsDate(Me.CMB_FBCH_DateUntil) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(" & strDateField & " < " & Format(Me.CMB_FBCH_DateUntil + 1, strcJetDate) & ")"
        End If
       
        'Close the report if already open: otherwise it won't filter properly.
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If
       
        'Open the report.
        'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
         DoCmd.OpenReport strReport, lngView, , strWhere


    End Sub

    -------------------------------------------------------------------------------

    All I am trying to do is use Combo boxes to pick the "Analyst" and date range for which to pull from the table and produce in report form.  I am betting there is an easier way of doing this.... just been so many years since I done any coding, it's already been a massive refresher.

    I should note that I got the above section of code from a website.

    Thanks in advance for your assistance.

    Dave

    • Moved by Mark Liu-lxf Wednesday, December 14, 2011 3:55 AM VBA (From:Visual Basic General)
    Monday, December 12, 2011 3:00 PM

Answers

  • If memory serves me...MS Access dates require the # in front for comparison.

    Try this:

        'Build the filter string.
        If IsDate(Me.CMB_FBCH_DateFrom) Then
            strWhere = "(#" & strDateField & " >= #" & Format(Me.CMB_FBCH_DateFrom, strcJetDate) & ")"
        End If
        If IsDate(Me.CMB_FBCH_DateUntil) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(#" & strDateField & " < #" & Format(Me.CMB_FBCH_DateUntil + 1, strcJetDate) & ")"
        End If
    
    

     


    Already reported as abusive
    • Proposed as answer by Mark Liu-lxf Wednesday, December 14, 2011 3:53 AM
    • Marked as answer by danishani Wednesday, January 11, 2012 11:58 PM
    Monday, December 12, 2011 3:14 PM

All replies

  • This forum is for Visual Basic inside Visual Studio Net

    However, there is a VBA forum

    http://social.msdn.microsoft.com/Forums/en-US/isvvba


    Success
    Cor
    Monday, December 12, 2011 3:13 PM
  • If memory serves me...MS Access dates require the # in front for comparison.

    Try this:

        'Build the filter string.
        If IsDate(Me.CMB_FBCH_DateFrom) Then
            strWhere = "(#" & strDateField & " >= #" & Format(Me.CMB_FBCH_DateFrom, strcJetDate) & ")"
        End If
        If IsDate(Me.CMB_FBCH_DateUntil) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(#" & strDateField & " < #" & Format(Me.CMB_FBCH_DateUntil + 1, strcJetDate) & ")"
        End If
    
    

     


    Already reported as abusive
    • Proposed as answer by Mark Liu-lxf Wednesday, December 14, 2011 3:53 AM
    • Marked as answer by danishani Wednesday, January 11, 2012 11:58 PM
    Monday, December 12, 2011 3:14 PM
  • Hi KickAssDave,

    Welcome to the MSDN forum.

    This queue is about Visual Basic. I’m afraid that your topic about VBA is unsuitable here. For better support, I will move this thread to Visual Basic for Applications (VBA) forum.

    Sorry for any inconvenient and have a nice day.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 14, 2011 3:54 AM