Form for dynamic report - need help with coding a Date feature

[Jeffshex]

I have code that works already, with the exception of a date range. In the
database there is a field named BarDate that I want to run a query on. My
current form has code where if a field is blank on the form when you hit the
Apply Filter button, it uses the * to grab all of the info. I need help
implemeting the date part, which i cannot figure out. I'd like to have it
where if the leave the StartDate and
EndDate blank it shows everything, otherwise if those fields are filled in,
I'd like the query to insert the Between & And clause.
Any Ideas???
Here is the current code that works perfect...minus the date part

Private Sub cmdApplyFilter_Click()
' Declare variables for query string
Dim strServer As String
Dim strAdmin As String
Dim strPriority As String
Dim strRequestType As String
Dim dtmStartDate As Date
Dim dtmEndDate As Date
Dim strRequestName As String
Dim strFilter As String

' These IF statements tell the query to show all records if the selection is
left blank.
If IsNull(Me.cboServerName.Value) Then
strServer = "Like '*'"
Else
strServer = "='" & Me.cboServerName.Value & "'"
End If
If IsNull(Me.cboSysAdminName.Value) Then
strAdmin = "Like '*'"
Else
strAdmin = "='" & Me.cboSysAdminName.Value & "'"
End If
If IsNull(Me.cboPriority.Value) Then
strPriority = "Like '*'"
Else
strPriority = "='" & Me.cboPriority.Value & "'"
End If
If IsNull(Me.cboRequestType.Value) Then
strRequestType = "Like '*'"
Else
strRequestType = "='" & Me.cboRequestType.Value & "'"
End If
If IsNull(Me.cboRequestName.Value) Then
strRequestName = "Like '*'"
Else
strRequestName = "='" & Me.cboRequestName.Value & "'"
End If

strFilter = "[ServerName] " & strServer & " AND [RequestName] " &
strRequestName & " And [RequestType] " & strRequestType & " AND
[SysAdminName] " & strAdmin & " AND [Priority] " & strPriority & " "

With Reports![rptBAR]
.Filter = strFilter
.FilterOn = True
End With

End Sub

 

[Albert D.Kallal]

I have code that works already, with the exception of a date range. In the
database there is a field named BarDate that I want to run a query on. My
current form has code where if a field is blank on the form when you hit
the
Apply Filter button, it uses the * to grab all of the info. I need help
implemeting the date part, which i cannot figure out. I'd like to have it
where if the leave the StartDate and
EndDate blank it shows everything, otherwise if those fields are filled
in,
I'd like the query to insert the Between & And clause.
Any Ideas???

Try:

dim strWhere as string
dim strSql as string

If IsNull(Me.cboServerName) = false Then
strWhere = (ServerName = '" & Me.cboServerName & "')"
gosub addWhere
end if

If IsNull(Me.cboSysAdminName) = false Then
strWhere = "(SysAdminName "='" & Me.cboSysAdminName & "')"
gosub addWhere
end if

If IsNull(Me.cboPriority) = false Then
strWhere = "(Priority ='" & Me.cboPriority & "')"
gosub addWhere
end if

If IsNull(Me.cboRequestType) = false Then
strWhere = "(RequestType = '" & Me.cboRequestType & "')"
gosub addWhere
End If

If IsNull(Me.cboRequestName) = false Then
strWhere = "(RequestName = '" & Me.cboRequestName & "')"
gosub addWhere
end if

' dates

If IsNull(Me.StartDate) = false Then
if isNull(me.EndDate) = false then
strWhere = "(BarDate between #" & format(me.StartDate,"mm/dd/yyyy") &
"#" & _
"and #" & format(me.EndDate,"mm/dd/yyyy") & "#)"
gosub addWhere
end if
end if


With Reports![rptBAR]
.Filter = strSQL
.FilterOn = True
End With

exit sub

addWhere:

