SCS1ST
asked on
DateAdd in SQL??
I have an access adp with a SQL back end. I am trying to filter a report as such:
Dim sFilter As String
sFilter = "[Active]=yes and [MaxofDate1] > '" + Str(DateAdd("d", -30, Now())) + "' "
Me.Filter = sFilter
Me.FilterOn = True
It keeps giving me todays date Instead of 30 days prior to now. How do I use the DateAdd in SQL to give me 30 days prior to today?
Dim sFilter As String
sFilter = "[Active]=yes and [MaxofDate1] > '" + Str(DateAdd("d", -30, Now())) + "' "
Me.Filter = sFilter
Me.FilterOn = True
It keeps giving me todays date Instead of 30 days prior to now. How do I use the DateAdd in SQL to give me 30 days prior to today?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Wherever possible, avoid mixing Access and SQL operators and functions. Nearly always use SQL.
So rather than NOW(), USE SQLs GETDATE(), as in
Str(DateAdd("d", -30, GETDATE()))
David
So rather than NOW(), USE SQLs GETDATE(), as in
Str(DateAdd("d", -30, GETDATE()))
David
ASKER
I tried that, but it kept saying that "Sub or Function not defined" when it would get to the GetDate. Am I putting it in the wrong place to use SQL? I'm creating the string that would go into the Filter Properties on the form. Is this the wrong way to do that? This is where I get really confused between Access & SQL.
>Wherever possible, avoid mixing Access and SQL[Server] operators and functions.
While I do agree, a filter is applied by Access, so it's too late to assert an SQLServer function like one might do in a passthru query. As a matter of observation:
Str(DateAdd("d", -30, GETDATE())) <==this is a mixture of SQL and Access which is why it doesn't work.
DateAdd('d', -30, Now()) <==this is a 100% Access
FWIW, my second alternative was missing some parens; this is what I meant:
sFilter = [Active]=yes and [MaxofDate1] > (Date() - 30)
While I do agree, a filter is applied by Access, so it's too late to assert an SQLServer function like one might do in a passthru query. As a matter of observation:
Str(DateAdd("d", -30, GETDATE())) <==this is a mixture of SQL and Access which is why it doesn't work.
DateAdd('d', -30, Now()) <==this is a 100% Access
FWIW, my second alternative was missing some parens; this is what I meant:
sFilter = [Active]=yes and [MaxofDate1] > (Date() - 30)
Ah yes! I am Access deficient, but SQL proficient. Thanks for the clarification!
David
David
ASKER
sFilter = "[Active]=yes and [MaxofDate1] > DateAdd('d', -30, Now())"