filter by date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that the user needs to have filtered on certain criteria.

I can set the filter for the 'ident' field, but when I try adding the date
filter I get a 'type mismatch' error. I've tried different ways of applying
the filter, without success, and now I am asking if somebody could give me a
nudge in the right direction.

The code I have is:

Me.Filter = "ident = 'AP' OR 'SI'" And "invoice_date >= show_start_date"
And "invoice_date <= show_end_date"

The show_start_date and show_end_date fields are unbound.

Thanks
 
Me.Filter = "ident IN('AP','SI') And invoice_date >= " & _
Format(show_start_date, "\#mm\/dd\/yyyy\#") & _
" And invoice_date <= " & _
Format(show_end_date, "\#mm\/dd\/yyyy\#")

Note that I've corrected your condition for indent (you need to use IN like
I did, or else repeat the field name, as in ident = 'AP' OR ident = 'SI'),
I've included the AND operators inside the string, I've moved the variables
show_start_date and show_end_date outside of the string (since you want
their values, not their names), and I've formatted the dates to ensure
they're delimited with # characters and that they'll be in mm/dd/yyyy
format, regardless of what your ShortDate format has been set to in Regional
Settings. (Access will not work correctly with dd/mm/yyyy format in SQL
statements for the first 12 days of each month)
 
Try this:
Me.Filter = "(ident = 'AP' Or 'SI') And (#invoice_date# between
#show_start_date# And #show_end_date#)"

You need to put # before and after a date to have it work in filters.

The syntax may change a bit depending of the nature of invoice_date,
show_start_date & show_end_date

Yanick
 
Back
Top