?
Solved

DateAdd in SQL??

Posted on 2006-05-15
6
Medium Priority
?
977 Views
Last Modified: 2008-01-09
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?

0
Comment
Question by:SCS1ST
  • 2
  • 2
  • 2
6 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 16683975
Seems like these would work:

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

or

sFilter = "[Active]=yes and [MaxofDate1] >  Date() - 30
0
 

Author Comment

by:SCS1ST
ID: 16684044
This one worked.  Thanks,

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

Expert Comment

by:anyoneis
ID: 16684045
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SCS1ST
ID: 16684082
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
 
LVL 42

Expert Comment

by:dqmq
ID: 16684498
>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
 
LVL 11

Expert Comment

by:anyoneis
ID: 16687398
Ah yes! I am Access deficient, but SQL proficient. Thanks for the clarification!

David
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question