Syntax Error In Code . .

[Evagrius]

Hi, this code is in a combobox. I can't seem to get it to function. I am siply trying to filter the form by the Field [YEAR] for only 2008 and 2009. The field type is TEXT and not date.

Private Sub cboFields_BeforeUpdate(Cancel As Integer)
    Me.FilterOn = False
    If IsNull(Me.cboFields) Then
        Me.FilterOn = False
    Else
        Me.Filter = "[Year] = IN('2009','2010')"
        Me.FilterOn = True
    End If
End Sub

 

[pbaldy]

That looks okay offhand. Try it in the after update event instead of the before. I would only use the before update event for validation.

Though it wouldn't cause an error, the first portion of the code is not necessary. You've already set the property to False.

 

[MSAccessRookie]

Hi, this code is in a combobox. I can't seem to get it to function. I am siply trying to filter the form by the Field [YEAR] for only 2008 and 2009. The field type is TEXT and not date.

Private Sub cboFields_BeforeUpdate(Cancel As Integer)
    Me.FilterOn = False
    If IsNull(Me.cboFields) Then
        Me.FilterOn = False
    Else
[COLOR=#ff0000][B]      Me.Filter = "[Year] = IN('2009','2010')"[/B][/COLOR]
        Me.FilterOn = True
    End If
End Sub

I believe that your SQL Syntax for Me.Filter is incorrect, and that the proper Syntax should be something like the following:

Me.Filter = "[Year] IN ('2009','2010')"

 

[pbaldy]

Oops; missed the "=" in there. That will certainly cause a problem.

 

[MSAccessRookie]

Oops; missed the "=" in there. That will certainly cause a problem.

I was worried that I was about to encounter a new version of the Syntax of the IN() keyword.

 

[Evagrius]

Thanks you both very much!! If it would not be asking too much, since I am filtering for 2009 and 2010, how can I adjust the code so that the records show the 2009s and then the 2010s. Thank you again!

 

[MSAccessRookie]

Thanks you both very much!! If it would not be asking too much, since I am filtering for 2009 and 2010, how can I adjust the code so that the records show the 2009s and then the 2010s. Thank you again!

Looking up the SQL Syntax for the "ORDER BY" statement should point you in the right direction. post back with any questions.

 

[pbaldy]

I was worried that I was about to encounter a new version of the Syntax of the IN() keyword.

What you encountered was the sadly-not-uncommon Paul brain cramp.

 

[MSAccessRookie]

What you encountered was the sadly-not-uncommon Paul brain cramp.

All is forgiven, I am sure. Heaven knows I am not perfect either. In fact if we were all perfect, this forum would not be half as much fun as it is.

 

[Evagrius]

Well, I've tried several option in my effort to learn, but I am just not getting it. The order by is causing my error. . .

Private Sub cboFields_AfterUpdate()
    If IsNull(Me.cboFields) Then
        Me.FilterOn = False
    Else
        Me.Filter = "[Year] IN ('2008','2009') order by [Year]"
        Me.FilterOn = True
    End If
End Sub

 

[SOS]

An order by does NOT go in the filter. It goes in the Order by property:

Me.OrderBy = "[Year]"
Me.OrderByOn = True

they are SEPARATE items.

 

[Evagrius]

SOS - Thank You for that lesson and solving the issue. Thanks . .