Filter Date

  • Thread starter Thread starter Asif
  • Start date Start date
A

Asif

Hi, I have a form which contains a listbox (holding the values Process
Date and Quantity) and I have a textbox which displayed the current
month. What I want to do is when I click on a command button
(cmdMonthDown) and the textbox value changes from May to April I want
the values in the listbox to be filtered so that only those values are
disiplayed that were entered in April.

If I'm not making sense please let me know otherwise your help would
be appreciated.

Thanks
 
Reference the text box as a parameter in the list box's RowSourcee, e.g. if
the text box shows the month name in full:

SELECT [Process Date], [Quantity]
FROM [YourTableOrQuery]
WHERE FORMAT([Process Date],"mmmm")
= Forms!YourForm!YourTextBox
ORDER BY [Process Date] DESC;

In the code behind your button add a line to requery the list box after the
code which changes the value of the text box, e.g.

YourListBox.Requery

This will show dates from the same month in more than one year of course if
the data covers several years.

Ken Sheridan
Stafford, England
 
Thanks for your reply Ken, I've tried your suggestion but I'm not
getting the result I'm after. Hence what I've managed to do is in the
query behind the listbox I've added an expression in a new field

Expr1: Format([tbl_PROCESS.ProcessDate],"mmmm")

and as a criteria below that field I've added

[Enter Month]

Hence what happens now is when the form opens an input dialog box is
displayed asking for a month, and if I enter April for example then
all the records for April appear.

As mentioned in my previous post I already have a textbox in the form
displaying the current month, what I'm thinking is instead of
specifying the critera in the query how do I refer the critera to
whats the textbox in the form?


Thanks
 
How are you assigning the current month name to the text box when opening the
form? If you are doing this in code in, for instance, the form's Load event
procedure, then requery the list box after assigning the value, e.g.

Me.YourTextBox = Format(VBA.Date,"mmmm")
Me.YourListBox.Requery

Referencing the text box as a parameter in the list box's RowSource in the
way I described, requerying it again when you change the month, should work.

Ken Sheridan
Stafford, England
 
Back
Top