Filter Problem

  • Thread starter Thread starter David W
  • Start date Start date
D

David W

I am trying to filter a form by a combobox, the combobox has a date format
of mmm yyyy, I am using the following with no luck

Me.Filter = "[date] = #" & Me!Combo47 & "#"
Me.FilterOn = True

Any ideals of where I went wrong?
 
David said:
I am trying to filter a form by a combobox, the combobox has a date
format of mmm yyyy, I am using the following with no luck

Me.Filter = "[date] = #" & Me!Combo47 & "#"
Me.FilterOn = True

Any ideals of where I went wrong?
Click to expand...

DateTime values in an Access table always have a complete DateTime value.
That means a year, month, day, and time. If you don't supply a day Access
will assume the first and if you don't supply a time Access will assume
midnight.

So...is a test of equality for the first of the month you grab from the
ComboBox with a time of midnight going to find any records in your table?

Remember that formatting only affects *display*, not the value stored.
 
David said:
I am trying to filter a form by a combobox, the combobox has a date format
of mmm yyyy, I am using the following with no luck

Me.Filter = "[date] = #" & Me!Combo47 & "#"
Me.FilterOn = True
Click to expand...


From what you've explained so far, that looks like a legal
filter.

Maybe the issue is that a date include a day of month even
if you don't specify it (the first of the month). So, if
you were expecting to find all dates in the month, that's
not the filter you want to use???
 
I wished I could do programming more often so I dont forget things, but when
you only get maybe 1-2 hours every other day maybe, sometimes you forget,
this is one of them.

What I am wanting to do I thought was to filter the subform by a date to
achieve a filter by months.

The subform has a query as a record source, the date field is of full
lenght. and what I was wanting to end of with is a subform that showed all
records at first then let the users filter out what wasnt desired.
I can get the query to filter out a month by formatting the date, then set
the criteria to look at the combobox to fiqure out what month was selected,
but how do you get the query to show all records in the begining?
 
David said:
I wished I could do programming more often so I dont forget things, but when
you only get maybe 1-2 hours every other day maybe, sometimes you forget,
this is one of them.

What I am wanting to do I thought was to filter the subform by a date to
achieve a filter by months.

The subform has a query as a record source, the date field is of full
lenght. and what I was wanting to end of with is a subform that showed all
records at first then let the users filter out what wasnt desired.
I can get the query to filter out a month by formatting the date, then set
the criteria to look at the combobox to fiqure out what month was selected,
but how do you get the query to show all records in the begining?
Click to expand...


If you're using the form's Filter property, just turn it
off:
Me.FilterON = False

If you're doing something els, please explain.
 
David W wrote:
I am trying to filter a form by a combobox, the combobox has a date format
of mmm yyyy, I am using the following with no luck

Me.Filter = "[date] = #" & Me!Combo47 & "#"
Me.FilterOn = True
Click to expand...
Click to expand...

Try this, after creating ComboYear and ComboMonth string
variables from your combobox result:

Me.Filter = "(Year([date]) = " & ComboYear
& ") And (Month([date]) = " & ComboMonth & ")"
 
Back
Top