End Date -7

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a query which I want to have return a weeks worth of data.

I have got this working using between [start date] and [end date] in the
criteria, but wondered if there was a way I could just prompt the user for
the either just the end date and have the query retreive information for the
previous 7 days or just the start date and add seven days.

Cheers,

Sue
 
Thanks Van,

I get a message to say that this expression is either typed incorrectly or
is too complex to be evaluated. Any ideas why this might be?

Cheers,

Sue

Van T. Dinh said:
.... BETWEEN [StartDate] AND ([StartDate] + 7)

--
HTH
Van T. Dinh
MVP (Access)


SiouxieQ said:
Hi,

I have a query which I want to have return a weeks worth of data.

I have got this working using between [start date] and [end date] in the
criteria, but wondered if there was a way I could just prompt the user for
the either just the end date and have the query retreive information for the
previous 7 days or just the start date and add seven days.

Cheers,

Sue
Click to expand...
Click to expand...
 
Probably because of the parentheses or the "shortcut" way of adding date.

Try:

... BETWEEN [StartDate] AND [StartDate] + 7

or more syntactically correct:

.... BETWEEN [StartDate] AND DateAdd("d", 7, [StartDate])
 
Thanks Van,

I didn't try the first code, because I tried the second and it worked
perfectly.

Great stuff - Thanks

Van T. Dinh said:
Probably because of the parentheses or the "shortcut" way of adding date.

Try:

... BETWEEN [StartDate] AND [StartDate] + 7

or more syntactically correct:

.... BETWEEN [StartDate] AND DateAdd("d", 7, [StartDate])



--
HTH
Van T. Dinh
MVP (Access)


SiouxieQ said:
Thanks Van,

I get a message to say that this expression is either typed incorrectly or
is too complex to be evaluated. Any ideas why this might be?

Cheers,

Sue
Click to expand...
Click to expand...
 
Van T. Dinh said:
Probably because of the parentheses or the "shortcut" way of adding date.

Try:

... BETWEEN [StartDate] AND [StartDate] + 7

or more syntactically correct:

.... BETWEEN [StartDate] AND DateAdd("d", 7, [StartDate])
Click to expand...

Why is the second more syntactically correct? The first is a documented way
of using the language and is more fluent. Just as native English speakers
usually
wouldn't say, "We are going to the store now." opting for, "We're going to
the store now."
instead.

Tom Lake
 
Tom

To me, using algebraic addition for a date/time is not entirely correct.
Note also that in my original suggestion, the parentheses actually makes a
difference which indicates something is not right in treating it
algebraically.

IIRC correctly, SQL Server won't be happy with the algebraic addition for
date/time values, either.

In addition, if you try to take away 6 hours from midnight of the
30/Dec/1899, you will see that DateAdd() is correct but algebraic
addition/subtraction doesn't give correct result. From AXP Debug window:

?Format(#12/30/1899# - 0.25, "mm/dd/yyyy hh:nn")
12/30/1899 06:00

?Format(DateAdd("h", -6, #12/30/1899#), "mm/dd/yyyy hh:nn")
12/29/1899 18:00

The documented way in books I read is DateAdd(). I use the algebraic
addition / subtraction for date/time regularly but AFAIK, it is a shortcut
because we know how date/time values are stored internally.
 
Back
Top