Compile error: Argument not optional

[Kaliman]

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

 

[John W. Vinson]

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.

 

[Kaliman]

Hi John

Many thanks for your reply.

Yes I found my mistakes with quotemarks as you said.

On the other hand, following Allen Browne filter form I include this lines
to end the code:



lngLen = Len(strTmp) - 1

If lngLen > 0 Then

strWhere = "XXX IN (" & Left$(strTmp), lngLen) & ") AND "

End If



I don’t know what I have to write instead of the XXX in the third line



Thanks again

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]

lngLen = Len(strTmp) - 1

If lngLen > 0 Then

strWhere = "XXX IN (" & Left$(strTmp), lngLen) & ") AND "

End If



I don’t know what I have to write instead of the XXX in the third line

The name of the field for which you wish to search multiple values. You know
what field you want to search - neither Allen nor I could possibly know that.