Re Syntax error with .Filter using a textbox or string

[Kevin Vaughan]

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

 

[MacDermott]

I think you need to build your Filter like this:

..Filter = "IsNull(" & CompetencyName & ") = True or " & CompetencyName & "<
'" &

strFilterDate & "'"

HTH
- Turtle

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