Access ADP project server filter by Date greater than

  • Thread starter Thread starter akzeph
  • Start date Start date
A

akzeph

Access adp, 2007-2.0 connecting to SQL2008-2005, On a form there is a text
box with a control source of DuesPaidThru and the forms recordsource is
Members, sql is storing the date as Datetime. Typical entry is 12/01/2009.
Filter by Selection works fine on this field. When doing a server Filter by
form, if I enter a date such as 12/04/2009, Access will append quotes like
so: "12/04/2009", apply the filter and it is good. Now when attempting a
filter say greater than a date, it is not possible. Say >#12/01/2009#, just
does not work and the error returned is Cannot apply filter on one or more
fields specified in the filter property. Have you been here before, I could
use some help:}
 
The # date delimiter is only for Access, not SQL Server.
I didn't test it, but since "12/04/2009" works as a filter, you might try
"12/01/2009" or >'12/01/2009', which is SQL Server's standard text and date
Click to expand...
delimiter.
 
Sorry, Paul, that does not work in this server filter applied to a form...
Access adp turns the one suggestion into
">""12/01/2009"""
The other is similarly rejected.

I just realized that if I have a field that is of type
DATE
it is possible to do a
Like '%2009-12%'
I think that is what I will do: Change the type to Date from Datetime...Then
this will show me dues paidthru in December 2009...End of another frustrating
Access issue:} Sorry Redmond <grin> I do like the app, but some of the
undocumented issues have been a challenge.

Again, with an adp: filtering on one date works, but a range will not. Does
that make sense?
 
akzeph said:
Sorry, Paul, that does not work in this server filter applied to a form...
Access adp turns the one suggestion into
">""12/01/2009"""
The other is similarly rejected.

I just realized that if I have a field that is of type
DATE
it is possible to do a
Like '%2009-12%'
I think that is what I will do: Change the type to Date from Datetime...Then
this will show me dues paidthru in December 2009...End of another frustrating
Access issue:} Sorry Redmond <grin> I do like the app, but some of the
undocumented issues have been a challenge.

Again, with an adp: filtering on one date works, but a range will not. Does
that make sense?
Click to expand...

Update: Just found out that changing the field source to date stops the text
box from formating Month/Year. I used to have the format property for the
field mm/yyyy and it would display month year. Now fomat does not work and I
 
Back
Top