Solved

Ms Access Sql Date Math

Posted on 2013-07-01
7
640 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
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 …

803 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