Calculate days in Access query

[KeyserSoze]

I currently have a query that gives me a list of records when I run it:

SELECT VR_TRACKING.LOC_CODE, Folders.CurrentSSN, Folders.StudentName, Folders.EntryTerm, Folders.EntryYear, Folders.CuriclmCode, Folders.StudentType, Folders.Tracking_ID, VR_TRACKING.TRANSFER_TS
FROM VR_TRACKING INNER JOIN Folders ON VR_TRACKING.TRACKING_ID = Folders.Tracking_ID
WHERE ((([VR_TRACKING]![LOC_CODE])<>"FILE-ROOM" And ([VR_TRACKING]![LOC_CODE])<>"FILEROOM") AND (([Folders]![EntryYear])>="2007"))
ORDER BY VR_TRACKING.LOC_CODE, Folders.StudentName;

However, I want to calculate the number of days from the field VR_TRACKING.TRANSFER_TS to the current date when the query is run and include that number in the displayed records. Is this possible in a query or will I have to go the REPORT/QUERY route in order to write some code?

Thanks!

 

[SkipVought]

Hi,

How about
[tt]
ElapsedDays: VR_TRACKING.TRANSFER_TS - Date()
[/tt]


Skip,

_[red][/red]

 

[SkipVought]

Maybe
[tt]
ElapsedDays: Date() - VR_TRACKING.TRANSFER_TS
[/tt]


Skip,

_[red][/red]

 

[KeyserSoze]

Skip:

Where in my code would I place your solution?

Thanks!

 

[SkipVought]

Actually in your Select clause...

, Date() - VR_TRACKING.TRANSFER_TS As ElapsedDays

Skip,

_[red][/red]

 

[KeyserSoze]

Skip:

I'm receiving an "Undefined Function" error for the Date function when I apply that line of code to the SELECT statement.

Thanks!

 

[lespaul]

Sounds like you're missing a reference...

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[KeyserSoze]

Is there a way to make a reference to the DATE function so that the query would work?

 

[lespaul]

In Access Help - Search for 'Missing Reference' :
[tt]
Troubleshoot undefined functions and references to projects or libraries
Show All
Hide All
When you open a Microsoft Access file that contains references to projects or libraries that aren't in the location specified in the References dialog box, Access tries to find the projects or libraries. However, if Access can't find a referenced file, you might have to set the reference manually. You might not be aware that you need to fix a missing reference until you receive an "undefined function" message or a message that Access can't find a project or library when you try to run a query or a Microsoft Visual Basic procedure (procedure: A sequence of declarations and statements in a module that are executed as a unit. Procedures in Visual Basic include both Sub and Function procedures.). To fix a missing reference, open a module in Design view and click References on the Tools menu. Then clear the check box next to any missing references and set the references you want.
You can't call a class module from a query, form, report or macro. Store a Visual Basic procedure in a standard module if you need to call it from a query, form, report, or macro.
You might receive an "undefined function" message when you open a data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.) outside of Access if the data access page contains a function that isn't available outside of Access.
[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins