Help with filters

[daltman1967]

I have the filter format correct, as follows:

Year([Date_Completed]) = Yr.Value

Yr is the name of an unbound text box on the form. On form load, the text box is filled in with the proper value. If I put the filter inside the Form_Load procedure, I get a blank response.



If, however, I put the same filter on the filter line in the Data section for the form - the program will ask for yr's value. If I type the value, the filter works great.

Now what?? I want the filter to work great, but get the value on its own without prompting me. I'm THIS close to an answer, but need help.

[TheShabz]

replace Yr.Value with Me.Yr if the textbox is on the same form and Forms!formName.Yr if it is not. Also, in VB, .Value is required. In VBA, it is implied. Me.Yr and Me.Yr.Value are exactly the same.

[daltman1967]

Me.Yr simply makes the form ask for the value of Yr. This happens no matter where I put it.

[boblarson]

How about posting the entire filter because this:

Year([Date_Completed]) = Yr.Value

as it stands is not correct. You can't set the Year function equal to something. You can use

Yr.Value = Year([Date_Completed])

but I'm sure that isn't what you want. If you want to filter the form by the value that is in the text box named Yr, then you would need to use

Me.Filter = "[Date_Completed] Between " & DateSerial(Me.Yr, 1, 1) & " And " & DateSerial(Me.Yr, 12, 31)
Me.FilterOn = True

but if you say that it keeps asking for Yr even if you use it in the code I just gave you, then something isn't right and perhaps you can post a copy of the database with bogus information so we can see what is happening.

[daltman1967]

Ok, I'm posting a bogus copy of the database. I'm at my wits' end. You can find it at www.anything--everything.com/AWIA.zip. It was too big to post here either as an .accdb or a .zip.

[daltman1967]

For those interested in this thread this was primarily the problem when the filter was being set it was being set with this code:

Me.Filter = "Year([Date_Completed]) = Me.Yr"

which set the actual formula on the FILTER property to:

Year([Date_Completed]) = me.yr

Which Access could not understand. The correct syntax would be

Me.Filter = "Year([Date_Completed]) = '" & Me.Yr & "'"

Which would set the actual formula on the FILTER property to:

Year([Date_Completed]) = '2010'

The alternative was to set up the query like this:

SELECT ... all my fields ...
FROM TableName
WHERE (((DatePart("yyyy",[FieldName]))=[Forms]![FormName]![FieldOnFormName]))
ORDER BY TableName.FieldName;

This takes the part of 'year' from the FieldName - in my case, Date_Completed, and compares it to the value of the field on the form. This field was set with the proper year BEFORE the form was opened - and I think that THAT made all the difference.

The query, fed the proper information, now brings up the list as desired, fully editable. This thread should be marked closed.