Help with Date Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

eoluke - 30 May 2008 14:13 GMT

Hello,
I am using a query in Access 2003 to get payroll data for our company.
Currently I am using the between/and parameter to achieve this however I
wonder if there is a way to just enter the last day of the pay period and
then include the previous 13 days in the results. This would make things much
easier to just input one date as opposed to 2. Any help would be appreciated
and thanks in advance.

Signature

eoluke

Reply to this Message

Michel Walsh - 30 May 2008 14:38 GMT

change your SQL from:

WHERE dateStamp BETWEEN [startingDate] AND [endingDate]

to

WHERE dateStamp BETWEEN CDate([endingDate]-14) AND [endingDate]

You could also use DateAdd VBA function, instead of adding -14 directly
(mainly if you want add month, or year, etc. )

Vanderghast, Access MVP

> Hello,
> I am using a query in Access 2003 to get payroll data for our company.
[quoted text clipped - 5 lines]
> appreciated
> and thanks in advance.

Reply to this Message

eoluke - 30 May 2008 15:57 GMT

I changed my SQL as follows:
WHERE((([tbl Exemption Data].[Exemption Date]) Between CDate([end date]-14)
AND [end date]));
It now has an error that says "the expession is typed incorrectly, or is to
complex to be evaluated" I think I am lost. Any other suggestions?

Signature

eoluke

> change your SQL from:
>
[quoted text clipped - 18 lines]
> > appreciated
> > and thanks in advance.

Reply to this Message

John Spencer - 30 May 2008 17:17 GMT

Try a slight rearrangement

WHERE [tbl Exemption Data].[Exemption Date] Between CDate([end date])-14
AND CDate([end date])

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> I changed my SQL as follows:
> WHERE((([tbl Exemption Data].[Exemption Date]) Between CDate([end date]-14)
> AND [end date]));
> It now has an error that says "the expession is typed incorrectly, or is to
> complex to be evaluated" I think I am lost. Any other suggestions?

Reply to this Message

eoluke - 30 May 2008 19:05 GMT

Thank you both for putting me on the right track....It works great!!!

Signature

eoluke

> Try a slight rearrangement
>
[quoted text clipped - 11 lines]
> > It now has an error that says "the expession is typed incorrectly, or is to
> > complex to be evaluated" I think I am lost. Any other suggestions?

Reply to this Message