if strSQL <> "" then
strSQL = strSQL & " and "
end if
strSQL = strSQL & strWhere
return

End Sub

You will find the above approach much better, as you an continue add (cut
paste if you want) more conditions with a lot less code....

I use the above all the time..and thus make report prompt screens that look
like:
http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

 

[Jeffshex]

Sweet!
Just had to make a few changes and it works like a champ!
How does this exactly build the criteria...it works great, but could you
give me some insight as how it builds?

Thanks!!!!
Jeff

I have code that works already, with the exception of a date range. In the
database there is a field named BarDate that I want to run a query on. My
current form has code where if a field is blank on the form when you hit
the
Apply Filter button, it uses the * to grab all of the info. I need help
implemeting the date part, which i cannot figure out. I'd like to have it
where if the leave the StartDate and
EndDate blank it shows everything, otherwise if those fields are filled
in,
I'd like the query to insert the Between & And clause.
Any Ideas???

Try:

dim strWhere as string
dim strSql as string

If IsNull(Me.cboServerName) = false Then
strWhere = (ServerName = '" & Me.cboServerName & "')"
gosub addWhere
end if

If IsNull(Me.cboSysAdminName) = false Then
strWhere = "(SysAdminName "='" & Me.cboSysAdminName & "')"
gosub addWhere
end if

If IsNull(Me.cboPriority) = false Then
strWhere = "(Priority ='" & Me.cboPriority & "')"
gosub addWhere
end if

If IsNull(Me.cboRequestType) = false Then
strWhere = "(RequestType = '" & Me.cboRequestType & "')"
gosub addWhere
End If

If IsNull(Me.cboRequestName) = false Then
strWhere = "(RequestName = '" & Me.cboRequestName & "')"
gosub addWhere
end if

' dates

If IsNull(Me.StartDate) = false Then
if isNull(me.EndDate) = false then
strWhere = "(BarDate between #" & format(me.StartDate,"mm/dd/yyyy") &
"#" & _
"and #" & format(me.EndDate,"mm/dd/yyyy") & "#)"
gosub addWhere
end if
end if


With Reports![rptBAR]
.Filter = strSQL
.FilterOn = True
End With

exit sub

addWhere:

if strSQL <> "" then
strSQL = strSQL & " and "
end if
strSQL = strSQL & strWhere
return

End Sub

You will find the above approach much better, as you an continue add (cut
paste if you want) more conditions with a lot less code....

I use the above all the time..and thus make report prompt screens that look
like:
http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D.Kallal]

Sweet!
Just had to make a few changes and it works like a champ!
How does this exactly build the criteria...it works great, but could you
give me some insight as how it builds?

It works exaclty the same way your oringal code works. We simply build up a
string that is the resuling filter.

dim strtest as string

strTest = "hello"

msgbox strTest
messbox is = Hello

strTest = strTest & " how are you"

msgbox strTest
msssbox is = Hello how are you

So, you can just "string" (pun intended) togher each part of the filter
string.


we got somting called strSql:

strWhere = ("City = 'edmonton'")

gosub addWhere

now, strSql = "(City = 'Edmonton'")

strWhere = "(SalesRep = 'Albert'")
gosub addWhere

now strSql = "(City = 'Edmonton') and (SalesRep = 'Albert')"

So, each time we call gosub addWhere, we are simply adding on the next
filter part. We build this up by EACH clause into that string. And, if you
leave the contorl bank, then we don't bother to "set", or add the certiea
(no need to add the salesrep to the condstions if it is blank). I mean, if I
leave out sales rep aobve, then we just filter by the city...

If you want to see how this works step by step, then just add the follwing
line of code (to be removed later asfter testing)

addWhere:

if strSQL <> "" then
strSQL = strSQL & " and "
end if
strSQL = strSQL & strWhere

msgbox "new add condistion = " & strWhere & vbcrlf & _
"result = " & strSQL


return

So, just add the above and then run the code, you will easlity see how this
works