Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2014
    Posts
    4

    Unanswered: VBA Code help requested

    Am trying to modifying the following but am totally lost.

    Private Sub cmdOpenReportSingle_Click()
    On Error GoTo Err_Handler

    Const REPORTNAME = "1A"
    Const MESSAGETEXT = "Both a start and end date must be selected."
    Dim strCriteria As String
    Dim strDateFrom As String, strDateTo As String

    ' make sure a customer is selected
    If Not IsNull(Me.cboDateFrom) And Not IsNull(Me.cboDateTo) Then
    strDateFrom = "#" & Format(Me.cboDateFrom, "yyyy-mm-dd") & "#"
    strDateTo = "#" & Format(DateAdd("d", 1, Me.cboDateTo), "mmmm") & "#"
    ' build string expression to filter report
    ' to selected data range
    strCriteria = "PurDate >= " & strDateFrom & " And PurDate < " & strDateTo

    ' open report filtered to selected customer
    DoCmd.OpenReport REPORTNAME, _
    View:=acViewPreview, _
    WhereCondition:=strCriteria
    Else
    MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
    End If

    Exit_Here:
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error"
    Resume Exit_Here
    End Sub



    With the above code I input dates as 19/02/2014 it works (with the Db I am using not referring to this sample 01A).

    Now I made some changes in form where I use Start: Month as January With selecting Year 2014
    And End Month February 2016, I am not getting the result. I think there is an issue of format & need to add Year in the code.
    But I don't know how to do that.

    If someone can fix or help me fix the code I will be greatful.


    For better understanding I have attached Sample 01A. Please open the form DialogACDC

    Sample 01AB.zip
    Last edited by Missinglinq; 02-24-16 at 22:25.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    you don't need any of this code,
    make the QUERY of the report look at these form fields, then just open the report
    DoCmd.OpenReport REPORTNAME

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    ranman 256 is right

    but

    what wrong with your code is

    strDateFrom = "#" & Format(Me.cboDateFrom, "yyyy-mm-dd") & "#"
    strDateTo = "#" & Format(DateAdd("d", 1, Me.cboDateTo), "mmmm") & "#"

    msaccess only like dealing with USA formated Dates SO


    strDateFrom = "#" & Format(Me.cboDateFrom, "mm/dd/yyyy") & "#"
    strDateTo = "#" & Format(DateAdd("d", 1, Me.cboDateTo), "mm/dd/yyyy") & "#"


    so from debuging

    I would put a MSGBOX after the strCriteria = "PurDate >= " & strDateFrom & " And PurDate < " & strDateTo

    msgbox(strCriteria) ' Just to checkdoes it look right
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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