applyfilter on a date field

[laffeg]

this is driving me nuts !

I have a form based on quite a complicated query

I have a button to filter the results to show only records where next contact date = todays date ( next contact is a calculated field )

the button has code

DoCmd.ApplyFilter , "[next contact]=" & Date

i have tried every combination of syntax i can think of but all i get returned is no records when i know there are some records where next contact = todays date

any ideas ?

 

[bob fitz]

Try:
DoCmd.ApplyFilter , "[next contact]=#" & Date & "#"

 

[laffeg]

nope - still get no records returned

thanks for the suggestion though

 

[bob fitz]

How about:
DoCmd.ApplyFilter , "[next contact] >=#" & Date & "# and #" & Date + 1 & "#"

 

[laffeg]

thanks for the suggestion

shouldnt there be a < in there somewhere ??

I assume you are trying to do a between statement ?

anyway it does return 2 records but strangely one is less than today 28/07/12 and 1 is after today 29/09/12 ?

 

[bob fitz]

Try:
DoCmd.ApplyFilter , "[next contact] >=#" & Date & "# and <#" & Date + 1 & "#"

 

[boblarson]

First off, you may need to use the U.S. Date formatting as that is a known issue (Allen Brown talks about it on his website).

So, how about:

Me.Filter = "[next contact] Between " & Format(Date, "\#mm\/dd\/yyyy\#") & " And " &  Format(Date + 1, "\#mm\/dd\/yyyy\#") 
Me.FilterOn

 

[laffeg]

Hi

tried that and get

syntax error ( missing operator ) in query expression '[next contact]>=#21/09/2012# and <#22/09/2012#'

strange that it doesnt return an error without the <

 

[bob fitz]

Or even:
DoCmd.ApplyFilter , "[next contact] >=#" & Date & "# and [next contact] <#" & Date + 1 & "#"

 

[laffeg]

Many thanks to both Bobs


got there with this


DoCmd.ApplyFilter , "[next contact] Between " & Format(Date - 1, "\#mm\/dd\/yyyy\#") & " And " & Format(Date, "\#mm\/dd\/yyyy\#")



it didnt like Me.Filteron ?

funny how i had to change from date , date+1 to date-1 and date

oh well that is access for you

thanks again

 

[boblarson]

oops, it was supposed to be

Me.FilterOn = True