Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Ms Access Sql Date Math

Posted on 2013-07-01
7
Medium Priority
?
672 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 6

Accepted Solution

by:
Dulton earned 1400 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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