Ms Access Sql Date Math

I want to calculate the number of days from today within a sql statement.  This is my sql:

dim d as date = today

 Sql = "SELECT ContractInformation.txtContractNumber, Warranty.txtWorkOrderNumber, Warranty.txtContractNumber, Warranty.datCallDate, " _
            & " (Warranty.datCallDate - " & d & " as DaysOld)," _
            & " Warranty.datWorkDate, Warranty.txtAssignedTo, Warranty.txtStatus, Warranty.txtWorkPerformed, Warranty.curCostofCall, Warranty.txtWarrantyType, " _
            & " Warranty.txtWarrantyItem, Warranty.lngCustomerID, Warranty.txtManager " _
            & " FROM ContractInformation INNER JOIN Warranty ON ContractInformation.AutoID = Warranty.txtContractNumber " _
            & " WHERE Warranty.lngCustomerID = " & CustomerID & " " _
            & " ORDER BY Warranty.txtWorkOrderNumber DESC "

I get a syntax error here (Warranty.datCallDate - " & d & " as DaysOld)     but can't find an example.
LVL 2
MoedAsked:
Who is Participating?
 
DultonConnect With a Mentor Commented:
a couple things:
you have a closing parenthesis after ".....As DaysOld", it should rather be after "GetDate()"
and an extra one before the whole formula.

Secondly.. I kinda jumped into SQL Server... if you're running an access back-end, try replacing "GetDate()" with "Today()"


Here's how your whole line should read:

 & " DateDiff(Day,Warranty.datCallDate, Today()) As DaysOld," _
0
 
DultonCommented:
use:

 DateDiff(Day,Warranty.datCallDate, GetDate()) As DaysOld
0
 
peter57rCommented:
Can you be clear about where you want to run this code.  You have tagged both Access and VB.net as topics and the answers would be quite different.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
MoedAuthor Commented:
This is VB.net.  Sorry for creating confusion.

Dulton:  Getting syntax error.  Here is the implementation:

     Sql = "SELECT ContractInformation.txtContractNumber, Warranty.txtWorkOrderNumber, Warranty.txtContractNumber, Warranty.datCallDate, " _
            & " (DateDiff(Day,Warranty.datCallDate, GetDate() As DaysOld)," _
            & " Warranty.datWorkDate, Warranty.txtAssignedTo, Warranty.txtStatus, Warranty.txtWorkPerformed, Warranty.curCostofCall, Warranty.txtWarrantyType, " _
            & " Warranty.txtWarrantyItem, Warranty.lngCustomerID, Warranty.txtManager " _
            & " FROM ContractInformation INNER JOIN Warranty ON ContractInformation.AutoID = Warranty.txtContractNumber " _
            & " WHERE Warranty.lngCustomerID = " & CustomerID & " " _
            & " ORDER BY Warranty.txtWorkOrderNumber DESC "
0
 
MoedAuthor Commented:
It returns "Undefined function "Today""
0
 
DultonCommented:
& " DateDiff('d',Warranty.datCallDate, Today) As DaysOld," _



Couple changes. Changed interval from Day to 'd'.. using single quotes because inside a querystring delimited by double quotes.
Dropped parenthesis on Today function inside querystring....
0
 
MoedAuthor Commented:
Had to tweak it just a bit.  

DateDiff('d',Warranty.datCallDate,Date()) AS DaysOld

Thanks for the help.
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.