Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Question Unanswered: Reports And Date Range

    I have reports which i would like to filter it out by Date Range. To do this i have created form where user can enter "StartDate" and "EndDate" . If user enters only "StartDate" then report will filter out from that Date onwards...If user input only "EndDate" then report will filter upto that date...If both StartDate and EndDate is there then reports will filter out between those two dates. I have wrote the code to do this but still there is problem

    Example: when i enter "02-Feb-05" in StartDate reports still shows "02-Feb-04".

    If Not IsNull(Me.txtStartDate) And IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " And " & strField2 & ">" & Format(Me.txtStartDate, conDateFormat)
    ElseIf Not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " And " & strField2 & "<" & Format(Me.txtEndDate, conDateFormat)
    Else
    strWhere = strWhere & " And " & strField2 & "Between" & Format(Me.txtStartDate, conDateFormat)
    strWhere = strWhere & " And " & strField2 & "Between" & Format(Me.txtEndDate, conDateFormat)
    End If
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    Skharva

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Skharva
    I have reports which i would like to filter it out by Date Range. To do this i have created form where user can enter "StartDate" and "EndDate" . If user enters only "StartDate" then report will filter out from that Date onwards...If user input only "EndDate" then report will filter upto that date...If both StartDate and EndDate is there then reports will filter out between those two dates. I have wrote the code to do this but still there is problem

    Example: when i enter "02-Feb-05" in StartDate reports still shows "02-Feb-04".

    If Not IsNull(Me.txtStartDate) And IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " And " & strField2 & ">" & Format(Me.txtStartDate, conDateFormat)
    ElseIf Not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " And " & strField2 & "<" & Format(Me.txtEndDate, conDateFormat)
    Else
    strWhere = strWhere & " And " & strField2 & "Between" & Format(Me.txtStartDate, conDateFormat)
    strWhere = strWhere & " And " & strField2 & "Between" & Format(Me.txtEndDate, conDateFormat)
    End If
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    Look at your "BETWEEN" construct ... It does not look like this:

    ... AND (SomeDateField BETWEEN #FormattedDateHere# AND #FormattedDateHere#) ...

    Let alone I see spacing issues ... Or lack thereof.

  3. #3
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Unhappy

    Sorry, but i don't knw what you are telling me to change
    Skharva

  4. #4
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Exclamation

    I have modify my code but still same problem. I 'm sure there is some Access Guru who can slove this with in min.........Please Help !

    strReport = "rptOrder"
    strField = "Region"
    strField2 = "ScheduleDate"



    If IsNull(Me.cboRegion) Then
    msg = "Plese Select the Home Visitor Name from the list"
    End If

    If IsNull(Me.txtStartDate) And IsNull(Me.txtEndDate) Then
    msg = msg & vbCrLf & "You must select at least one date"
    End If

    If msg <> "" Then
    MsgBox msg
    Exit Sub
    End If

    If Not IsNull(Me.cboRegion) Then
    strWhere = "[HomeVisitor]= '" & Me![cboRegion] & "'"
    End If

    If IsNull(Me.txtStartDate) Then
    If Not IsNull(Me.txtEndDate) Then 'End date, but no start date.
    strWhere = strField & "<" & Format(Me.txtEndDate, conDateFormat)
    End If
    Else
    If IsNull(Me.txtEndDate) Then
    strWhere = strField & " > " & Format(Me.txtStartDate, conDateFormat)
    Else
    strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
    & " And " & Format(Me.txtEndDate, conDateFormat)
    End If
    End If
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    Skharva

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    whooah!

    is the bound column of cboregion a string...
    add a line:
    If Not IsNull(Me.cboRegion) Then
    msgbox cboRegion,,"This should be a string"
    strWhere = "[HomeVisitor]= '" & Me![cboRegion] & "'"
    End If
    ...just to verify. if not, you need me!cboregion.column(N) where N is the 0-based index of the column containing your string

    next: your 2nd, 3rd, 4th strwhere builds kill the 1st build.
    try something along the lines of
    strwhere = "((1=1) " 'thanks rudy
    if something then
    strwhere = strwhere & "AND (the next bit of your where) "
    endif
    if something then
    strwhere = strwhere & "AND (the next bit of your where) "
    endif
    'etc until you have completed all the iffy bits, then
    strwhere = strwhere & ");"
    msgbox strwhere,,"Is this what you wanted?"

    next: what format are your dates in the table: date or text?
    if text: take two strong coffees and think deeply about fixing your tables.
    if dates: you should not be messing with all this string/format stuff.
    = strwhere & "AND ([dateField] < #" & Forms!theform!thisTextBox & "#) "
    or
    = strwhere & "AND ([dateField] Between #" & Forms!theform!thisTextBox & "# and #" & Forms!theform!thatTextBox & "#) "
    should do it.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oops: you clearly have your two text boxes on Me! so the last few lines above should read...

    = strwhere & "AND ([dateField] < #" & thisTextBox & "#) "
    or
    = strwhere & "AND ([dateField] Between #" & thisTextBox & "# and #" & thatTextBox & "#) "
    should do it.

    if your dates are stored in the table as text, you have a problem (but a fixable problem). you need to beat the [dateAsString] field into date form in the query... since this is going to be painfully slow, you are better off converting the table to store dates as dates.

    without converting the [dateAsString] field into date you are into string comparison, and there you may have the following problem:
    31/1/1960 > 30/1/2004 ' !!! in a string comparison !!!
    with american mm/dd/yy format it's an even bigger mess comparing strings

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Thumbs up

    Thanks izy.........your solution wrks fine
    Skharva

Posting Permissions

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