Solved

Ms Access Sql Date Math

Posted on 2013-07-01
7
648 Views
Last Modified: 2013-07-02
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.
0
Comment
Question by:Moed
  • 3
  • 3
7 Comments
 
LVL 6

Expert Comment

by:Dulton
ID: 39290730
use:

 DateDiff(Day,Warranty.datCallDate, GetDate()) As DaysOld
0
 
LVL 77

Expert Comment

by:peter57r
ID: 39290746
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
 
LVL 2

Author Comment

by:Moed
ID: 39291397
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 6

Accepted Solution

by:
Dulton earned 350 total points
ID: 39292140
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
 
LVL 2

Author Comment

by:Moed
ID: 39292735
It returns "Undefined function "Today""
0
 
LVL 6

Expert Comment

by:Dulton
ID: 39292861
& " 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
 
LVL 2

Author Closing Comment

by:Moed
ID: 39292922
Had to tweak it just a bit.  

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

Thanks for the help.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

The purpose of this article is to demonstrate how we can use conditional statements using Python.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 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