Between Dates is not working

  • Thread starter Thread starter gg
  • Start date Start date
G

gg

Hi group,
I have a form with two date boxes-txtFrom and txtTo. When
a user leaves them blank the report displayes all recirds,
when a user enters only starting date (txtFrom) all
records afer the date are displayed, when a user enters
only ending date(txtTo) only the records prior to this
date are displayed.

Problem: When a user enters starting date and ending date
(between txtFrom and txtTo) my form again displayes all
records. Please help me fix this issue:


Private Sub cmdRun_Click()


Dim strWhere As String

If IsNull(Me!txtFrom) = False Then

strWhere = "DATAFIELD >= #" & Format(Me!
txtFrom, "mm/dd/yyyy") & "#"

End If

If IsNull(Me!txtTo) = False Then

If strWhere <> "" Then
strWhere = strWhere & " and "
End If

strWhere = "DATAFIELD <= #" & Format(Me!
txtTo, "mm/dd/yyyy") & "#"

End If

DoCmd.OpenReport "rptComments", acViewPreview, , strWhere
End Sub
 
What I do is to edit the SQL statement that drives the
report before you open up the report using DAO and the
QueryDef.SQL.
 
It does not look like your function would ever look between dates. You need
to make your if statements more specific. You should have the following....

If start is blank AND end is not
If End is blank AND start is not
If both are blank
If non are blank


Rick B


Hi group,
I have a form with two date boxes-txtFrom and txtTo. When
a user leaves them blank the report displayes all recirds,
when a user enters only starting date (txtFrom) all
records afer the date are displayed, when a user enters
only ending date(txtTo) only the records prior to this
date are displayed.

Problem: When a user enters starting date and ending date
(between txtFrom and txtTo) my form again displayes all
records. Please help me fix this issue:


Private Sub cmdRun_Click()


Dim strWhere As String

If IsNull(Me!txtFrom) = False Then

strWhere = "DATAFIELD >= #" & Format(Me!
txtFrom, "mm/dd/yyyy") & "#"

End If

If IsNull(Me!txtTo) = False Then

If strWhere <> "" Then
strWhere = strWhere & " and "
End If

strWhere = "DATAFIELD <= #" & Format(Me!
txtTo, "mm/dd/yyyy") & "#"

End If

DoCmd.OpenReport "rptComments", acViewPreview, , strWhere
End Sub
 
After the nested if statement, you need to append the second condition to
whatever is in strWhere (either empty or first condition with the " AND "
already appended). You had

strWhere = "DATAFIELD <= #" & Format(Me!txtTo, "mm/dd/yyyy") & "#"

I think it should be:

strWhere = strWhere & "DATAFIELD <= #" & Format(Me!txtTo, "mm/dd/yyyy") &
"#"
 
try

Private Sub cmdRun_Click()

Dim strWhere As String

If Not IsNull(Me!txtFrom) And _
Not IsNull(Me!txtTo) Then
strWhere = "DATAFIELD Between #" _
& Format(Me!txtFrom, "mm/dd/yyyy") _
& "# And " & Format(Me!txtTo, "mm/dd/yyyy") _
& "#"
ElseIf Not IsNull(Me!txtFrom) Then
strWhere = "DATAFIELD >= #" _
& Format(Me!txtFrom, "mm/dd/yyyy") & "#"
ElseIf Not IsNull(Me!txtTo) Then
strWhere = "DATAFIELD <= #" _
& Format(Me!txtTo, "mm/dd/yyyy") & "#"
Else
MsgBox "Please enter at least one date, please."
Exit Sub
End If

DoCmd.OpenReport "rptComments", _
acViewPreview, , strWhere

End Sub

hth
 
Thank you very much Sandra,

It works perfectly now.

galin
-----Original Message-----
After the nested if statement, you need to append the second condition to
whatever is in strWhere (either empty or first condition with the " AND "
already appended). You had

strWhere = "DATAFIELD <= #" & Format(Me! txtTo, "mm/dd/yyyy") & "#"

I think it should be:

strWhere = strWhere & "DATAFIELD <= #" & Format(Me! txtTo, "mm/dd/yyyy") &
"#"

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi group,
I have a form with two date boxes-txtFrom and txtTo. When
a user leaves them blank the report displayes all recirds,
when a user enters only starting date (txtFrom) all
records afer the date are displayed, when a user enters
only ending date(txtTo) only the records prior to this
date are displayed.

Problem: When a user enters starting date and ending date
(between txtFrom and txtTo) my form again displayes all
records. Please help me fix this issue:


Private Sub cmdRun_Click()


Dim strWhere As String

If IsNull(Me!txtFrom) = False Then

strWhere = "DATAFIELD >= #" & Format(Me!
txtFrom, "mm/dd/yyyy") & "#"

End If

If IsNull(Me!txtTo) = False Then

If strWhere <> "" Then
strWhere = strWhere & " and "
End If

strWhere = "DATAFIELD <= #" & Format(Me!
txtTo, "mm/dd/yyyy") & "#"

End If

DoCmd.OpenReport "rptComments", acViewPreview, , strWhere
End Sub
Click to expand...


.
Click to expand...
 
Back
Top