Calculating Dates

[deegan424]

How do you count 7 days back from a date? Like, give me all transactions that occurred 7 days from today in access.

Thanks for the help.

[Andrew Thackray]

How do you count 7 days back from a date? Like, give me all transactions that occurred 7 days from today in access.

Thanks for the help.

The function is Dateadd. the syntax is Dateadd(PeriodT ype,period,date )

in your case it would be

Newdate = Dateadd("d",-7,OldDate)

[Killer42]

The function is Dateadd. ...in your case it would be Newdate = Dateadd("d",-7,OldDate)

Also, you can probably use the Now() function for the current time. That would make something like

Newdate = DateAdd("d",-7,[b]Now()[/b])

(You might not need the parentheses after Now - I forget).

[MMcCarthy]

How do you count 7 days back from a date? Like, give me all transactions that occurred 7 days from today in access.

Thanks for the help.

SELECT * FROM TableName
WHERE [Date Field] BETWEEN Date() And Date()-7;

[NeoPa]

As the Date in Access is stored as 'Number of Days since ...' and the time is stored as a fractional part of a day, adding/subtracting a number to a date field is equivalent to using

DateAdd("d",number,[Date Field])

The documentation for DateAdd() also includes an option for adjusting by a weekday ("w"). I've found this not to work (albeit only tested up to Access 2K).