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?

SCS1STAsked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
Seems like these would work:

sFilter = "[Active]=yes and [MaxofDate1] > DateAdd('d', -30, Now())"

or

sFilter = "[Active]=yes and [MaxofDate1] >  Date() - 30
0
 
SCS1STAuthor Commented:
This one worked.  Thanks,

sFilter = "[Active]=yes and [MaxofDate1] > DateAdd('d', -30, Now())"
 
0
 
anyoneisCommented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
SCS1STAuthor Commented:
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.
0
 
dqmqCommented:
>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)  

 
0
 
anyoneisCommented:
Ah yes! I am Access deficient, but SQL proficient. Thanks for the clarification!

David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.