Prev: Absolute Field Validation
Next: Subform changes to totally different subform upon save!
From: Kaliman on 13 Apr 2010 18:52
Hi

In continues form in Access 2003 I'm trying to apply a filter using txtboxes
and checkboxes. Here is the code I have on a command button
(cmdFilter).Nevertheless, when I fill the txboxes and select one or several
checkboxes and I click the cmdFIlter vba editor displays this message:
Compile error: Argument not optional and and ([Year] is blue coloured:



I don't know how to fix this error and I don't know if the rest of code is
correct. I
used samples of code from this forum, but unfortunately I'm still a
beginner in vba and I'm sure I do mistakes. Could you suggest me how to fix
the error and help me with the rest of the code?



Option Compare Database

Option Explicit

Private Sub cmdFilter_Click()

Dim strWhere As String

Dim lngLen As Long

Dim strTmp As String



If Not IsNull(Me.txtOOID) Then

strWhere = strWhere & "([ObraID] = """ & Me.txtOOID & """) AND "

End If



If IsNull(Me.txtStartYear) And IsNull(Me.txtEndYear) Then

'do nothing

ElseIf IsNull(Me.txtEndYear) Then

strWhere = strWhere & "([Year] = """ & Me.txtStartYear & """) AND "

Else

strWhere = strWhere & ([Year] >= """ & me.txtStartYear & """ And "" & [Year]
<= """ & me.txtEndYear & """) And ""

End If



If IsNull(Me.txtAUNB) Then

strWhere = strWhere & "([AUNB] Like ""*" & Me. txtAUNB & """) AND "

End If



If Not IsNull(Me.txtTTO) Then

strWhere = strWhere & "([TTO] Like """ & Me. txtTTO & """) AND "

End If



'The next code is to to filter with check box controls



If Me.chkBDV.Value Then

strTmp = "'BDV',"

End If

If Me.chkBES.Value Then

strTmp = "'BES',"

End If

If Me.chkCON.Value Then

strTmp = "'CON',"

End If



If Me.chkParticipacion.Value Then

strTmp = "'Participacion',"

End If



If Me.chkSector.Value Then

strTmp = "'Sector',"

End If



If Me.chkTerritorio.Value Then

strTmp = "'Territorio',"

End If



If Me.chkSinFaceta.Value Then

strTmp = "'SinFaceta',"

End If



If Me.chkCUL.Value Then

strTmp = "'CUL'"

End If



If Me.chkGEO.Value Then

strTmp = "'GEO'"

End If



If Me.chkPOL_ADM.Value Then

strTmp = "'POL_ADM'"

End If



'With this code I want to filter Keywords



Dim aKW() as string, intKW as integer

Dim strKWCriteria as string, strDelimiter as string



'set the value of the "includes all" radio button to 0

strDelimiter = iif(me.ogKW = 0, " AND ", " Or ")



aKW = Split(strKeyWords, " ")

For intKW = lbound(aKW) to ubound(aKW)

strKWCriteria = strKWCriteria & strDelimiter _

& "([TextField] Like ""*" & aKW(intKW) &

"*"" )"

Next

'strip the leading delimiter from the string

strKWCriteria = Mid(strKWCriteria, LEN(strDelimiter) + 1)



'Wrap in quotes and add to strWHERE

strWHERE = strWHERE & "(" & strKWCriteria & ") AND"

END IF



End Sub
From: John W. Vinson on 13 Apr 2010 19:38
On Tue, 13 Apr 2010 15:52:02 -0700, Kaliman
<Kaliman(a)discussions.microsoft.com> wrote:

>In continues form in Access 2003 I�m trying to apply a filter using txtboxes
>and checkboxes. Here is the code I have on a command button
>(cmdFilter).Nevertheless, when I fill the txboxes and select one or several
>checkboxes and I click the cmdFIlter vba editor displays this message:
>Compile error: Argument not optional and and ([Year] is blue coloured:

I think you may have some misplaced quotemarks. The nature of the error,
though, suggests that you're feeling the effects of using a reserved word
(Year) as a fieldname. Year() is a builtin Access function, and I think it's
interpreting what you consider a fieldname as a call to the function - and the
Year() function requires a date/time value as an argument.

Which line of your code is being flagged as an error? I did find one that
looks wrong:

strWhere = strWhere & ([Year] >= """ & me.txtStartYear & """ And "" & [Year]
<= """ & me.txtEndYear & """) And ""

should probably be

strWhere = strWhere & "([Year] >= " & me.txtStartYear & " And [Year]
<= " & me.txtEndYear) And "

assuming that Year is a Number type field and txtStartYear contains a number
value. As you've written it it's not going to treat [Year] as a fieldname,
since it's not in quotes.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Absolute Field Validation
Next: Subform changes to totally different subform upon save!