Help with Date Range

  • Thread starter Thread starter eoluke
  • Start date Start date
E

eoluke

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.
 
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
 
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?
--
eoluke


Michel Walsh said:
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


eoluke said:
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.
Click to expand...
Click to expand...
 
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
 
Thank you both for putting me on the right track....It works great!!!
--
eoluke


John Spencer said:
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?
Click to expand...
Click to expand...
 
Back
Top