How to create YTD query without entering the Dates?

[Bre035]

I am creating a Year to Date report. Typically, I create a query to allow the ability to enter the beginning and end date, but I want to automate this without entering the date.

I have criteria in query >[Enter beginning Date]<DATE(). However, I wanted to know is there a Date format for the beginning of the year.

I attempted to DateModified >01/01 <Date(), but this always saves as the current date.

[mshmyob]

YOu could do what you are doing with the "01/01/" and then append just the year from the current date by using the YEAR function for date manipulation.

dteYTD="01/01/" & year(Date)
 

cheers,

[Bre035]

Thank you but when I get the date then attempt add it as a criteria for DateModified for YTD Date Range - Access states datatypes are different.

DateYTD is saving as a test field.

[mshmyob]

Show me your query.

cheers,

[Bre035]

My initial query set the DateYTD field:

SELECT tbl_CLLData.[CALL TYPE], tbl_CLLData.[RECORD ID], tbl_CLLData.[CALL RESULT], tbl_CLLData.CLL_Type, tbl_CLLData.DateModified, "01/01/" & Year(Date()) AS DateYTD INTO tbl_CALLTPE_MGT
FROM tbl_CLLData;

My second query performs the comparison where I get the Data Type mismatch:

SELECT tbl_CALLTPE_MGT.[CALL TYPE], tbl_CALLTPE_MGT.[RECORD ID], tbl_CALLTPE_MGT.[CALL RESULT], tbl_CALLTPE_MGT.CLL_Type, tbl_CALLTPE_MGT.DateModified INTO tbl_CALLTPE_MGT_1YTD
FROM tbl_CALLTPE_MGT
WHERE (((tbl_CALLTPE_MGT.DateModified)>"DateYTD"<Date()));

[mshmyob]

I would expect that to produce an error.

Last line (your WHERE clause) should be something like

WHERE tbl_CALLTPE_MGT.DateModified > #DateYTD# AND tbl_CALLTPE_MGT.DateModified < Date();

Note: I have not tested this but please try this to see if it helps.

cheers,

[Bre035]

What I did was trim the DateModified field to get the Year, then criteria set to Year(Date()), which yeilds the results I needed for the current year records.

[NeoPa]

Assuming [DateModified] cannot be a future date, you can simply say :

WHERE (Year([DateModified]) = Year(Date))

The existing code, while pretty close, would surely ignore anything dated on the current date.