Setting up a calculated field for dates

[JoshD]

Hi there,



I have a simple need I think.....

I need to set up a query that takes the difference between 2 dates and returns the difference as number. My table, let's call it (Data) has 8 fields, 2 of which are date fields (Date1, Date2). I want the query to show all 8 fields, plus a calculated field that shows the number of days between Date1 and Date2

I tried this below, to no avail. (I know this is probably way off)

SELECT Field1, Field2, Field3, Field4, Field5, Field6, Date1, Date2
FROM Data
BETWEEN Date1 AND Date2 AS DaysBetween

Any help is appreciated. Thanks

[pbaldy]

You wouldn't want that Between statement there; that would be for a criteria. Try the DateDiff() function in your SELECT clause:

SELECT Field1, Field2, Field3, Field4, Field5, Field6, Date1, Date2, DateDiff(...) AS DaysBetween

[JoshD]

Hmmm Ok, I tried this still no luck. Am I getting closer?

SELECT*FROM Data
=DateDiff("d",[Date1],[Date2]) AS DaysBetween

Thanks again

[pbaldy]

I showed you the query syntax.