Hi all

I a creating a query the gets an [entered date] – 7 days then – 14 days then – 21 days etc…

i have this

FORMAT(Appointments.[18 Week Start Date], "DD-7/MM/YYYY")
FORMAT(Appointments.[18 Week Start Date], "DD-14/MM/YYYY")
FORMAT(Appointments.[18 Week Start Date], "DD-21/MM/YYYY")


The problem is that when i get to 14 and 21 the amounts are the same 🙁

Please help

Cheers,
Neil

been a while since I did any work with Access, but wouldn’t it be better to call dateadd() ?

http://www.techonthenet.com/access/functions/date/dateadd.php

Can’t remember if it purely a VBA function or if they implement it as part of their SQL processing. If it is a callback to the VBA function the SQL query might run a tad slowly…(each row will have to be fetched then processed client side)

A fellow NHS worker no doubt?
try:


FORMAT(Appointments.[18 Week Start Date]-7, "DD/MM/YYYY")
FORMAT(Appointments.[18 Week Start Date]-14, "DD/MM/YYYY")
FORMAT(Appointments.[18 Week Start Date]-21, "DD/MM/YYYY")


As long as the Appointments.[18 Week Start Date] is a date field you should be sorted