Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    99

    Unanswered: Opening form between date

    Not sure on the vba command to open a form between dates below is what I have so far the 'docmd.openform "fiil" works and filters the results just for the start date but cannot get the between dates to work

    No error pops up just does not filter the form which is in dataview

    Any ideas

    Private Sub Command157_Click()
    If IsNull(Me.Startdate) = True Or IsNull(Me.Enddate) = True Or Startdate = "" Or Enddate = "" Then
    MsgBox "Start AND End dates are required" '
    Exit Sub '
    ElseIf Startdate > Enddate Then
    MsgBox "Start Date cannot be later than End Date"
    Exit Sub '
    Else
    'DoCmd.Openform "FIIL", acFormDS, , "[incident date] = #" & Me.Startdate & "#"

    DoCmd.Openform "FIIL", acFormDS, , "[Incident Date] Between #" & Me.Startdate & "# And #" & Me.Enddate & "#"
    End If
    End Sub]

    Thank you

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access is US centric and expects dates in US format, its also ISO compliant (in this area) so you could use ISO date format
    Code:
    DoCmd.Openform "FIIL", acFormDS, , "[Incident Date] Between #" & format(Me.Startdate,"YYYY/MM/DD") & "# And #" & format(Me.Enddate,"YYYY/MM/DD") & "#"
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2013
    Posts
    99
    Thank you Healdem

    With the reording of the date format for the UK works a treat

    Can I ask I understand the format function but what does the enclosing the me. in a ()

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    format is a function
    it expects two parameters
    the first being the value or variable to be formatted
    the second being the format rule to be applied
    eg
    formattedvalue = format(value,formatmask)
    to find the full SP on the format function follow the link
    if you are in any doubts then use the help function within Access. it used to be much better (select the function, right click to pull up the help) but its still a mine of information
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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