Form search BETWEEN

[NightSpy2]

Hi there, I'm relatively new to Access and I have a problem which I can't seem to figure out!

I have created a form for a table which contains ~600 movies and their name, genre, rating, director, year it was made, and length (min).
Here's what my form looks like:
imgur .com/Mwu7qV4

I need to be able to enter numbers into the two Year boxes, and then it filters the movies in my database and only shows me records from between those two years. The years in my database are just in one column in the format of: XXXX e.g. 1996 etc

I've tried the code:

Private Sub Year2_AfterUpdate()

Me.Filter = "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2
Me.Filteron = True
Debug.Print "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2

End Sub

Year1 and Year2 are the boxes Year: and To: respectively. [Year] just being the column name which contains all my years.
When I try to run my query it shows me my records, but it shows me all of them! It doesn't filter it at all!

Any help would be very much appreciated!

 

[John Big Booty]

Check this link for various criteria requirements, your code should probably look something like;

Private Sub Year2_AfterUpdate()

Me.Filter = "[Year] BETWEEN #" & Me.Year1 & "# AND #" & Me.Year2 & "#"
Me.Filteron = True
Debug.Print "[Year] BETWEEN #" & Me.Year1 & "# AND #" & Me.Year2 & "#"

End Sub

Assuming we are dealing with dates

 

[NightSpy2]

No it's just the year. So for example the Column [Year] is like this: 1990, 2005, 1987, 2012 etc, so I don't think there should be any need for some kind of date-format thing. It's just a plain number.

I've tried pretty much everything, but nothing works. I just did it with that code and I got this (Error Form attachment).

OK. I just found out that Year is a reserved word... I'll try changing my column name and see what happens.

 

[NightSpy2]

I used the code:

Private Sub RunQuery_Click()

Me.Filter = "[MovieYear] BETWEEN " & "*" & Me.MovieYear1 & "*" & " AND " & "*" & Me.MovieYear2 & "*"
Me.FilterOn = True
Debug.Print "[MovieYear] BETWEEN " & "*" & Me.MovieYear1 & "*" & " AND " & "*" & Me.MovieYear2 & "*"

End Sub

Ok, I thought it worked, but I was either hallucinating, or it only worked for 1 time... :/
Anything else I might need to do in order for this to work?

 

[NightSpy2]

Ok well I've figured it does what I need it to do, if I just put this into the Criteria in the query:

Between [Forms]![SearchForm]![MovieYear1] And [Forms]![SearchForm]![MovieYear2]

But, I need to make it so that it also works if I don't put anything into those boxes (as in, it shows all records). But if I add in wild card "*" then it just screws up and says: 'This expression is typed incorrectly, or is too complex to be evaluated.'
How do I fix it?