Thanks a lot Steve
I really enjoyed the added quips as well as your valued observations. I'll
have a play around with your suggestions and try to figure out a more
logical approach.
Basically, I have a number of different forms dealing with different
competencies - from fire to Advanced Life Support, Intravenous Cannulation
etc.
The Main form gives the user an opportunity to select their department. A
subform then appears with the names of the department's staff and the dates
at which they have completed their competencies. There may be no date
entered at times. Some departments have over 80 staff. I am trying to give
users a simple way of filtering those who need to update any particular
competency (those without a date or over a year old) via command buttons.
It circumvents them needing to go into the advanced filter sort screen which
would confuse a lot of them.
I figured that if I had 1 function which does this I could just call it and
send the field name and form (haven't got to this bit yet) and thus cut down
on the repititious coding.
Thanks Again
Regards Kevin
Kevin,
Have you set a breakpoint in the Function and stepped thru the code to
see what is happening?
If I might also make some observations.....
Private Sub VideoButt_Click()
CompetencyLYDateCheck ("FireVideoDate")
End Sub
In the (above) subroutine VideoButt_Click(), (sounds like what my wife
calls me when I watch TV.... but I digress), you call a function. But a
function is usually used like this:
Private Sub VideoButt_Click()
Me.SomeControlName = CompetencyLYDateCheck ("FireVideoDate")
End Sub
and the function would return a value (like this):
Public Function CompetencyLYDateCheck(CompetencyName As String)
.
' some code that does calculations or logic stuff
.
.
.
CompetencyLYDateCheck = ValueToReturn
End function
Looking at these lines:
Public Function CompetencyLYDateCheck(CompetencyName As String)
.
.
.Filter = "IsNull(CompetencyName) = True or CompetencyName < '" &
strFilterDate & "'"
we know that the variable CompetencyName is a string (text) and that
it's value is "FireVideoDate" because that is what you passed to the
Function.
IsNull(CompetencyName) will ALWAYS be false if you call the Function
like this:
Me.SomeControlName = CompetencyLYDateCheck ("FireVideoDate")
because CompetencyName.value = "FireVideoDate"
The other part of the .Filter is
OR CompetencyName < '" & strFilterDate & "'"
where strFilterDate is a Date converted to a string. So the OR part of
the filter string evaluates to
OR "FireVideoDate" < 'A_Date' ????
The filter string syntax should look like:
.Filter = " stringFieldName = 'SomeString' OR DateFieldName < #SomeDate#"
using your example it would be
.Filter = "stringFieldName = '" & CompetencyName & _
"' OR DateFieldName < '" & strFilterDate & "'"
(that is single-quote double-quote)
Doesn't make sense to me, but it isn't my database; (most of the time
*my* databases don't make sense to me, either so that is nothing new).
I hope I explained this clear enough; had to get the paper and pen to
work thru it.
-----
So, I think the .Filter string needs to be re-thought.
And I would use a Subroutine, not a Function, to set the filter.
HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kevin said:
Thanks Nikos,
I tried your suggestion, but my real problem is that I am trying to use
the
variable called CompetencyName within the .Filter statement so that I can
use the same code for Extinguisher and Evacuation training and other
competencies as I do for FireVideo. It still keeps asking me for a
parameter
value for CompetencyName even after I tried your code and replaced
FireVideoDate with CompetencyName as below: My apologies that I wasn't
clearer in my previous explanatio and thankyou again for your help.
Regards Kevin
Private Sub VideoButt_Click()
CompetencyLYDateCheck ("FireVideoDate")
End Sub
Public Function CompetencyLYDateCheck(CompetencyName As String)
Dim LYDate As Date
Dim strFilterDate As String
LYDate = Date - 365
With Me![Firesafety2002 subform].Form 'refer to the form object of the
SubForm
strFilterDate = Format$(LYDate, "\#mm\/dd\/yyyy#")
.Filter = "IsNull(CompetencyName) = True or CompetencyName < '" &
strFilterDate & "'"
.FilterOn = True
End With
End Function
Kevin,
Try changing to:
.Filter = "IsNull(FireVideoDate) = True or _
FireVideoDate < '" & strFilterDate & "'"
(IsNull function syntax, date in quotes).
HTH,
Nikos
-----Original Message-----
Hello Everybody,
I can't seem to crack the right syntax for a function I'm
creating to filter
a subform as below.
It works perfectly if I replace CompetencyName with
FireVideoDate in the
..Filter line as in
.Filter = "FireVideoDate Is Null or FireVideoDate
< " &
strFilterDate
FireVideoDate is a textbox on the subform FireSafety2002
subform
I have tried declaring CompetencyName as an Object,
Control and Textbox and
dropping the " on "FireVideoDate" and setting it as
CompetencyLYDateCheck (Me![Firesafety2002 subform].Form!
FireVideoDate)
As you can see, I really don't have much idea of how to
do this.
If anyone knows where I am going wrong, I'd really
appreciate some pointers.
Thanks very much.
Regards
Kevin Vaughan
Private Sub VideoButt_Click()
CompetencyLYDateCheck ("FireVideoDate")
End Sub
Public Function CompetencyLYDateCheck(CompetencyName As
String)
Dim LYDate As Date
Dim strFilterDate As String
LYDate = Date - 365
With Me![FireSafety2002 subform].Form 'refer to the
form object of the
SubForm
strFilterDate = Format$(LYDate, "\#mm\/dd\/yyyy#")
.Filter = "CompetencyName Is Null or
CompetencyName < " &
strFilterDate
.FilterOn = True
End With
End Function
.
