Filter Using ComboBox For Date Criteria

[burrina]

I have a ComboBox named FilterByDate and it is Unbound.
RowSource is set to "Today";"This Week";"Last Week";"This Month";"Last Month";"This Year";"Last Year";"All"
Event Procedure is set to be On Change,, is this correct? Or else ???


I am starting my vb code like this;



Me.Filter = "OrderDate" = & Me.FilterByDate
DoCmd.RunCommand acCmdApplyFilterSort

I now want to add my date criteria and then move on the next else statement or filter!

My code for the First Date Criteria is "Today"
= (Year([OrderDate[) = Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate]) = Day(Date())

So, my question is, how do I put this together so that it runs when I choose my option form the list?
I then want to be able to move on to the next criteria if chosen which is "This Week" and so one!

Thanks in Advance,

[pbaldy]

That seems unnecessarily complex. Why not just

OrderDate = Date()

By the way, I'd use the after update event. The change event fires with every keystroke. If actually a date field, it would be:

Me.Filter = "OrderDate = #" & Me.FilterByDate & "#"

[burrina]

Are you saying this code will run whatever selection I enter in the combobox? Today,This Week, etc...???

I need to be able to filter the selection by the List as noted above.

[pbaldy]

This:

OrderDate = Date()

was an alternative to this:

(Year([OrderDate[) = Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate]) = Day(Date())

This:

Me.Filter = "OrderDate = #" & Me.FilterByDate & "#"

was a correction of this:

Me.Filter = "OrderDate" = & Me.FilterByDate

because date values require the # delimiters.

[burrina]

Me.Filter = "[OrderDate] Like '*" & [OrderDate] & "*'"
Me.FilterOn = True


I tried this, but it is not working.My form name is OrderDetails and the combobox is named Filter. The key date on the form is OrderDate which is what I want t filter by. It is on the after update event of the form. I was going to use the Date Picker thing, but maybe not. What is a Date error occurs? Do I not need error code for this as well? Criteria is in place to make sure you have a CustomerID and so on.

Error message is , you cant save this record right now.

[pbaldy]

Is OrderDate a text field or date/time? You're treating it like text.

[burrina]

It is a Date Field.

[orange]

Paul gave you the correct syntax for assigning a date value to a variable of date/time data type in post #4

This:

Me.Filter = "OrderDate = #" & Me.FilterByDate & "#"

was a correction of this:

Me.Filter = "OrderDate" = & Me.FilterByDate

because date values require the # delimiters.

[burrina]

Explain to me how that is going to show me Today,This Week,Last Week,This Month,Last Month, etc... ???

[orange]

What exactly do you think will happen when you assign
OrderDate = Last Month??? or Next Month???

Do you not think you would have to have some construct to assign an actual Date or Between Date1 and Date2?

Last Month may be equivalent to
OrderDate Between FirstOfLastMonth and EndOfLastMonth

But I think you would agree if you are building a filter , or an SQL where clause, you have to have the proper units and syntax.

When we see a Datefield (Orderdate), you have to be dealing in Dates , not a text string "Last Month".
I guess you should tell us what is in your "list".

Like is not going to help you either.
You may have to use some combinations of DateAdd, DateDiff, Between AND....

[burrina]

I am using this code now, probably not the best way, but I don't know of any other way. I set my combo to unbound,value list to "Today";"This Week", etc...
Then on the After Update Event I run this code. Not tested yet.

If Forms![OrderDetails]![OrderDate]![Filter] = "Today" Then
OrderDate = (Year([OrderDate]) = Year(Date) And Month([OrderDate]) = Month(Date) And Day([OrderDate]) = Day(Date))
End If

[pbaldy]

There's nothing wrong with your concept, though I'd use Select/Case given the number of options. I suppose you could create a second column of the combo with the filter string in it.

[orange]

You may also want to review DateSerial, DateAdd , DateValue functions.

See Date Functions at http://www.techonthenet.com/access/functions/index.php

This code

If Forms![OrderDetails]![OrderDate]![Filter] = "Today" Then
OrderDate = (Year([OrderDate]) = Year(Date) And Month([OrderDate]) = Month(Date) And Day([OrderDate]) = Day(Date))
End If

Could be

If Forms![OrderDetails]![OrderDate]![Filter] = "Today" Then
OrderDate = Date
End If

As I said, you'll have to get the values for NextMonth etc into a Date datatype or some construct that can be used with Dates.

Good luck with your project