What is proper statement to retrieve current year

[lmn]

Mind burp.

I have a report where I want to hard code that the query returns only data from the current year.....I have created a date part statement and then I'm sure I have to put something in criteria equivalent to the Now() statement...

How do I do this?

I feel like an idiot for not remembering....I did a quick search of help and couldn't find it.

~L

 

[jbehrne]

Hi,

Try this:

SELECT TableName.DateField, *
FROM TableName
WHERE (((TableName.DateField)=Now()));

- If your datefield data does not match the now statment you will have to create some expressions and format them to fit your datefield.

I.E. - Expr1:=Now()
Expr2:=Format(Expr1, "YYYY&quot

Then set your where section to fit the expr2:

WHERE (((TableName.DateField)=Expr2));

HTH,

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[lmn]

Thank you for your response.

I'm still confused...

I am able to get the current fiscal year by this function:

DatePart("yyyy",DateAdd("m",-1,[FIELD NAME])) - this will get me their current fiscal year (their year starts Feb 01).

I want to hard code it to say - get the current fiscal year.

If they ran on normal years - I would simply say Year(Now())

I'm sure it's something simple but I think I've used all my allotted brain cells for today...

Lisa

 

[jbehrne]

Sorry,

But you have me confused... You wrote "I want to hard code it to say - get the current fiscal year." Do you want a msgbox to pop up and ask for the year? Or are you asking for a button labeled "Get the Current Fiscal Year"? Please elaborate...

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[lmn]

I'm a blonde.....and had a blonde moment...

What I want to do is hard code reports (that don't require a user to enter current year) and it will retrieve the current fiscal year info.

This is what worked under the Criteria portion of the query for Fiscal year

Year(DateAdd("m",-1,Now()))

 

[jbehrne]

Hi,

If you just want to set the report to print the current year then change it's underlying query to something like what I had posted above or this:

SELECT *
FROM TableName
WHERE (((TableName.DateField)= Year(DateAdd("m",-1,Now()))))

- Change TableName to the name of the table that the data is being pulled from
- Change DateField to the field in your table that holds the date

Please post and let me know if this made anything clearer...

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations