Filter Listbox by Current Year 1

[deweb]

I've developed a Trainings Database. I have a listbox on a dialog form that lists training titles and training dates. I'd like to filter the listbox to show trainings for only the current year (whatever the current year happens to be). Do I use the DateSerial function in the QBE? If so, what is the syntax? If not, how can I accomplish this?

Thanks.

 

[RoyVidar]

Hi!

Not giving much information to play with;-)

Assuming you use a query as row source for the listbox:

Add another field to it, with a syntax which might look something like this (just using the year function):

[tt]MyFilterField: Year(MyDateField)[/tt]

Substitute MyDateField with your date field.

In the criteria row, something like this:

[tt]Year(Date())[/tt]

To not mess up the listbox, the new fields 'Show' check box should perhaps be unchecked.

HTH Roy-Vidar

 

[deweb]

What a genius -- And with so little to work with.

Thanks so much!

 

[RoyVidar]

Thank you, both for the star, and the very kind words! (did something quite like it just a while ago;-)) Pleasure to assist when the result is positive!

Roy-Vidar

 

[deweb]

This worked great. So well, in fact, now rather than just wanting to filter for the current year my boss wants the ability to filter by specific years. For example, all trainings for 2002 or all trainings for 2003. Is it best to do this using a combo box or a text field? How do I pass the year information to the query that I'm sorting with?

Thanks.

Denise

 

[RoyVidar]

Hi!

Yes, both combo and textcontrol on a form should do. You could try both versions and see what you like best;-)

To reference a control on a form in a query;

[tt]forms!NameOfTheForm!NameOfTheControl[/tt]

If names of control or form contains spaces or special characters, then you'll have to add brackets manually

[tt]forms![Name Of The Form]![Name Of The Control][/tt]

This would provide the values from either a combo or a textcontrol.

If necessary, there's a faq (faq702-4538) on how to add an ALL selection to a combobox.

HTH Roy-Vidar

 

[deweb]

Hi Roy -

You're an angel. I think my problem is that the trainings are listed in mmddyyyy format and I need to be able to enter only the year and filter the trainings. Possible? What's the proper syntax when I'm referring to the year in the combo or text box?

Thanks again.

Denise

 

[RoyVidar]

Hi again!

Where your listbox query now has the Year(Date()) (criteria row for the "new" field created), replace with the appropriate line from my above post, substituting form and control names.

Report back if you don't make it work;-)

HTH Roy-Vidar

 

[deweb]

Great. I'll give it a try. Keep your fingers crossed.

One more question. Should the combo or text control be bound to the MyFilterField field in the query?

Thanks again.

Denise

 

[RoyVidar]

Hi again!

No, I think the textcontrol/combo should be unbound.

Should you get trouble, post back with the excact names of combo/text control and form;-)

Enjoy - Roy-Vidar

 

[deweb]

Okay. Thanks.

 

[deweb]

Well, I don't know what I'm doing wrong, but the information in the listbox disappears when I add the MyFilterField field to the RowSource query. >:-<

The name of my dialog form is &quot;GoToTrainingDialog&quot;, the textbox name I used was &quot;Text20&quot; and the combobox was &quot;cboYear&quot;.

Denise

 

[RoyVidar]

Hi again!

It is possible that the list needs to be requeried after selecting/typing a value, so the line:

[tt]Me!NameOfYourListBox.requery[/tt]

Might be put in either the combos or the text controls after update event.

Else, just repeating and some troubleshooting...

You have a &quot;new field&quot; in your listbox query. It should still look like this (just with your substituted field name) in the field part of the query.

[tt]MyFilterField: Year(MyDateField)[/tt]

The show row for this field should still be unchecked.

In the criteria row, EITHER something like this:

[tt]forms!GoToTrainingDialog!Text20[/tt]

OR

[tt]forms!GoToTrainingDialog!cboYear[/tt]

Then - be sure that either the textcontrol (if that't the one you're using) or the combo contains a valid year (2002)

For instance, use =Year(Date()) as default value for them.

HTH Roy-Vidar

 

[deweb]

You are my hero!!! I got it to work with both the combo and the textbox controls. Now I'm going to try to all &quot;All&quot; to the combo box. Is there a way to do the same type of thing with the textbox? Right now if I leave the text box empty the listbox is empty.

I know you are tired of me by now, but you've been a tremendous help.

Thank you, thank you, thank you.

Denise

 

[RoyVidar]

Hi again!

Should perhaps have suggested it before...

With the textbox, you could use the nz function in the query criteria in stead of just referencing the form. Something like this:

[tt]Like nz(forms!GoToTrainingDialog!Text20,&quot;*&quot[/tt]

With the nz function you may specify a value to apply if the form control does not contain a value. Here &quot;*&quot;, which also needs the &quot;like&quot; keyword.

Roy-Vidar

 

[deweb]

Roy -

This is just what I needed. It works perfectly. Thanks again.

Denise