Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: record update using between and

    Hope someone can shine somelight onto my Update statement. First 'submissiondate' is a Date type. My Private sub returns ALL records no matter what dates I input. Can anyone tell me what is wrong with my Update statement. Thanks

    ************************************************** ***


    Private Sub cmdSubmissionDate_Click()

    Dim LN As Variant
    LN = InputBox("Enter Start Date: mm/dd/yyyy")
    Dim LN2 As Variant
    LN2 = InputBox("Enter End Date: mm/dd/yyyy")


    'Do nothing if user provide no input
    If LN = "" Or LN2 = "" Then
    MsgBox ("Problem in input value. Please try again!")
    Exit Sub
    End If

    'Check if the End Date is younger then the Start Date

    If CDate(LN2) < CDate(LN) Then
    MsgBox ("The start day is older then the end day. Please re-enter!")
    Exit Sub
    End If


    'Set all record's print report field = false
    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = False;")

    'Find our all the Revise code{s} for this Change Control Form
    Dim dbRevise As Database
    Dim rsRevise As DAO.Recordset
    Dim strRecCount As Integer
    Set dbRevise = CurrentDb()

    'Open the tblChangeControlFormDetails table
    Set rsRevise = dbRevise.OpenRecordset("Select * FROM tblChangeControlFormDetails ")

    rsRevise.MoveFirst

    If Not rsRevise.EOF Then

    Do

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE 'SubmissionDate (between CDate(LN) and CDate(LN2))';")

    rsRevise.MoveNext
    Loop While rsRevise.EOF = False

    End If

    rsRevise.Close

    ' release the memory back to the system

    Set rsRevise = Nothing


    'Check if there is any matching report

    strRecCount = DCount("*", "tblChangeControlFormDetails", "PrintReport = True")

    If strRecCount = 0 Then
    MsgBox ("no match")
    Exit Sub
    End If

    'Minimize the Search form
    DoCmd.Minimize

    Dim stDocName As String

    stDocName = "rptChangeControlForm"
    DoCmd.OpenReport stDocName, acPreview


    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The quotes make the whole thing a literal string. You also need to concatenate the variables. Try this:


    CurrentDb.Execute "UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE SubmissionDate between #" & CDate(LN) "# and #" & CDate(LN2)) & "#"
    Paul

  3. #3
    Join Date
    Sep 2007
    Posts
    148
    Thank you Paul,

    Will try your suggestion first thing tomorrow morning. It is 12:45 AM my time. Time for bed.

    Thanks

  4. #4
    Join Date
    Sep 2007
    Posts
    148
    Paul, it works, thank you

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE SubmissionDate between #" & CDate(LN) & "# and #" & CDate(LN2) & "# ;")
    Last edited by sweetmail; 04-30-08 at 20:40.

Posting Permissions

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